ActiveX Script Task in SSIS 2005 and 2008

ActiveX Script Task in SSIS requires the following library to be registered manually using regsvr32.exe on the Windows OS that runs SQL Server.

Otherwise, the SSIS package may fail when it is run as a SQL Server job.

[SQL Server 2005]
regsvr32.exe "C:\Program Files\Microsoft SQL Server\90\COM\AXSCPHST90.DLL"

[SQL Server 2008]
regsvr32.exe "C:\Program Files\Microsoft SQL Server\100\COM\AXSCPHST.DLL"

Another element of surprise to keep in mind is to try configuring the SSIS package to run in 32-bit mode. I realize that SSIS Task such as Excel Destination would only work when run in 32-bit mode. 


When you create a SQL 2008 SSIS package on 64-bit Windows 7, for example, the SSIS package defaults to 64-bit environment (as well as .NET Framework 2.0). You can change these default settings in the property section of either at the Task-scope or project-scope.


Of course, if you choose to use 32-bit environment when you complete your SSIS package, you'd have to run the SQL Server job that runs the SSIS package in 32-bit mode as well. You can set this option in the job properties window.