Moving SSIS Packages with ADO.NET Destinations Between 2008 R2 and 2008

As noted by Matt Masson from the Integration Services team, not much has changed in SSIS 2008 R2. In fact, R2 is pretty much identical, with the exception of the ADO.NET Destination. So if you are developing packages, you can build them using the 2008 or 2008 R2 version of BIDS, and they can be used in either environment successfully, as long as the package doesn’t have an ADO.NET Destination. If it does, a package developed in the 2008 R2 version of BIDS will give errors when you open on a machine with the 2008 version of SSIS installed. This is because the 2008 version of the ADO.NET Destination doesn’t know what to do with the new property added to the R2 version.

This wouldn’t be a big deal, if you could have side-by-side installs of BIDS 2008 and BIDS 2008 R2, but the install for R2 replaces the 2008 version of BIDS. So, if you need to move packages developed in 2008 R2 to 2008 (a common scenario for me), you have to do a little extra work. There are a few values that need to be removed or changed by directly editing the package XML.

As always, it’s a good idea to make a backup of your package before editing the XML directly.

Open the package in your favorite text or XML editor, and look for the section that contains <components>. Underneath that, you need to locate the <component> tag that relates to your ADO NET Destination (the sample below is easy, since the name is “ADO NET Destination”, but that’s not the typical case (you do give your components meaningful names, right?).

<component id="16" name="ADO NET Destination" componentClassID="{2E42D45B-F83C-400F-8D77-61DDE6A7DF29}" description="Writes to a database using ADO.NET provider." localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="1" pipelineVersion="0" contactInfo="Writes to a database using ADO.NET provider.;Microsoft Corporation; Microsoft SqlServer v10; © 2007 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;1">
  <properties>
    <property id="23" name="TableOrViewName" dataType="System.String" state="default" isArray="false" description="The Destination database table name." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version= 10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="Notify">"sample"</property>
    <property id="24" name="BatchSize" dataType="System.Int32" state="default" isArray="false" description="Specify the number of rows per batch. The default is 0 which uses the same size as SSIS internal buffer. " typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">0</property>
    <property id="25" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates infinite time-out. " typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">30</property>
    <property id="26" name="UseBulkInsertWhenPossible" dataType="System.Boolean" state="default" isArray="false" description="Specifies whether to use the SqlBulkCopy interface to improve the performance of bulk insert operations. Only certain providers support this interface." typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">true</property>
    <property id="27" name="UserComponentTypeName" dataType="System.String" state="default" isArray="false" description="" typeConverter="" UITypeEditor="" containsID="false" expressionType="None">Microsoft.SqlServer.Dts.Pipeline.ADONETDestination, Microsoft.SqlServer.ADONETDest, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</property>
  </properties>

Once you’ve located the right <component> tag, you need to make two changes:

One, change the version=”1” attribute in the <component> tag to version=”0”.

<component id="16"
           name="ADO NET Destination"
           componentClassID="{2E42D45B-F83C-400F-8D77-61DDE6A7DF29}"
           description="Writes to a database using ADO.NET provider."
           localeId="-1"
           usesDispositions="true"
           validateExternalMetadata="True"
           version="0"
           pipelineVersion="0"
           contactInfo="Writes to a database using ADO.NET provider.;Microsoft Corporation; Microsoft SqlServer v10; © 2007 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;1">

Second, remove the entire <property name=”UseBulkInsertWhenPossible> element. You can comment it out, as shown below, or just delete it.

    <property id="25" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates infinite time-out. " typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">30</property>
    <!--<property id="26" name="UseBulkInsertWhenPossible" dataType="System.Boolean" state="default" isArray="false" description="Specifies whether to use the SqlBulkCopy interface to improve the performance of bulk insert operations. Only certain providers support this interface." typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">true</property>-->
    <property id="27" name="UserComponentTypeName" dataType="System.String" state="default" isArray="false" description="" typeConverter="" UITypeEditor="" containsID="false" expressionType="None">Microsoft.SqlServer.Dts.Pipeline.ADONETDestination, Microsoft.SqlServer.ADONETDest, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</property>

If you have a lot of packages to move back and forth, you probably don’t want to hand edit this each time. I wrote a little application that will process a folder of .dtsx files, and strip the 2008 R2 information out. This is done using an XSLT transform (available in the source code). The application and source are available to download from my SkyDrive.

22 Comments

  1. http:// says:

    hi your blog is very nice.

  2. Hi John.

    I’ve not extensivly tested, but I think that the “version” attribute is not required for either version of SSIS (R2 or SP1) and “UseBulkInsertWhenPossible” property is not required for R2.

    Based on that, and just because I can and for the fun of it, I rewrote your tool as a .NET 4.0 console application using XLINQ instead of XSLT.

    Here it is:

    namespace Ssis2008R2_To_Ssis2008
    {
    using System;
    using System.IO;
    using System.Linq;
    using System.Xml.Linq;

    static class Program
    {
    static void Main(string[] args)
    {
    string path;

    if ((args == null) || (args.Length == 0))
    {
    path = Environment.CurrentDirectory;
    }
    else if (args.Length == 1)
    {
    path = args[0];
    }
    else
    {
    Console.WriteLine(“Usage: [path]“);
    return;
    }

    Console.WriteLine(“Converting packages in {0}”, path);

    Directory.EnumerateFiles(path, “*.dtsx”, SearchOption.TopDirectoryOnly)
    .AsParallel()
    //.WithDegreeOfParallelism(1)
    .ForAll(ConvertPackage);
    }

    static void ConvertPackage(string path)
    {
    var info = new FileInfo(path);

    if (info.Attributes.HasFlag(FileAttributes.ReadOnly))
    {
    return;
    }

    var dtsx = XDocument.Load(path, LoadOptions.None);

    var converted = false;

    var components = from c in dtsx.Descendants(“component”)
    where c.Descendants(“property”).SingleOrDefault(p => p.Attribute(“name”).Value == “UserComponentTypeName” && p.Value.StartsWith(“Microsoft.SqlServer.Dts.Pipeline.ADONETDestination”)) != null
    select c;

    foreach (var c in components)
    {
    var version = c.Attribute(“version”);
    if (version != null)
    {
    version.Remove();
    converted = true;
    }

    var useBulkInsertWhenPossible = c.Descendants(“property”).SingleOrDefault(p => p.Attribute(“name”).Value == “UseBulkInsertWhenPossible”);

    if (useBulkInsertWhenPossible != null)
    {
    useBulkInsertWhenPossible.Remove();
    converted = true;
    }
    }

    if (converted)
    {
    dtsx.Save(path, SaveOptions.None);

    Console.WriteLine(“{0} converted”, path);
    }
    }
    }
    }

  3. Chad McKee says:

    Sooo helpful. Thank you, thank you, thank you!!

  4. scott says:

    Thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you!

  5. Subrat says:

    Hi,

    I have followed the instuction to migrate SSIS packages developed in BIDS 2008 r2 to BIDS 2008, and then deployed to a 2008 box. But, it still says me ADO NET Destination version mismatch(component not upgradeable, component missing, not registered etc..).

    Let me know if I am doing something wrong.

    Thanks

  6. Shanon Bailey says:

    WOW! Thank you SO much! I’ve been hung up on this for a couple of days and stumbled upon your post. This FIXED my issue!!! Thanks!!!!!!!!!

  7. Actually, when searching for the component, you can search for the componentClassID which specifies the type of component. This should be: {2E42D45B-F83C-400F-8D77-61DDE6A7DF29}.
    This helps if you have named your components something other than ADO NET Destination.

  8. Randy Strommen says:

    Well, we’re running SQL Server 10.0.4000, SS 2008 SP2.

    I’ve run both programs, and I get the same error result.

    Error: 2012-03-16 14:57:29.59
    Code: 0xC0047062
    Source: Transfer Canola Data to Holding Transfer Canola Data to Holding (ADODest Holding [1550])
    Description: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatch
    Exception: The version of component “ADODest Holding” (1550) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]]
    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper100 wrapper Int32 lPipelineVersion)
    End Error

    Here is the component. The version=”1″ has been removed, and the UseBulk… property has been removed. I’m not sure where the discrepancy is located.

    “dbo”.”tblHolding”
    0
    30
    Microsoft.SqlServer.Dts.Pipeline.ADONETDestination, Microsoft.SqlServer.ADONETDest, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

    • Randy Strommen says:
                  <component id="1550" name="ADODest Holding" componentClassID="{2E42D45B-F83C-400F-8D77-61DDE6A7DF29}" description="Writes to a database using ADO.NET provider." localeId="-1" usesDispositions="true" validateExternalMetadata="True" pipelineVersion="0" contactInfo="Writes to a database using ADO.NET provider.;Microsoft Corporation; Microsoft SqlServer v10; © 2007 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;1"&gt;
                    <properties>
                      <property id="1557" name="TableOrViewName" dataType="System.String" state="default" isArray="false" description="The Destination database table name." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version= 10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="Notify">"dbo"."tblHolding"</property>
                      <property id="1558" name="BatchSize" dataType="System.Int32" state="default" isArray="false" description="Specify the number of rows per batch. The default is 0 which uses the same size as SSIS internal buffer. " typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">0</property>
                      <property id="1559" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates infinite time-out. " typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">30</property>
                      <property id="1561" name="UserComponentTypeName" dataType="System.String" state="default" isArray="false" description="" typeConverter="" UITypeEditor="" containsID="false" expressionType="None">Microsoft.SqlServer.Dts.Pipeline.ADONETDestination, Microsoft.SqlServer.ADONETDest, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</property>
                    </properties>
      
  9. Andrew says:

    Thank you so much for this. It stopped me banging my head against a wall. Worked perfectly first time.

  10. Kiran says:

    Worked perfectly for me. Thanks a lot

  11. Rusty says:

    Thank you very much for providing the transformation utility. It sure beats editing 20k line package files!

  12. David Bridge says:

    Thanks for this very helpful information.

    Its a big shame that MS don’t put a hotfix for this so that a 2008 SSIS can be executed.
    This is clearly just a case of MS trying to force needless expensive server upgrades. How hard can it be for MS to check the version of the package and server and simply degrade the additional functionality of the package.

    Not hard I think.

    Thanks for your efforts in this and for letting the world know

    Dave

  13. ashish says:

    Amazing thanks a ton :)

  14. Marius says:

    Thanks! Life saver!

  15. Steve says:

    We have a SSIS package that was written against SQL Server 2008 R2 and it has ADO.NET Destination tasks which is failing with the same messages as seen above. I modified the .dtsx file to change the version numbers for those components to version = 0 and removed the UseBulkInsertWhenPossible property but after deploying the package it does not fail but it also does not write any values to the tables that are defined in those ADO.NET Destination tasks.

Leave a Reply