There are several enhancements in SSIS 2008 such as enhanced lookup transformation, the development environment for Script Task and Script Component changing from VSA to VSTA, etc. If you intend to upgrade your SSIS 2005 packages to SSIS 2008 it may be a challenging task especially if it uses the Script Task or Script Component because of the development environmental changes (from VSA to VSTA) . In this article I will examine the different options available to upgrade SSIS packages .
Upgrading with BIDS 2008 – Open SSIS 2005 packages in BIDS which comes with SQL Server 2008, and it will automatically upgrade your packages to SSIS 2008 and list down the details as below (pleaes note, unless you save the packages after upgradating your upgrade changes will not be persisted).
This is one of the simplest options, but it works on per package basis so what if we have large number of packages?
Upgrading with the SSIS Package Upgrade Wizard – SSIS 2008 includes the SSIS Package Upgrade Wizard which can use upgrade packages and also, if required, 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). Alternatively, 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 as below:
Next, you will be prompted for your SSIS package source. Choose any of the three options as listed in the Combo-Box.
The next screen will list the packages available at the specified source location. Here, check the Check-Box for the package to upgrade. The wizard allows you to change the upgraded package name and specify the password as required.
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 which case you be presented with an option in the next screen to take a back-up of the original packages. If you elect to take back-upa, a new folder named “SSISBackupFolder” will be created as sub-folder in the source location and original packages will be copied here.
The next screen allows you to specify the different upgrade options as shown below.
The next screen will be a review of your selections so far , “Finish” will commence the upgrade.
The next screen will display the upgrade status and result of each package. You can click on the Message on each row for details of each package’s upgrade.
