通过

COM references within an SSIS Script Component

  • 项目

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:
https://blogs.conchango.com/jamiethomson/archive/2005/11/02/2341.aspx

Comments

  • Anonymous
    April 22, 2007
    The comment has been removed
  • Anonymous
    July 16, 2007
    I just wanted to comment we have implemented an extension of the standard Microsoft Script Task. It allows the implementation of your own script's user interface and has better script reusability. This can can be used as alternative to implementing a full blown custom SSIS control flow task. For more information please visit: http://www.cozyroc.com/products.html Regards, Ivan