Upgrade SSIS 2005 Packages to SSIS 2008

Upgrading SSIS Packages Programmatically – The Application
class in SSIS 2008 provides an Upgrade method to programmatically upgrade SSIS
2005 packages to SSIS 2008.

Example: Create a Visual Studio 2008 console
application and take reference of Microsoft.SQLServer.ManagedDTS dll as
shown in the image below:

Add the following Using statements at the top of the file as shown below:

using
System;

using
System.Collections.Generic;

using
System.Linq;

using
System.Text;

using
System.IO;

using
System.Collections.ObjectModel;

using
Microsoft.SqlServer.Dts.Runtime;

Next
create a log status writer to log all the success, warning and failure messages
in their respective log text files and create an instance of the DirectoryInfo class
which represents the source of the SSIS packages.

/* create the log status writer and open it */

TextWriter logSuccess = new StreamWriter(@”D:SSISPackages2008Success.txt”);

TextWriter logWarning = new StreamWriter(@”D:SSISPackages2008Warning.txt”);

TextWriter logFailure = new StreamWriter(@”D:SSISPackages2008Failure.txt”);

DirectoryInfo sourceDirectory = new DirectoryInfo(@”D:SSISPackages2005″);

Create
an instance of the Microsoft.SqlServer.Dts.Runtime.Application class which
provides a package access mechanism and also provides an upgrade method to upgrade
packages. Furthermore, you will need to create an instance of the UpgradePackageInfo class
which represents a single package and a collection of the UpgradePackageInfo class,
which will be passed as the first parameter of the upgrade method (as shown below).
In this example I am also creating an instance of the SSISEventsClass which is the fifth and last
parameter for upgrade method.

Application SSISApplication = new Application();

UpgradePackageInfo SSISPackage;

Collection<UpgradePackageInfo>
SSISPackages;

SSISEventsClass eventsClass = new SSISEventsClass();

After  the declaration part  you need to create one instance for the all
SSIS packages in the specified folder and add it to the collection already created
as shown in the below code snippet:

SSISPackages
= new Collection<UpgradePackageInfo>();

FileInfo[]
allFiles = sourceDirectory.GetFiles();

for
(int intCount = 0; intCount <
allFiles.Length; intCount++)

{

   
if (allFiles[intCount].Extension == “.dtsx”)

   
{

       
SSISPackage = new UpgradePackageInfo();

       
SSISPackage.Name = allFiles[intCount].Name;

       
SSISPackages.Add(SSISPackage);

   
}

}

The
upgrade method expecta two instances of StorageInfo, one represents the source
location, the other represents the target location as shown below.

StorageInfo storeLocation = StorageInfo.NewFileStorage();

storeLocation.RootFolder
= @”D:SSISPackages2005″;

StorageInfo destinationLocation = StorageInfo.NewFileStorage();

destinationLocation.RootFolder
= @”D:SSISPackages2008″ ;

The
fourth parameter for the upgrade method is an instance of BatchUpgradeOptions,
with which you specify the different options to be used during the upgrade process
as shown below:

/* With BatchUpgradeOptions class you specify the different
options, few of

 * them are discussed below, whic will be used during upgrade
operation */

BatchUpgradeOptions options = new BatchUpgradeOptions();

/* With BackupOldPackages you specify if you want to take back
up the

 * original package before upgrade, if you set it to true, a
folder

 * named “SSISBackupFolder” will be created and backup
of the

 * original packages will be taken at this location */

options.BackupOldPackages
= true;

/* With Validate, you specify whether you want to validate the
package

 * during upgrade or not, please note if you set it to true then
only the

 * PreValidate or PostValidate events will be raised */

options.Validate
= true;

options.ValidationFailureAsError
= true;

/* With ActionOnExist you specify what to do if the file already
existing

 * at target location, either to replace, overwrite or raise an
error*/

options.ActionOnExist
= ActionOnExistType.Overwrite;

options.ContinueOnError
= true;

Now
finally I am call  the upgrade method which returns the upgrade operations
results as an instance of UpgradeResult class. Furthermore I will  enumerate through
each of the Succeeded, Warning and Failure collections and log the information in
the appropriate log writer.

UpgradeResult result = SSISApplication.Upgrade(SSISPackages, storeLocation,
destinationLocation, options, eventsClass);

IEnumerable<PackageUpgradeResult>
successResult = result.Succeeded;

if
(successResult.Count() > 0)

{

   
foreach (PackageUpgradeResult
succeededPackage in successResult)

   
{

       
logSuccess.WriteLine(“”);

       
logSuccess.WriteLine(succeededPackage.Package.Name + ”
package upgrade status : ” + succeededPackage.Status);

       
IEnumerable<StatusMessage>
succeededMessages = succeededPackage.Messages;

       
foreach (StatusMessage
succeededMessage in succeededMessages)

       
{

           
logSuccess.WriteLine(succeededMessage.Status + ”
: ” + succeededMessage.Message);

       
}

   
}

}

IEnumerable<PackageUpgradeResult>
warningResult = result.Warnings;

if
(warningResult.Count() > 0)

