Every now and then I've come across scenarios where I need to update a lot of packages all in the same way. The usual scenario revolves around a group of packages all having been built off the same package template, and something needs to updated to keep up with new requirements, a new logging standard for example.You'd probably start by updating your template package, but then you need to address all your existing packages. Often this can run into the hundreds of packages and clearly that's not a job anyone wants to do by hand. I normally solve the problem by writing a simple console application that looks for files and patches any package it finds, and it is an example of this I'd thought I'd tidy up a bit and publish here.

This sample will look at the package and find any top level Execute SQL Tasks, and change the SQL Statement property to use an expression. It is very simplistic working on top level tasks only, so nothing inside a Sequence Container or Loop will be checked but obviously the code could be extended for this if required.

The code that actually sets the expression is shown below, the rest is just wrapper code to find the package and to find the task.

/// <summary>
/// The CreationName of the Tasks to target, e.g. Execute SQL Task
/// </summary>
private const string TargetTaskCreationName = "Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask,
 Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

/// <summary>
/// The name of the task property to target.
/// </summary>
private const string TargetPropertyName = "SqlStatementSource";

/// <summary>
/// The property expression to set.
/// </summary>
private const string ExpressionToSet = "@[User::SQLQueryVariable]";

.... 


// Check if the task matches our target task type
if (taskHost.CreationName == TargetTaskCreationName)
{
    // Check for the target property
    if (taskHost.Properties.Contains(TargetPropertyName))
    {
        // Get the property, check for an expression and set expression if not found
        DtsProperty property = taskHost.Properties[TargetPropertyName];
        if (string.IsNullOrEmpty(property.GetExpression(taskHost)))
        {
            property.SetExpression(taskHost, ExpressionToSet);
            changeCount++;
        }
    }
}

This is a console application, so to specify which packages you want to target you have three options:

• Find all packages in the current folder, the default behaviour if no arguments are specified

TaskExpressionPatcher.exe

• Find all packages in a specified folder, pass the folder as the argument

TaskExpressionPatcher.exe C:\Projects\Alpha\Packages\

• Find a specific package, pass the file path as the argument

TaskExpressionPatcher.exe C:\Projects\Alpha\Packages\Package.dtsx

The code was written against SQL Server 2005, but just change the reference to Microsoft.SQLServer.ManagedDTS to be the SQL Server 2008 version and it will work fine.

If you get an error Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008… then check that the package is from the correct version of SSIS compared to the referenced assemblies, 2005 vs 2008 in other words.

Download

Sample Project TaskExpressionPatcher.zip (6 KB)