SSIS Packages: Update From Code

We often want to modify many SSIS packages at once. We may want to change the password on all packages within a folder, or regenerate the unique identifier on each SSIS package to make sure that it’s unique across multiple copies of the package. This is a case where a little scripting knowledge can save you a lot of time.

[alert type=”error”]WARNING #1 – The first hurdle is setting a reference to the right version of Microsoft.SqlServer.ManagedDTS.dll and you should find it in the GAC. If you reference the wrong version, you will make the package unusable. So, if you are updating SQL Server 2012 packages use the version 11 dll, and if you are updating SQL Server 2008 R2 packages use version 10.[/alert]

[alert type=”error”]WARNING #2 – I expected an Exception from LoadPackage when I tested passing an incorrect password. That is NOT what happened, so BEWARE. If you aren’t 100% sure you will be supplying the right password, make sure that you also pass an IDTSEvents interface to LoadPackage, but that it is outside the scope of this post.[/alert]

Changing the SSIS Package Password

SSIS Packages often have the ProtectionLevel set to EncryptSensitiveWithPassword, and as packages are passed across environments, that password is different. So, having a script ready to change the password on many SSIS packages can save a lot of time:

void ChangePassword(string fullPath, string originalPassword, string newPassword) {
  Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
  app.PackagePassword = originalPassword;
  Microsoft.SqlServer.Dts.Runtime.Package package = app.LoadPackage(fullPath, null);

  package.PackagePassword = newPassword;
  package.ProtectionLevel = Microsoft.SqlServer.Dts.Runtime.DTSProtectionLevel.EncryptSensitiveWithPassword;
  app.SaveToXml(fullPath, package, null);
}

SSIS Package RegenerateID

Code reuse within SSIS is not ideal. One way to allow for code reuse is to create packages that can server multiple purposes. Package configurations and variables allow you make a single package very flexible, and change it’s behavior at runtime. One downside can be that any logging will contain the same sourceid for each usage. One way to make the intent clearer is to make a copy of the package at deploy time for each usage, and sure each copy of the package has a unique id:

void RegenerateID(string fullPath, string password) {
  Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

  if (password.Length > 0)
    app.PackagePassword = password;

  Microsoft.SqlServer.Dts.Runtime.Package package = app.LoadPackage(fullPath, null);
  string originalID = package.ID.ToString();
  package.RegenerateID();

  if (password.Length > 0) {
    package.PackagePassword = password;
    package.ProtectionLevel = Microsoft.SqlServer.Dts.Runtime.DTSProtectionLevel.EncryptSensitiveWithPassword;
  }

  app.SaveToXml(fullPath, package, null);
}

In the past, I would always create .NET utilities for small tasks like this, but Visual Studio projects and solutions are too cumbersome quick hitters like this. These days I prefer to just use LINQPad as my harness. This code could also easily go into a Script Task in an SSIS package, too.