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.




Array

No comments yet... Be the first to leave a reply!

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 |