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();
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
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).
Robert C. Cain, MVP, MCTS, N4IXT
Senior Consultant, Pragmatic Works
Technical Contributor, Pluralsight
@ArcaneCode | http://arcanecode.com