Upgrading SSIS 2005 packages to SSIS 2008
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.
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.
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.
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
– 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.
first screen of this wizard will display a welcome screen something like this.
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.
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.
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.
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.
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
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:
use following using statement at the top of the file as shown below:
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");
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();
SSISEventsClass eventsClass = new SSISEventsClass();
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.
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();
StorageInfo destinationLocation = StorageInfo.NewFileStorage();
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
* them are discussed below, whic will be used during upgrade
BatchUpgradeOptions options = new BatchUpgradeOptions();
/* With BackupOldPackages you specify if you want to take back
* original package before upgrade, if you set it to true, a
* named "SSISBackupFolder" will be created and backup
* original packages will be taken at this location */
/* With Validate, you specify whether you want to validate the
* during upgrade or not, please note if you set it to true then
* PreValidate or PostValidate events will be raised */
/* With ActionOnExist you specify what to do if the file already
* at target location, either to replace, overwrite or raise an
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,
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.
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.
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.