Wednesday 9 March 2016

Switching Target Server Versions for custom components

Case
Microsoft just released the first multi-version SSDT which can edit 2012, 2014 and 2016 SSIS packages. You can now change the Target Server Version in the properties of your SSIS project and then SSDT will convert the entire project to that SSIS version. But how can you make your custom components 'switchable'?
Target Server Version












Solution
For this example I will use my own Zip Task which has a 2008, 2012, 2014 and 2016 version. There are different approaches and this is only one of them. And it doesn't descibe how to create a custom task it self, but you can read that here and here.

In a Visual Studio Solution I have my 4 editions of the Zip Task. The projects and the assemblies have the same names and use the same strong name key file.
Solution with different editions of my Zip Task





















AssemblyInfo
If the assemblies are identical there can only by one assembly in the GAC at a time, but we need them all in the GAC. So I gave my assemblies different versions. You can do that in the AssemblyInfo file of your project. See the red rectangles in the image below.
AssemblyInfo















GAC
Now I can have all versions in the GAC. Different version number, but same strong name key.
2012, 2014 and 2016 version in the GAC












This looks very similar to the out-of-the-box tasks and transformations like the FTP task. Different version number, but the same strong name key.
Different editions of the FTP Task in the GAC











UpgradeMappings - Mappings
Now we need to tell SSIS how to convert between 2012, 2014 and 2016. There is a subfolder in the DTS map called UpgradeMappings. Each version of SSIS has its own folders (even the 2008 version):

SSIS 2012: C:\Program Files (x86)\Microsoft SQL Server\110\DTS\UpgradeMappings
SSIS 2014: C:\Program Files (x86)\Microsoft SQL Server\120\DTS\UpgradeMappings
SSIS 2016: C:\Program Files (x86)\Microsoft SQL Server\130\DTS\UpgradeMappings

In each of these folders you need to add an XML mapping file with the name of your task (or transformation/enumerator/connection manager). For example ZipTaskMapping.xml. In this file you need to tell which assembly is the old version and which one is the new version of your task. For example: in the SSIS 2014 UpgradeMappings folder I will use the 2012 assembly as old and the 2014 as the new assembly. The assembly strong name string can be copied from the UITypeName property in your task code (search for "UITypeName"). You can also find an example file called mapping.xml.sample in the folder which you can use to start with. Here is my 2014 example:
 
<?xml version="1.0" encoding="utf-8"?>
<Mappings xmlns="http://www.microsoft.com/SqlServer/Dts/UpgradeMapping.xsd">
  <!-- Extensions -->
  <ExtensionMapping tag="ZipTask"
      oldAssemblyStrongName="ilionx.SSIS.Tasks.Zip, ilionx.SSIS.Tasks.Zip, Version=1.12.0.0, Culture=neutral, PublicKeyToken=4b5c6d755ae87bf7"
      newAssemblyStrongName="ilionx.SSIS.Tasks.Zip, ilionx.SSIS.Tasks.Zip, Version=1.14.0.0, Culture=neutral, PublicKeyToken=4b5c6d755ae87bf7" />
</Mappings>


The tag property is for logging purposes only, but it seems to be handy to put your tasks name in it: ZipTask.

UpgradeMappings - Extensions
In a second file we need is to set an alias for our assembly. This should be done for SSIS 2014 and later only. When you use this file, SSDT will change the package XML code for your task (when you add it to the package). Instead of using the strong name string as CreationName it will use this alias. The filename should be something like ZipTaskExtensions.xml. Here is my 2014 example:
<?xml version="1.0" encoding="utf-8" ?>
<Extensions xmlns="http://www.microsoft.com/SqlServer/Dts/Extensions.xsd">
  <Tasks>
    <Task Identifier="ZipTask" Model=".NET">
      <CreationName>ilionx.SSIS.Tasks.Zip, ilionx.SSIS.Tasks.Zip, Version=1.14.0.0, Culture=neutral, PublicKeyToken=4b5c6d755ae87bf7</CreationName>
    </Task>
  </Tasks>
</Extensions>

I used the same Identifier for SSIS 2016. The only difference is the version number. The rest is identical. After adding this extensions file, restart SSDT and add your custom task to the control flow and check the package XML.
Difference in XML code














After you have done this for your own custom SSIS components you can safely switch between the Target Server Versions (or upgrade packages with your custom component in it).

You can download the XML example files here or download and install my ZipTask and browse to the UpgradeMappings folders. I have added these XML files in my installers. The installer now copies the assemblies to the task folder and installs them in the GAC. The XML files are copied to the UpgradeMappings folder. Some extra info on upgrademappings on MSDN and here a blogpost from Matt Masson.

Thanks to the SSIS team for pointing me in the right direction with the XML files and letting me play with an early edition of the multi version SSDT!

What I still need to figure out is how to have only one edition of the projects and just switch references if I want to create different versions for 2008, 2012, 2014, 2016, etc. For one component that doesn't change that much this approach is no problem. If you have a whole bunch of custom components and that perhaps often change then this approach with different projects for each version of SSIS is hard to maintain.


Related Posts Plugin for WordPress, Blogger...