Building SSIS Packages in PowerShell

Question

I should preface, saying that my experience with writing scripts or programming in OOP languages ​​is limited.

I am working on a method for programmatically creating and executing SSIS packages using PowerShell. Unfortunately, most of the resources available for PowerShell and SSIS are for calling PS from SSIS, and not vice versa.

However, I found several resources for VB / C # to create SSIS packages.

An example of a resource is here.

I managed to convert most of the code by invoking the DTS / SSIS assemblies, but now it was not able to convert the TaskHost object to mainpipe.

Code example:

[Void][Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ManagedDTS') [Void][Reflection.Assembly]::LoadWithPartialName('Microsoft.Sqlserver.DTSPipelineWrap') # Create the Package and application, set its generic attributes $Package = New-Object Microsoft.SqlServer.Dts.Runtime.Package $Package.CreatorName = $CreatorName $App = New-Object Microsoft.SqlServer.Dts.Runtime.Application # Set connection info for our package $SourceConn = $package.Connections.Add("OLEDB") $SourceConn.Name = "Source Connection" $SourceConn.set_ConnectionString("Data Source=$SourceServer;Integrated Security=True") $TargetConn = $package.Connections.Add("OLEDB") $TargetConn.Name = "Target Connection" $TargetConn.set_ConnectionString("Data Source=$TargetServer;Integrated Security=True") # Build the tasks # Data Flow Task - actually move the table [Microsoft.SQLServer.DTS.Runtime.Executable]$XferTask = $Package.Executables.Add("STOCK:PipelineTask") $XferTaskTH = [Microsoft.SqlServer.Dts.Runtime.TaskHost]$XferTask $XferTaskTH.Name = "DataFlow" $XferTaskTH.Description = "Dataflow Task Host" $DataPipe = [Microsoft.SQLServer.DTS.pipeline.Wrapper.MainPipeClass]($XferTaskTH.InnerObject) 

Everything works fine to the last line when I get the error message:

Cannot convert "System .__ ComObject" to type "System .__ ComObject # {}" to type "Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipeClass"

Any help or ideas are welcome!

Answer 1

Microsoft.SqlServer.DTSPipelineWrap actively uses COM instances.

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

You can try the following:

 $DataPipe = [System.Runtime.InteropServices.Marshal]::CreateWrapperOfType($XferTaskTH.InnerObject, [Microsoft.SQLServer.DTS.pipeline.Wrapper.MainPipeClass]) 

Does not throw an error and does not create an object - I'm not sure which type.

Answer 2

You can always simply compile the working version of .NET that you referenced above in exe and let it accept parameters as needed to create SSIS packages. Then use Powershell to invoke the executable with parameters as needed.