Custom Extensions in SQL Server 2008

The introduction of new SSIS features in the SQL Server 2008 release and beyond made it necessary for the SSIS team to introduce some breaking changes that affect SSIS custom extensions built for SQL Server 2005. This post is intended to clarify what those breaking changes are and how to migrate your SQL Server 2005 SSIS custom extensions to SQL Server 2008.

Updating Your Extension

1. Update references for SSIS assemblies from 9.0.242.0 to 10.0.0.0
2. Rename IDTSxxxx90 objects to IDTSxxxx100
3. Follow any deprecated code warnings
4. (Optional) Reversion your extension (see below)

You can check out my previous post for more details (including a simple regex you can use in visual studio to do the interface name changes).

Deploying Your Extension

Deploying your extension is similar to 2005. The only difference is that you would copy your extension to a directory under %ProgramFiles%/Microsoft SQL Server/100/DTS, instead of 90/DTS.

Whether or not you choose to reversion (changing the extension's assembly version for managed code, or the ProgID/CLSID for native code) will affect the way your packages that consume the extension will be upgraded.

Keeping the same Version

If the version number of your extension doesn't change, there are no special steps needed for upgrade. Your 2005 packages should upgrade to 2008 format with no additional modifications.

Changing the Version

If you decide to reversion your extension (you'd do this if you want to be able to install Yukon and Katmai versions side by side), you'll need to provide an upgrade mapping file so the SSIS upgrade engine knows how to map your assembly.

Upgrade Mappings

The next Katmai CTP will add a new "UpgradeMappings" directory. SSIS will read the XML mapping files placed in this directory when upgrading a package.

Sample mapping file:

<?xml version="1.0" encoding="utf-8"?>
<Mappings xmlns="http://www.microsoft.com/SqlServer/Dts/UpgradeMapping.xsd">
  <!-- Connection Managers -->
  <ConnectionManagerMapping tag="MSOLAP - Analysis Services connections" 
                            oldValue="MSOLAP90"
                            newValue="MSOLAP100" />
  
  <!-- Extensions -->
  <ExtensionMapping tag="mycustom extension"
      oldAssemblyStrongName="MyCustomAssembly.MyCustomTask, MyCustomAssembly,

 Version=1.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
      newAssemblyStrongName="MyCustomAssembly.MyCustomTask, MyCustomAssembly,

 Version=2.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

  <!-- Providers -->
  <ProviderMapping tag="SQL Native Client - Version dependent" 
                   oldValue="sqlncli.1"
                   newValue="sqlncli10.1" />
  <ProviderMapping tag="SQL Native Client - Version independent"
                   oldValue="sqlncli"
                   newValue="sqlncli10" />
  <ProviderMapping tag="MSOLAP - Analysis Services connections"
                   oldValue="msolap.3"
                   newValue="msolap.4" />
</Mappings>

The interesting one for most users will be the <ExtensionMapping> element.

Attribute	Description
tag	Text describing your extension (used for logging)
oldAssemblyStrongName	The strong name of your extension's assembly in 2005
newAssemblyStrongName	The strong name of your extension's assembly in 2008

You have two options when mapping assemblies - you can use the fully qualified class name (like in the example - <assembly name>, <class>, Version=<version>, Culture=<culture>, PublicKeyToken=<key>), or you can provide the strong name of the assembly itself, without the class. This will map all classes from the old assembly to the new assembly.

For example:

<ExtensionMapping tag="mycustom extension"
    oldAssemblyStrongName="MyCustomAssembly.MyCustomTask, 
Version=1.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
    newAssemblyStrongName="MyCustomAssembly.MyCustomTask,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

Once you've deployed a mapping file to the UpgradeMappings directory, SSIS will be able to upgrade packages containing your custom extensions. Note, the mappings are only needed during package upgrade - once all of your packages have been upgraded, you can remove them.