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.

Pages: 1 2




Related Articles :

One Response to “Upgrade SSIS 2005 Packages to SSIS 2008”

  1. HI ,

    I followed the given steps

    script tasks in the packages get corrupted by following steps .

    Are there any steps to be configured for script task before using package upgrade wizard to prevent it form being corrupted .

    Thanks ,

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |