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

santosh poojari

1.00/5 (1 vote)

Mar 9, 2011

CPOL

2 min read

22420

This article will provide the first step towards migrating error free dtsx package to higher version of sql server ssis

1.Introduction
2.Real Time Scenarios
3.Prerequisites
4.Code Implementation
5.References
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.

			
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.