8

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!

JNK
  • 58,244
  • 14
  • 113
  • 132
  • 1
    I'm upvoting this old question because I don't see why it has downvotes =/ – Josh Darnell Nov 04 '11 at 12:54
  • 2
    @jadarnel27 - they are mostly revenge downvotes. I pissed someone off and they downvoted the few questions I have asked (because it's free to downvote questions). I appreciate your sense of fairness. – JNK Nov 04 '11 at 13:15

2 Answers2

3

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.

Chad Miller
  • 32,809
  • 3
  • 24
  • 33
1

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.

Michael
  • 1,578
  • 5
  • 22
  • 40
  • Let's assume for security reasons it needs to be a self-contained powershell script that will be executed from SQL Server. – JNK Feb 02 '11 at 19:19