Search code examples
sql-serverssisxp-cmdshelldtexec

Executing a SSIS package that references mso.dll with DTEXEC

Apologies if this has been answered, but I've not been able to find an answer that solves my issue. I have a SSIS package that has a script task which references:

C:\Program Files (x86)\Common Files\microsoft shared\OFFICE16\MSO.DLL.

When executed through SQL Server Data Tools, the package runs as expected.

When deployed to Integration Services Catalogs, and manually executed, the package runs as expected. xp_cmdshell was then enabled.

When the package was executed using the command:

DECLARE @returncode INT;
EXEC @returncode = xp_cmdshell 'DTEXEC /ISSERVER "\SSISD
\test\test_DW_ETL\test_script_task.dtsx"'

it failed with the error message

Cannot Create ActiveX component

When another SSIS package, which doesn't have the Microsoft Objects reference in a script task, was executed using the same DTEXEC command, it ran successfully.

When the script task package is executed using the command:

DECLARE @returncode INT; EXEC @returncode = xp_cmdshell '@"C:\Program 
Files\Microsoft SQL Server\120\DTS\Binn\DTExec.exe" /ISSERVER "\SSISDB
\test\test_DW_ETL\test_script_task.dtsx"'

to use the 32 bit DTEXEC, the package also fails with the error message

Cannot Create ActiveX component

I've tried changing the account that SQL Server is executed as, using a Local System, Local Service, Network Service, my own account and a service account - none of which seemed to make a difference.

My suspicion is that DTEXEC cannot access mso.dll.

This must be quite a common method, so I think I must be missing a fairly obvious step. Can anyone provide any assistance?

Thanks,

Ian

Solution

  • Updating for the purpose of posterity in the event that someone else has this issue.

    In the first instance, I replaced the xp_cmdshell with reference to the stored procedures
    - SSISDB.catalog.create_execution
    - SSISDB.catalog.set_execution_parameter_value
    - SSISDB.catalog.start_execution

    I then ensured that the Proxy Account was set up correctly on SQL Server (both credentials and object references to permissions) so that it could run a SSIS Package.

    The SSIS package referenced Excel objects, so I updated the DCOM (MMC comexp.msc) settings for Microsoft Excel to ensure that the account, referenced by the Proxy Account, had Launch and Activation Permissions and Access Permissions. In addition to this, the identity was set to run as the Interactive User.

    In the development environment, we had Visual Studio, SSDT, and SSMS installed. The test environment only had SSMS. As a result, a number of components were missing, so we installed SSDT and Windows SDK 8.1. We also had to ensure that the following directories existed:
    - C:\Windows\SysWOW64\config\systemprofile\Desktop
    - C:\Windows\System32\config\systemprofile\Desktop

    Thanks,

    Ian