Unwrapping a SSIS COM object

PowerShell Help
1

I’m having some issues with getting a conversion to a COM object to work right (at least I think that’s my problem).

I have a need to generate over 1,500 SSIS (SQL Server Integration Services) packages that all do pretty much the same thing. We created a template SSIS package, and within a PowerShell script I can access that package, make changes to it, and save it out as a new package. I’m using the DTS namespace for everything, as opposed to mucking around in XML. I’ve been able to do a huge amount so far, such as setting variables, connection managers, and the like.

In our template, the OLEDB Source component in the Data Flow Task is set from a variable. Once I change the variable, I need to be able to refresh the metadata associated with the control so it will update the output columns collection. Without doing that, the OutputColumnCollection shows 0 columns in the output. My ultimate goal is to dive into the OutputColumnsCollection so I can set the SortKey property on the correct key columns.

In C#, the examples I have seen ( http://www.selectsifiso.net/?p=337 is a good example) looks something like:

CManagedComponentWrapper instance = source.Instantiate();
instance.ProvideComponentProperties();
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.RefreshConnections();

In my code I get a reference to the source component (the same as the variable in line 1 above). I know it is valid, because I can set other properties of it such as the name and its connection managers. So first I tried a simple:

$instance = $source.Instantiate();

Which works, or at least doesn’t generate any error. But then I try the

$instance.ProvideComponentProperties()

and get an error that the method doesn’t exist (even though documentation says it should be a method on CManagedComponentWrapper). I then thought maybe I need to put a wrapper around it, so tried

$component = [System.Runtime.InteropServices.Marshal]::CreateWrapperOfType($instance, [Microsoft.SQLServer.DTS.pipeline.Wrapper.CManagedComponentWrapper])

But this gave an error "Exception calling “CreateWrapperOfType” with “2” argument(s): “The type must be __ComObject or be derived from __ComObject.” even though a GetType on the $comp shows it is a ComObject. I get the same error if I substitute the $source variable for the first parameter, it being the OLDEDB Source control.

Somehow I need to get a proper reference to this instance, but not quite sure where I’m going wrong. Any suggestions are appreciated. Meanwhile I may investigate calling C# code from PowerShell and getting around it that way, although I’d prefer to stay native PowerShell if I can. While I know both PowerShell and C#, I know that the next guy to come along may not (and probably won’t since I mostly work with BI people).

Thanks,

Robert 

--

Robert C. Cain, MVP, MCTS, N4IXT
Senior Consultant, Pragmatic Works
Technical Contributor, Pluralsight
@ArcaneCode | http://arcanecode.com

2

You can probably do what you need to do with Reflection, and not worry about a wrapper. In C#, you can cast __ComObject instances to an interface, but as far as I know, that’s not an option in PowerShell.

Try this:

$instance = $source.Instantiate()
$type = $instance.GetType()
$flag = [System.Reflection.BindingFlags]::InvokeMethod

$type.InvokeMember('ProvideComponentProperties', $flag, $null, $instance, $null)
$type.InvokeMember('AcquireConnections', $flag, $null, $instance, @($null))
$type.InvokeMember('ReinitializeMetaData', $flag, $null, $instance, $null)
$type.InvokeMember('RefreshConnections', $flag, $null, $instance, $null)
3

I faced this problem some time ago, and I was able to use Powershell to create and execute the SSIS packages from an SSIS template. The way I that approached it was to create an SSIS template with the connections to the source and destination databases and then use the SSIS API to create the table mappings. You can check this post at The Admin As A Toolsmith: Create and execute SSIS packages with Powershell which shows what I did in greater detail. Please feel free to email me with any additional questions. You can try the following to create the OLEDB source and destination adapters:

# The path to the template
$template = '.\Template\OLEDBTemplate.dtsx'

# Load the template  
$app = New-Object Microsoft.SqlServer.Dts.Runtime.Application
$package = $app.LoadPackage($template, $null)
$package.CreatorComputerName = $env:COMPUTERNAME
$package.Name = "SSIS_" + $tablename

# Get the Taskhost
$XferTaskTH = [Microsoft.SqlServer.Dts.Runtime.TaskHost]$dataflowtask

# Get the pipeline
$DataPipe = [System.Runtime.InteropServices.Marshal]::CreateWrapperOfType($XferTaskTH.InnerObject, [Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipeClass])

# Set the OLEDB Source
$oledbSource = $DataPipe.ComponentMetaDataCollection.New()

$oledbSource.ComponentClassID = "DTSAdapter.OLEDBSource.2"

$instanceSource = [System.Runtime.InteropServices.Marshal]::CreateWrapperOfType($oledbSource.Instantiate(), [Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass])

$instanceSource.ProvideComponentProperties()

$oledbSource.RuntimeConnectionCollection.Item(0).ConnectionManager = [Microsoft.SqlServer.Dts.Runtime.DtsConvert]::GetExtendedInterface($package.Connections[0])

$oledbSource.RuntimeConnectionCollection.Item(0).ConnectionManagerID = $package.Connections[0].ID



# Set Source Parameters
$sourcetablename = "`"dbo`".`"$tablename`""
$null = $instanceSource.SetComponentProperty("AccessMode", 0)
$null = $instanceSource.SetComponentProperty("OpenRowset", "$sourcetablename")


# Refresh the source table metadata
$instanceSource.AcquireConnections($null)
$instanceSource.ReinitializeMetaData()
$instanceSource.ReleaseConnections()

# Set the OLEDB Destination
$oledbDestination = $DataPipe.ComponentMetaDataCollection.New()

$oledbDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.2"

$instanceDestination = [System.Runtime.InteropServices.Marshal]::CreateWrapperOfType($oledbDestination.Instantiate(), [Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass])

$instanceDestination.ProvideComponentProperties()

$oledbDestination.RuntimeConnectionCollection.Item(0).ConnectionManager = [Microsoft.SqlServer.Dts.Runtime.DtsConvert]::GetExtendedInterface($package.Connections[1])

$oledbDestination.RuntimeConnectionCollection.Item(0).ConnectionManagerID = $package.Connections[1].ID

# Set the destination parameters
$destinationtablename = "[dbo].[$tablename]"
$null = $instanceDestination.SetComponentProperty("AccessMode", 3)
$null = $instanceDestination.SetComponentProperty("OpenRowset", "$destinationtablename")
$null = $instanceDestination.SetComponentProperty("FastLoadKeepIdentity", $true)
$null = $instanceDestination.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS")


# Refresh the destination table metadata
$instanceDestination.AcquireConnections($null)
$instanceDestination.ReinitializeMetaData()
$instanceDestination.ReleaseConnections()