The SSIS Script Task and Script Component editors don’t provide functionality (available in full VS) to add a reference to unmanaged COM components. Can this be done at all?
Well, yes, although lack of GUI option in VSA (which SSIS uses for editing and debugging scripts) makes it a bit complex. We’ll need to create an interop assembly using command line tools, install the assembly into GAC and .NET Frameworks folder, and then VSA will be able reference it. You’ll need .NET SDK or Visual Studio to do this.
Open .NET SDK command prompt. First, generate a key for creating strong names for your assemblies. You may have already created a key for custom task or transform – then skip this step and use existing key.
sn.exe -k MyKey.snk
Now find the COM component and generate interop assembly:
tlbimp.exe “C:\…\MyComObj.dll” /out:Interop.MyComObj.dll /keyfile:MyKey.snk
Install the assembly into GAC: drag it to
c:\Windows\Assembly folder or run following command (this step should be repeated on every machine where you’ll run this package):
gacutil.exe /i Interop.MyComObj.dll
and also copy it to
c:\Windows\Microsoft.NET\Framework\v2.0.50727 folder (this step is only required on machine where you edit the scripts, except if you turn off the option to precompile scripts – then it is also needed at machines where you run packages).
Things to be aware of: the code executed by script task or script component always runs on multi-threaded COM apartment (MTA, same as when using [MTAThread]), so using COM components marked as Free/Both is fine, but using STA components will use marshalling and might be slow or broken (as in case of SqlXml).
This blog by Jamie Thomson contains some useful background information as well: