Lazy developer – Powershell for SSIS

I’m sure this could be applied to a package in order to build a custom solution for SSIS ETL against multiple tables, without manually creating ETL for each table.

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" 

Darren Gosbell [MVP] - Random Procrastination