{

   
foreach (PackageUpgradeResult
warningPackage in warningResult)

   
{

       
logWarning.WriteLine(“”);

       
logWarning.WriteLine(warningPackage.Package.Name + ”
package upgrade status : ” + warningPackage.Status);

       
IEnumerable<StatusMessage>
warningMessages = warningPackage.Messages;

       
foreach (StatusMessage
warningMessage in warningMessages)

       
{

           
logWarning.WriteLine(warningMessage.Status + ”
: ” + warningMessage.Message);

       
}

   
}

}

IEnumerable<PackageUpgradeResult>
failureResult = result.Failures;

if
(failureResult.Count() > 0)

{

   
foreach (PackageUpgradeResult
failedPackage in failureResult)

   
{

       
logFailure.WriteLine(“”);

       
logFailure.WriteLine(failedPackage.Package.Name + ”
package upgrade status : ” + failedPackage.Status);

       
IEnumerable<StatusMessage>
failedMessages = failedPackage.Messages;

       
foreach (StatusMessage
failedMessage in failedMessages)

   
    {

           
logFailure.WriteLine(failedMessage.Status + ”
: ” + failedMessage.Message);

       
}

   
}

}

SSIS
packages generate different events during their  life-cycle. If you want to
override these default events and want your overridden events to be fired
during the upgrade operation, you can override these events as shown below. First,
override the OnPreValidate and OnPostValidate events (in the commented section
below you can provide your own custom code). These events will be fired only if you
have set Validate = true. Next, I override the OnProgress and
OnError events to add my custom code to these events for writing this
information on the console.

class
SSISEventsClass : DefaultEvents

{

   
bool failure = false;

   
public override
void OnPreValidate(Executable
exec, ref bool
fireAgain)

   
{             

       
if (exec.GetType().FullName == “Microsoft.SqlServer.Dts.Runtime.Package”)

       
{

           
//Console.WriteLine(“The PreValidate event of
the ” + ((Package)(exec)).Name.ToString() + ” package has been
raised.”);

       
}

       
base.OnPreValidate(exec, ref fireAgain);           

   
}

   
public override
void OnPostValidate(Executable
exec, ref bool
fireAgain)

   
{

       
if (exec.GetType().FullName == “Microsoft.SqlServer.Dts.Runtime.Package”)

       
{

           
//Console.WriteLine(“The PostValidate event of
the ” + ((Package)(exec)).Name.ToString() + ” package has been
raised.”);

       
}

       
base.OnPostValidate(exec, ref fireAgain);

   
}

   
public override
void OnProgress(TaskHost
taskHost, string progressDesc, int perComplete, int
progressCountLow, int progressCountHigh, string subComp, ref
bool fireAgain)

   
{

       
if ((!string.IsNullOrEmpty(subComp))
&& (subComp.EndsWith(“.dtsx”)))

       
{

           
switch (perComplete)

           
{

               
case 0:

                   
Console.Write(subComp + ” package upgradation status : “);

                   
Console.ForegroundColor = ConsoleColor.Yellow;

                   
Console.WriteLine(”
In Progress…”);

                   
Console.ResetColor();

                   
break;

               
case 100:

                   
if (failure != true)

                   
{

                       
Console.Write(subComp + ” package upgradation status : “);

                       
Console.ForegroundColor = ConsoleColor.Green;

                       
Console.WriteLine(”
Completed.”);

                       
Console.ResetColor();

                   
}

                   
else

                   
{

                       
Console.WriteLine(“”);

                       
failure = false;

                   
}

                   
break;

           
}

       
}

       
base.OnProgress(taskHost, progressDesc,
perComplete, progressCountLow, progressCountHigh, subComp, ref fireAgain);

   
}

   
public override
bool OnError(DtsObject
source, int errCode, string
subComp, string desc, string helpFile, int
helpContext, string idofInterfaceWithErr)

   
{

       
if (subComp.EndsWith(“.dtsx”))

       
{

           
Console.Write(subComp + ” package upgradation status : “);

           
Console.ForegroundColor = ConsoleColor.Red;

           
Console.WriteLine(”
Failed.”);

           
Console.ResetColor();

           
Console.Write(errCode + ” : ” + desc);

           
failure = true;

       
}

       
else

       
{

           
Console.Write(subComp + ” Task”);

           
Console.ForegroundColor = ConsoleColor.Red;

           
Console.WriteLine(”
Failed.”);

           
Console.ResetColor();

           
Console.Write(errCode + ” : ” + desc);

           
failure = true;

       
}

       
return base.OnError(source,
errCode, subComp, desc, helpFile, helpContext, idofInterfaceWithErr);

  
 }

}

Finally
when you run the above code, your results should appear similar to those shown
below:

Note:

·        
If
you have SSIS 2005 packages, you can use DTEXEC utility to temporarily
upgrade your packages to SSIS 2008 during runtime and execute it, but it will
not allow you to save the upgraded packages.

·        
You cannot change the language from Visual Basic 2005 
(which was only option in SSIS 2005) to C# during the upgrade but you can create
a new Script Task or Script Component in after upgrading to SSIS 2008.

·        
If
the Script Task or Script Component references IDTSxxx90 interfaces, then you
might need to change these references with the corresponding IDTSxxx100
interfaces before package upgrade.

]]>

Leave a comment

Your email address will not be published.