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:

[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 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 1

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:

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

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

Answer 2

You could always just compile the working .NET version you referenced above into an exe, and allow it to accept parameters as needed in order to create the SSIS packages. Then, use Powershell to call the executable with the parameters as needed.