Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

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

1.00/5 (1 vote)
9 Mar 2011CPOL2 min read 22.4K  
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

1.Introduction

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.

    3.Prerequisites

  • 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.

    C#
    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;
                
                app.SaveToXml(dtsxUpgrade,packageDTSX,null);
                
                DTSProtectionLevel dtsProtectionLevel2 = packageDTSX.ProtectionLevel;
    
                Console.WriteLine("New ProtectionLevel DontSaveSensitive = " + dtsProtectionLevel2);
                Console.ReadLine();
            }
        }
    }

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

    5.References

    http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.upgrade.aspx

    http://msdn.microsoft.com/en-us/library/ms188550.aspx

    http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtsprotectionlevel.aspx

    6.Conclusion

    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)