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

3.Prerequisites

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.