Using SSIS 2008 Components in SQL Server Data Tools with SSIS 2012

Update: 2012-05-07: Apparently, this method does work for SSIS 2005 tasks. However, these extensions should not use the native interfaces. Thanks to Matt Masson for the correction.

For the next few weeks, the topic of upgrading SSIS 2008 packages to SSIS 2012 will be keeping me very busy. I am preparing to speak on the topic three times at upcoming developer events: SQL Saturday #112, SQL Saturday #132 and TechEd NA 2012.

The most pressing issue I face in upgrading to SSIS 2012 is the use of many custom SSIS components (I’ve listed a few I use often below). These components are designed and built to work based on SSIS 2008 class libraries and COM wrappers. Those are essentially strongly-typed .NET assemblies provided with the client tools SDK of SQL Server 2008 (R2). How do we use these in SSIS 2012?

TechNet states:

… You can use the current release of SQL Server Integration Services tools to run and manage packages that include SQL Server 2008 custom components. To use SQL Server Data Tools to design packages that include SQL Server 2008 custom components, you need to modify the devenv.exe.config file. …

And that’s all the guidance provided in BOL that I can find. The SSIS Team Blog does have a few more words to say here.

Concept

Microsoft has (by their description) gone through great lengths of easing the upgrade pain we all experienced moving from SSIS 2005 to SSIS 2008. One of their primary goals was to make packages from 2008 “just work” in 2012. That sounds easy, of course, until you realize that the technical basis of SSIS has changed: where custom components were developed for the .NET Framework 3.5 SP 1 in SSIS 2008, SSIS 2012 targets .NET Framework 4.0. The managed wrappers for SSIS have also changed version numbers from 10.0.0.0 to 11.0.0.0.

This is a case for assembly redirection to the rescue! You can read more about the mechanics of assembly redirection in the MSDN Library, but essentially assembly redirection allows a .NET application to load a version of a component at runtime that’s different from what was specified at compile time. At the time your custom components were built, they refered to the 10.0.0.0 versions of SSIS libraries. These versions may exist on your SQL Server 2012 box (for example if you do a side-by-side install), but if they do, they are inadequate to use in SSIS 2012 packages.

The assemblies that need to be redirected from version 10.0.0.0 (ships with 2008) to version 11.0.0.0 (ships with 2012) are:

  • Microsoft.SqlServer.ManagedDTS
  • Microsoft.SqlServer.DTSRuntimeWrap
  • Microsoft.SqlServer.DTSPipelineWrap
  • Microsoft.SqlServer.PipelineHost

Microsoft has already provided this assembly redirection in the following executables’ config files:

  • DTExec.exe
  • DTExecUI.exe
  • dtshost.exe
  • DTSWizard.exe
  • DTUtil.exe

This means that packages from SSIS 2008 can run just fine in SSIS 2012, even if they employ SSIS extensions. But that doesn’t apply to Visual Studio: the assembly redirection entries in the VS 2010 config file (and there are quite a few of them) don’t include the SSIS libraries.

Locating the Config File

In order to make it work in the SSIS 2012 designer, we must add that redirection ourselves in the Visual Studio 2010 configuration file. The config file in a standard installation of SQL Server Data Tools (SSDT) or full-blown edition of Visual Studio 2010 is found at

C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE

The name of the config file is devenv.exe.config (because the Visual Studio executable is devenv.exe).

Modifying the Config File

Once you have opened the configuration file (in any text or XML editor, but you may have to run it elevated), locate the assemblyBinding element (child of the runtime element). You will find there are already plenty of assembly bindings specified.

When modifying the config file, I add any custom ones at the top, prefixed and suffixed by XML comments, just in case I need to roll back my changes. These are the entries to add as child nodes of the runtime element:

<!-- BEGIN SSIS 2012 -->
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
  <dependentAssembly>
    <assemblyIdentity name="Microsoft.SqlServer.ManagedDTS" publicKeyToken="89845dcd8080cc91" culture="neutral" />
    <bindingRedirect oldVersion="10.0.0.0" newVersion="11.0.0.0"/>
  </dependentAssembly>
</assemblyBinding>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
  <dependentAssembly>
    <assemblyIdentity name="Microsoft.SqlServer.DTSRuntimeWrap" publicKeyToken="89845dcd8080cc91" culture="neutral" />
    <bindingRedirect oldVersion="10.0.0.0" newVersion="11.0.0.0"/>
  </dependentAssembly>
</assemblyBinding>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
  <dependentAssembly>
    <assemblyIdentity name="Microsoft.SqlServer.DTSPipelineWrap" publicKeyToken="89845dcd8080cc91" culture="neutral" />
    <bindingRedirect oldVersion="10.0.0.0" newVersion="11.0.0.0"/>
  </dependentAssembly>
</assemblyBinding>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
  <dependentAssembly>
    <assemblyIdentity name="Microsoft.SqlServer.PipelineHost" publicKeyToken="89845dcd8080cc91" culture="neutral" />
    <bindingRedirect oldVersion="10.0.0.0" newVersion="11.0.0.0"/>
  </dependentAssembly>
</assemblyBinding>
<!-- END SSIS 2012 -->

Results

After completing this process, you will be able to successfully execute the SSIS Package Upgrade Wizard if it previously failed on errors with custom components.

Limitations

This process will make your SSIS 2008 custom components work in SQL Server Data Tools with SSIS 2012… however, their UI (Edit, Advanced Edit) does not work. This means you are not able to really “use” them to develop your package, you can just make your package work in SSDT.

This may be sufficient to hold you over for a little while, but long-term, you will of course need to obtain a 2012 version of the component. I will blog about the process that’s involved in upgrading any components you have the source code for. Jesse Kraut has already blogged about it here.

Successful Components

I’ve successfully used this technique with the following components:

SSIS 2005 Components?

If your SSIS 2005 custom tasks don’t use native interfaces (but the .NET COM wrappers), this method should work also.

Advertisements

8 thoughts on “Using SSIS 2008 Components in SQL Server Data Tools with SSIS 2012

  1. Is it possible to continue to run/write SSIS R8 packages on SQL Server 2012? (To allow time to make the conversion, or delay upgrading SSIS indefinitely)

    Thanks in advance.

    1. @Ronn, Thanks for the question. Assuming by SSIS R8 you mean SSIS 2008 R2? Absolutely, provided that any third party controls are available on the SQL Server 2012 instance, your packages will just execute as expected. However, once you open a package and save it with SSDT, it will run on 2012 only.

  2. Adding this code in config file gives out this error:-
    Could not load file or assembly ‘Microsoft.DataTransformationServices.VsIntegration’ or one of its dependencies. The system cannot find the file specified.

    SSDT fails to open.. any suggestions?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s