Table of Contents
- 1.Introduction
- 2.Real Time Scenarios
- 3.Prerequisites
- 4.Code Implementation
- 5.References
- 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.
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.dllBIDS IDE to run and test SSIS dtsx packages
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)
{
string dtsxOld = @"D:\Test2005.dtsx";
string dtsxUpgrade = @"D:\Test2008.dtsx";
Application app = new Application();
Package packageDTSX = app.LoadPackage(pkg, null);
DTSProtectionLevel dtsProtectionLevel1 = packageDTSX.ProtectionLevel;
Console.WriteLine("Old ProtectionLevel Encypted One = " + dtsProtectionLevel1);
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.
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
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.