PowerShell manipulation of SSIS packages

One of the developers has been hand-coding some very repetitive SSIS packages. They perform a full replication of data out of an Informix database into our SQL Server. For the final database, he had about 50 tables that needed to be processed. I convinced him that we can make his life and maintenance better by creating lots of little packages vs the behemoth package with umpteen data flows in it.

I built out a template package, PolSumAgentCountyDir.dtsx, that is an Execute SQL Task, wired to a Data Flow. The trick is the expressions. I have one variable, base_table that drives the operation of the package. There are variables that build the delete statement, source query and destination table. If I change the value of base_table from foo to bar, the OLE DB datasource in the package is going to get very angry because I've swapped out the meta data on it. Back in the DTS days, I could have passed a list to the process and it'd have been able to make it work but this is SSIS and that doesn't fly

The original approach was we were going to split the list of tables to replicate and meet in the middle. Each person would open the template, update the value of base_table, save the package with a name based on the table it's replicating (along with the SSIS's package's Name property) and then fix the meta data. Dull work, but doable.

If I were clever, know what I'd do? I'd spend an hour watching StarTrek FY Edition and writing some PowerShell. Calling the Function below with a fully qualified path to my template package, the table name and the English name of the table (ls_pol_sum => LivestockPolicySummary) results it instantiating an instance of the SSIS object model, loading the template package and programmatically manipulating it and saving it out with a new name. Dead simple but until you try it, or see it, you won't believe how so. I spent more time defining the hash table with table names to English than I did the code for manipulating objects. I'm sure some of that's due to my familiarity with the object model from my PowerShell SSIS deployment and maintenance script.

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.ManagedDTS") | out-null

# Utility script for making clones of a template SSIS package
# and modifying the variable (base_table) that drives operation
# of the package
# Created by Bill Fellows
# 2011-11-10

Function ModifyTemplate
{
    param
    (
        [string]$template,
        [string]$tableName,
        [string]$PackageSuffix
    )
    
    $events = $null
    $userName = $null
    $password = $null

    try
    {
        # open template package
        # save as package suffix
        # replace AgentCountyDir in template with suffix
        $newName = $template.Replace("AgentCountyDir", $PackageSuffix)
        $app = New-Object Microsoft.SqlServer.Dts.Runtime.Application
        $package = $app.LoadPackage($template, $null)

        # modify the table variable
        $package.Variables["base_table"].Value = $tableName
        $package.Name = $package.Name.Replace("AgentCountyDir", $PackageSuffix)
        $app.SaveToXml($newName, $package, $events)
    
    }
    catch
    {
        Write-Error ([string]::Format("Failed to do something. Table {0} on server {1}", $tableName, $PackageSuffix))
        Write-Error $_ | fl * -Force
        exit(0)
    }

}

Who loves PowerShell? This guy