The requirement is to refresh an Excel file in the SQL Report Server by running the Macro in the Excel file.
In this post, I'll summarize the approach and the issues faced and in another post, I'll explain the final implementation.
The key areas of the implementation is
1. Download the Excel file from Report Server
2. Run the Macro to refresh the Excel file and Save the file
3. Upload the Excel file to Report Server
For the implementation, SQL Server Integration Service is used.
If I summarize the approach followed for each steps
1. Download the Excel file using the DownloadFile method in WebClient.
2. Run the Macro using Excel COM Objects
3. Upload the Excel file to Report Server using Report Server Web Service
For 1st and 3rd steps, I could not find any resource directly for Excel files in Report Server, but I was able to achieve it by applying the relevant property values for the Excel files.
To run the Macro (2nd step), initially I used the Microsoft.Office.Interop.Excel namespace using C#, inside a SSIS Script Task. But I faced an issue while focusing on the production deployment. Because for Interop libraries, Excel needs to be installed on the running computer. That means in the Production server where the package is deployed, needs to have Excel installed.
But in the production servers it is not recommend to install Excel due to security reasons. Therefore I have to find an alternative approach.
Due to that initial approach was changed little bit.
Since the Excel needs to be installed on the computer where we run the Macro, we configured a separate VM with Excel installed. Actually this approach reduced any security risk which may occur due to the configuration changes, which will be described later.
Then to run the Macro, if I try to use Microsoft.Office.Interop.Excel namespace using C#, inside a SSIS Script Task, that means I have to deploy the package in the VM, where Excel is installed.
But that will add an additional cost since we have to install SSIS on the VM.
Due to that I choose to use PowerShell Command to run the Macro inside the Excel file, since it is possible to run a PowerShell command on a remote machine. To download the Excel files to the VM, a shared folder is created as well.
PowerShell command is executed from the Database Server, on the configured VM using the PowerShell Remoting. For this PowerShell Remoting needs to be enabled on the VM as well.
PowerShell Remoting can be configured using the below PowerShell command
Enable-PSRemoting -Force
Reference: https://www.howtogeek.com/117192/how-to-run-powershell-commands-on-remote-computers/
PowerShell command on the remote computer can be executed using inovke-command, and you have to provide the relevant credentials as well.
The provided user needs to have Administrator privileges on the remote computer to run the PowerShell command remotely.
But there are ways that we can skip this condition, but it is not recommended to do that due to security reasons.
The complete PowerShell command for Excel Macro execution is as below:
$Username = 'EnterUserName'
$Password = 'EnterPassword'
$pass = ConvertTo-SecureString -AsPlainText $Password -Force
$Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$pass
invoke-command -computername VMExcel -ScriptBlock {$excel = new-object -comobject excel.application
$filePath = "\\VMExcel\ExcelFiles\ExcelReport.xlsm"
$workbook = $excel.Workbooks.Open($filePath)
$worksheet = $workbook.worksheets.item(1)
$excel.Run("ExcelReport.xlsm!MacroName")
$workbook.save()
$workbook.close()
$excel.quit()
} -credential $Cred
But when I tried to execute the above command from the Database server using PowerShell, I got the below error message
Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the
following error: 80080005 Server execution failed (Exception from HRESULT: 0x80080005 (CO_E_SERVER_EXEC_FAILURE))
If you google this error, you will find most of the people responded saying that automating Excel refresh cannot be achieved, due to the way Microsoft Office is designed.
Reference:
https://social.technet.microsoft.com/Forums/lync/en-US/4ea691e1-fb3e-41dc-887a-0e90f7d1c38d/accessing-excel-com-interop-dll-inside-a-new-cmdlet-code-resulting-in-exception-esp-when-cmdlet?forum=winserverpowershell
But I found some posts regarding some work around for the above error.
Based on those I applied the below changes.
1. Configuration changes to be done to allow access for the COM Object access.
2. Change the Identity of Microsoft Excel Application, from Launching User to Interactive User
Then the last thing is to enable Auto Logon for the user who is running the PowerShell command on the VM.
The reason that the auto logon needs to be enable is, since the Excel application Identity mode is Interactive User, that user needs be log in to the VM, for the PowerShell command to be executed successfully.
To enable Auto Logon on the VM, I applied the required steps in the below link
https://superuser.com/questions/28647/how-do-i-enable-automatic-logon-in-windows-7-when-im-on-a-domain
That is it.
In my next post, I'll write about the SSIS implementation for the Excel Report refresh.
In this post, I'll summarize the approach and the issues faced and in another post, I'll explain the final implementation.
The key areas of the implementation is
1. Download the Excel file from Report Server
2. Run the Macro to refresh the Excel file and Save the file
3. Upload the Excel file to Report Server
For the implementation, SQL Server Integration Service is used.
If I summarize the approach followed for each steps
1. Download the Excel file using the DownloadFile method in WebClient.
2. Run the Macro using Excel COM Objects
3. Upload the Excel file to Report Server using Report Server Web Service
For 1st and 3rd steps, I could not find any resource directly for Excel files in Report Server, but I was able to achieve it by applying the relevant property values for the Excel files.
To run the Macro (2nd step), initially I used the Microsoft.Office.Interop.Excel namespace using C#, inside a SSIS Script Task. But I faced an issue while focusing on the production deployment. Because for Interop libraries, Excel needs to be installed on the running computer. That means in the Production server where the package is deployed, needs to have Excel installed.
But in the production servers it is not recommend to install Excel due to security reasons. Therefore I have to find an alternative approach.
Due to that initial approach was changed little bit.
Since the Excel needs to be installed on the computer where we run the Macro, we configured a separate VM with Excel installed. Actually this approach reduced any security risk which may occur due to the configuration changes, which will be described later.
Then to run the Macro, if I try to use Microsoft.Office.Interop.Excel namespace using C#, inside a SSIS Script Task, that means I have to deploy the package in the VM, where Excel is installed.
But that will add an additional cost since we have to install SSIS on the VM.
Due to that I choose to use PowerShell Command to run the Macro inside the Excel file, since it is possible to run a PowerShell command on a remote machine. To download the Excel files to the VM, a shared folder is created as well.
PowerShell command is executed from the Database Server, on the configured VM using the PowerShell Remoting. For this PowerShell Remoting needs to be enabled on the VM as well.
PowerShell Remoting can be configured using the below PowerShell command
Enable-PSRemoting -Force
Reference: https://www.howtogeek.com/117192/how-to-run-powershell-commands-on-remote-computers/
PowerShell command on the remote computer can be executed using inovke-command, and you have to provide the relevant credentials as well.
The provided user needs to have Administrator privileges on the remote computer to run the PowerShell command remotely.
But there are ways that we can skip this condition, but it is not recommended to do that due to security reasons.
The complete PowerShell command for Excel Macro execution is as below:
$Username = 'EnterUserName'
$Password = 'EnterPassword'
$pass = ConvertTo-SecureString -AsPlainText $Password -Force
$Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$pass
invoke-command -computername VMExcel -ScriptBlock {$excel = new-object -comobject excel.application
$filePath = "\\VMExcel\ExcelFiles\ExcelReport.xlsm"
$workbook = $excel.Workbooks.Open($filePath)
$worksheet = $workbook.worksheets.item(1)
$excel.Run("ExcelReport.xlsm!MacroName")
$workbook.save()
$workbook.close()
$excel.quit()
} -credential $Cred
But when I tried to execute the above command from the Database server using PowerShell, I got the below error message
Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the
following error: 80080005 Server execution failed (Exception from HRESULT: 0x80080005 (CO_E_SERVER_EXEC_FAILURE))
If you google this error, you will find most of the people responded saying that automating Excel refresh cannot be achieved, due to the way Microsoft Office is designed.
Reference:
https://social.technet.microsoft.com/Forums/lync/en-US/4ea691e1-fb3e-41dc-887a-0e90f7d1c38d/accessing-excel-com-interop-dll-inside-a-new-cmdlet-code-resulting-in-exception-esp-when-cmdlet?forum=winserverpowershell
But I found some posts regarding some work around for the above error.
Based on those I applied the below changes.
1. Configuration changes to be done to allow access for the COM Object access.
- Open the 32 bit Component Services on the VM using the mmc comexp.msc /32 in the run start bar
- Expand till get the My Computer (Component Services -> Computers -> My Computer) and Right click on it to get the Properties.
- Under COM Security tab, in the Access Permission section, click on Edit Default.
- Please make sure Running user or VM Administrator Group has the Local and Remote Access.
- Do the same for Launch and Activation Permissions
- Click Ok to apply the changes made
2. Change the Identity of Microsoft Excel Application, from Launching User to Interactive User
- Expand the DCOM Config in the Component Service which was opened before.
- Go to the Microsoft Excel Application, right click on it and select Properties
- Go to the Identity tab and select The interactive user
- Click Ok to apply those changes
Then the last thing is to enable Auto Logon for the user who is running the PowerShell command on the VM.
The reason that the auto logon needs to be enable is, since the Excel application Identity mode is Interactive User, that user needs be log in to the VM, for the PowerShell command to be executed successfully.
To enable Auto Logon on the VM, I applied the required steps in the below link
https://superuser.com/questions/28647/how-do-i-enable-automatic-logon-in-windows-7-when-im-on-a-domain
- Type regedit in Start Search and hit Enter
- Navigate to the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon - Double-click the AutoAdminLogon entry, type 1 in the Value Data box, and then click OK.
- If there is no AutoAdminLogon entry, create a new String Value (REG_SZ) with AutoAdminLogon as the value name and type 1 in the Value Data box, and then click OK.
- Then need configure Windows to remember the password for logging in.
- In the run box, type control userpasswords2
- Ensure your domain username is in the list, if not, add it.
- Untick (or tick and untick): Users must enter a user name and password to use this computer.
- Make sure your username is selected. Click Apply.
- At this point, Windows should prompt for the password that will be used.
- Then go back to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon
- Ensure the following String Values are set, if not, set them:
DefaultUserName: Your domain username (without the domain prefix)
DefaultDomainName: Your domain
That is it.
In my next post, I'll write about the SSIS implementation for the Excel Report refresh.
No comments:
Post a Comment