Assembling SSIS Packages in PowerShell

Question:

I should preface by saying my experience with scripting or programming in OOP languages is limited.

I’m working on a method for programatically creating and executing SSIS packages using PowerShell. Unfortunately, most of the resources available for PowerShell and SSIS are for calling PS from SSIS, not the other way around.

I have, however, found a number of resources for VB/C# for creating SSIS packages.

Example resource here.

I’ve succeeded in converting most of the code by calling the DTS/SSIS assemblies, but it’s failing now on converting the TaskHost object to a mainpipe.

Sample code:

Everything works fine til the last line, when I get the error:

Cannot convert the
“System.__ComObject” value of type
“System.__ComObject#{}” to
type
“Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipeClass”

Any assistance or ideas are welcome!

Answer:

Microsoft.SqlServer.DTSPipelineWrap makes heavy use of COM instances.

This forum post suggested using CreateWRapperOfType method:
http://social.technet.microsoft.com/Forums/en-US/ITCG/thread/0f493a31-fbf0-46ac-a6a5-8a10af8822cf/

You could try this:

Doesn’t error out and produces an object–I’m not sure of what type.

Source:

Assembling SSIS Packages in PowerShell by licensed under CC BY-SA | With most appropriate answer!

Leave a Reply