Upgrading SSIS 2005 packages to SSIS 2008

There are couple of enhancements in SSIS 2008 from its predecessor SSIS 2005. For example, Lookup transformation has been enhanced, development environment for Script Task and Script Component has been changed from VSA to VSTA, name of certain connection providers have changed etc.

If you intend to upgrade your SSIS 2005 packages to SSIS 2008, it would not be so simple especially if it uses Script Task or Script Component because of the development environmental changes (from VSA to VSTA) and you need to do proper planning for this. In this article I will walk you through different options available to upgrade your SSIS packages with ease.

Upgrading with BIDS 2008 – You can simply open your SSIS 2005 packages in BIDS which comes with SQL Server 2008, while doing so the BIDS will upgrade your packages to SSIS 2008 and will list down the details as below, please note unless you save the packages after upgradation your upgrade changes will not be persistent.

This is one of the simplest options, but it works on per package basis so what if we have large number of packages. We have other options as discussed next.

Upgrading with SSIS Package Upgrade Wizard – SSIS 2008 includes SSIS Package Upgrade Wizard which you can use to upgrade packages and also, if required, take back up the original packages (though the back-up option works only for the packages stored on the file system). You can launch SSIS Package Upgrade Wizard from command prompt by typing SSISUpgrade.exe (default physical location C:Program FilesMicrosoft SQL Server100DTSBinn folder) or if you open a SSIS 2005 project in BIDS 2008, it will automatically launch the SSIS package upgrade wizard.

The first screen of this wizard will display a welcome screen something like this.

The next screen will ask you for SSIS package source. You can choose any of the three options as listed in the Combo-Box and depending on the selection; the form to take input will change.

The next screen will list down all the packages available at the specified source location. Here you can check the Check-Box for the package which you want to upgrade. The wizard allows you to change the upgraded package name and specify the password as per your need.

The next screen will ask you to specify the target location where upgraded packages will be stored. You can use the same the source location as the target; in that case you will get an option in the next screen to take back-up of the original packages. If you specify to take back-up, a new folder named “SSISBackupFolder” will be created as sub-folder in the source location and original packages will be copied here as back-up.

The next screen allows you to specify the different upgrade options as shown in the image below.

The next screen would be a review screen for all your selections so far in this wizard. The moment you click on the “Finish” button, the upgrade operation will start.

The next screen will display the upgrade status and result of each package. You can click on the Message against each row for a package to get details of each package upgrade.


Upgrading SSIS Packages Programmatically – Application class in SSIS 2008 provides 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:

Then use following using statement 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 log status writer to log all the success, warning and failure messages in the respective log text files and create an instance of 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 Microsoft.SqlServer.Dts.Runtime.Application class which provides package access mechanism and also provides upgrade method to upgrade packages. Further you need to create an instance of UpgradePackageInfo class which represents a single package and a collection of UpgradePackageInfo class, which will be passed as first parameter of upgrade method, as shown below, here I am also creating an instance of SSISEventsClass which is the fifth and last parameter for upgrade method, detail discussion of the definition of this class is available at the end of this section.

Application SSISApplication = new Application();

UpgradePackageInfo SSISPackage;

Collection<UpgradePackageInfo> SSISPackages;

SSISEventsClass eventsClass = new SSISEventsClass();

Once the declaration part is over, now you need to create one instance for the all SSIS packages in the specified folder and add it to the collection created above 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 expect two instances of StorageInfo, one represents the source location whereas the other one 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 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 calling the upgrade method which returns the upgrade operations results as an instance of UpgradeResult class. Further I am enumerating through each Succeeded, Warning and Failure collections and logging the information in the appropriate log writer created in the beginning.

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 its life-cycle. If you want to override these default events and want your overridden events to be fired during upgrade operation, you can override these events as shown below. First I am overriding OnPreValidate and OnPostValidate events, in the commented section you can provide your own custom code. These events will be fired only if you have set Validate = true in the option. Next I am overriding 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, you will see the result similar to the one as shown below; depending on the name and number of SSIS packages in your source location screen may vary in your case.

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.

·         Though it’s quite obvious, you cannot change the language from Visual Basic 2005  (which was only option in SSIS 2005) to C# during upgrade but you can create 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.


Conclusion

There are several of enhancements in SSIS 2008 from its predecessor SSIS 2005. This article talks about different ways to upgrade your SSIS 2005 packages to SSIS 2008 so that you can take advantage of these enhanced and new features.

]]>

Leave a comment

Your email address will not be published.