Click here to Skip to main content
13,358,478 members (53,906 online)
Click here to Skip to main content
Add your own
alternative version


5 bookmarked
Posted 9 Mar 2011

A method to migrate and automate exisitng DSTX SSIS package from SQL server 2005 to 2008 R2

, 9 Mar 2011
Rate this:
Please Sign up or sign in to vote.
This article will provide the first step towards migrating error free dtsx package to higher version of sql server ssis

Table of Contents

  1. 1.Introduction
  2. 2.Real Time Scenarios
  3. 3.Prerequisites
  4. 4.Code Implementation
  5. 5.References
  6. 6.Conclusion


The objective of this article is to help developer to create automated component for engagement which involves migration of existing dtsx packages from Sql Server 2005 to SQL Server 2008 R2. The idea is to present the most common migration that a given dtsx package may require to undergo changes. The only way to do is to change the set property of varibales in package and save in xml format dtsx.

2.Real Time Scenarios

  • If any engagement contains huge number of dtsx packages then manual changes will take lot of efforts and increase migration timelines.
  • This migration/automation targets only file system SSIS package.
  • This migration/automation involves removing common errors like protection level where dtsx package is ecrypted with User Key.DPAPI Algorithm used to encrypt data in package.
  • Using the above approach one can change the set properties of dtsx package and can overcome errors that occurred during migration.


  • VS2005 .net 2.0
  • Namespace: Microsoft.SqlServer.Dts.Runtime Assembly: Microsoft.SqlServer.ManagedDTS
  • Physical Location  :C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll
  • BIDS IDE to run and test SSIS dtsx packages

    4.Code Implementation

    Add reference of assembly Microsoft.SqlServer.ManagedDTS in your console application.One needs to install Integration services component from SQl Server Setup to get this dll. Using the below code one can change the variable properties of dtsx package that are added during development of the same.

    using Microsoft.SqlServer.Dts.Runtime;
    namespace UpdateDTSX_SSIS_Properties_Pkg
        class Program
            static void Main(string[] args)
                //Physical path of dtsx package
                string dtsxOld = @"D:\Test2005.dtsx";
                string dtsxUpgrade = @"D:\Test2008.dtsx";
                //Load package
                Application app = new Application();
                Package packageDTSX = app.LoadPackage(pkg, null);
                // Set properties value as required
                DTSProtectionLevel dtsProtectionLevel1 = packageDTSX.ProtectionLevel;
                Console.WriteLine("Old ProtectionLevel Encypted One = " + dtsProtectionLevel1);
                //Change protectionlevel from EncryptSensitiveWithUserKey To DontSaveSensitive
                packageDTSX.ProtectionLevel = DTSProtectionLevel.DontSaveSensitive;
                DTSProtectionLevel dtsProtectionLevel2 = packageDTSX.ProtectionLevel;
                Console.WriteLine("New ProtectionLevel DontSaveSensitive = " + dtsProtectionLevel2);

    This is all for now,using above approach one can change the required dtsx package properties and saved dtsx file in xml format.



    I tried to keep this article short and will act as a reference to start automation of dtsx conversion from one version to another. Please post your comment or queries if any.

  • License

    This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


    About the Author

    santosh poojari
    Technical Lead
    Australia Australia
    Whatsup-->Exploring--> MVC/HTML5/Javascript & Virtualization.......!

    You may also be interested in...


    Comments and Discussions

    Question[My vote of 1] Did you run and test this? Pin
    Member 256664131-Mar-11 3:55
    memberMember 256664131-Mar-11 3:55 
    Did you run and test this? Your soultion is not working.
    AnswerRe: [My vote of 1] Did you run and test this? Pin
    santosh poojari7-Apr-11 0:10
    membersantosh poojari7-Apr-11 0:10 

    General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

    Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

    Permalink | Advertise | Privacy | Terms of Use | Mobile
    Web01 | 2.8.180111.1 | Last Updated 9 Mar 2011
    Article Copyright 2011 by santosh poojari
    Everything else Copyright © CodeProject, 1999-2018
    Layout: fixed | fluid