PowerShell and SSIS

Up until now, most of my use of PowerShell has been against SSAS, but I recently had a need to setup a number of SSIS packages for some relatively large CSV files. My problem was that the CSV files had about 125 columns and 120 of them (10 years of monthly figures) need to be inserted into Numeric(18,5) columns in SQL Server. However the connection manager auto-detected these columns on my flat file connection manager as DT_FLOAT. Changing these columns to DT_NUMERIC and setting the scale to 5 quickly proved to be tedious.

So I fired up PowerShell Analyzer and started poking around. Unfortunately I soon found that the combination of SSIS and PowerShell proved to be a little problematic. I think that the issue comes about because PowerShell tries really hard to help you out as far as object types are concerned. It has this concept of Type Adapters which usually make things easier. Unfortunately under the covers it appears that the SSIS API's use a relatively thin .Net wrapper over the top of some COM objects. I don't know for sure, but I'm guessing that the COM might be there as a partial bridge from the unmanaged code through to .Net.

But in the case of PowerShell this is an issue as it seems to be able to "see" through some of the .Net objects and interfaces to the COM based code and no matter how I tried to cast some of these objects as a specific type, PowerShell kept returning them as System.__ComObject.

Not to be one to admit defeat (and being too lazy to edit 120 columns by hand) I pulled together some C# to work with the interfaces that I need and then exposed this as PowerShell cmdlets. This both simplified the script and got around the issue of PowerShell not casting to the interfaces I needed.

The following script opens a package, grabs a reference to the columns property of a given csv connection and then loops through the columns, changing all the datatypes and then finally saves the package.

add-PSSnapin powerSSIS $pkg = get-ssisPackage "C:\temp\Test Package.dtsx" $fileCon = $pkg.Connections["CSV file"] $col = $fileCon.Properties["Columns"].GetValue($fileCon)


for ($i = 4; $i -lt $col.Count; $i++) { $name = get-ssisflatfileColumnName $pkg "CSV File" $i if ($name.startsWith("Column")) { set-ssisflatfileColumnName $pkg "CSV File" $i "Mth$($i-2)" $c = get-SSISFlatFileColumn $pkg "CSV File" $i $c.DataType = [Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType]::DT_Numeric $c.DataPrecision = 18 $c.DataScale = 5 } } set-SSISPackage $pkg "C:\temp\Test Package.dtsx"


I have highlighted the cmdlets I built in red. There weren't many and the ones to open and save packages were not strictly necessary, they just helped take a few lines of plumbing code out of my script. It was really just the cmdlets that get and set the column name and the one that gets the column object that I could not do in "native" PowerShell.

I am still looking into this as I can't help feeling that there must be a better way of doing this, but at the moment everything I have tried has been unsuccessful.

If anyone has any experience in this area, or thinks that this is either a good or bad idea, I would be interested to hear from you.

Print | posted on Monday, July 14, 2008 12:21 AM