SSIS packages created in 2005 have to go through an upgrade process to run in 2008. 2008 packages created prior to the February CTP also have to be upgraded. The upgrade kicks in automatically once the package is loaded by the runtime - whether in the designer, dtexec, or through the API. To make the upgrade permanent, the package has to be saved (so although dtexec will upgrade the package when it's being executed, it will do so every time the package is run). Note that once the package is upgraded, it is no longer compatible with 2005.
The February CTP introduced a new Package Upgrade Wizard which allows you to do batch upgrades. It can be launched in a number of ways:
- In BIDS, by right clicking on the "SSIS Packages" folder of an SSIS project and selecting "Upgrade All Packages"
- Running "ssisupgrade.exe" from the command line
- In SSMS connected to the Integration Services service, by right clicking on a package folder and selecting "Upgrade Packages..."
The wizard will also launch automatically if you open an SSIS project in BIDS and one or more 2005 packages are detected in the project directory.
Running the wizard
The first thing to do is select the source location of the packages you want to upgrade. This will recursively pick up all packages from all sub directories. You can select a path from the file system, SSIS Service (Package Store), or SQL Server.
Things to note:
- When the wizard is launched from BIDS, the location is automatically set to the project directory
- The wizard can only connect to the 2008 SSIS Service (however, you can configure the 2008 service to manage packages stored on a 2005 server)
The next page of the wizard shows all packages contained under the source location you selected. Here you can select which packages you want to upgrade, and set passwords for packages with EncryptSensitiveWithPassword or EncryptAllWithPassword protection levels. Select multiple packages, type in the password in the lower box and click the "Apply to Selection" button to set a password on a bunch of packages in one shot.
Things to note:
- Both 2005 and 2008 packages will be listed here. Running the upgrade wizard on a 2008 package will succeed without changing anything.
- You can rename the package by changing the "Upgrade package name" value in the grid
- You can set the password directly in the grid
Once the packages have been chosen, the next page asks you to pick the output location. This can be:
- Same as the source location
- File System
- SSIS Service (Package Store)
- SQL Server
If you choose "Save to source location", you'll be given the option to backup the packages on the next page of the wizard.
Things to note:
- Launching the wizard from BIDS will select "Save to source location" by default
- The wizard does not support changing the package protection level - which means you'll get an error if you're trying to save packages with "ServerStorage" to the file system
- The package hierarchy / directory structure is maintained between the source and destination
The options that appear on the next wizard page will depend on what you choose as your destination.
The options:
Update connection strings to use new provider names
If this option is selected (true by default), connection managers which use MS providers that were renamed in 2008 will be automatically updated to use the new values. These include SQL Native Client (SQLNCLI -> SQLNCLI10), and MSOLAP.3 -> MSOLAP.4.
See my blog post about SQL Native Client 10.0 for more details.
Validate upgraded packages
This will force validation of the package after it has been upgraded. If validation fails, the upgraded package will not be saved.
Create new package ID
If this option is checked, the package ID GUID will be regenerated after the package is upgraded.
Continue upgrade process when a package upgrade fails
Packages will be upgraded one by one. If this option is set, the wizard will stop when it encounters an error.
Backup original packages
This option appears when you are using the same source and destination locations. It will create a backup folder in the source directory (SSISBackupFolder), and copy the original packages into it to prevent them from being overwritten.
Package name conflicts
This option tells the wizard what to do if a package with the same name already exists at the destination. The options are:
- Overwrite existing package files
- Add numeric suffixes to upgrade package names
- Do not upgrade packages
The final page of the wizard displays the upgrade status. Each package will have it's own line, and progress indicator. If a package has warnings (common if connection strings are being updated), or errors, you can click the "Messages..." link to see more details. Click the "Report" button to see messages for all of the packages.
Reasons why upgrade might failPackage has validation errors
If your package doesn't validate before the upgrade, your upgrade may fail if you have the "Validate upgraded packages" option set. See the details above.
Password protected fields
If the package has a protection level that encrypts with a user key (and the wizard is run as a different user), the upgrade will fail with the following error:
Error 0xc0016016: : Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
The best way to upgrade this package is to run the wizard with the account that created the package. If you don't mind losing the password information stored in the package, you can also upgrade it by opening it in the designer.
Packages with SQL Server configurations
The current CTP has a bug that causes the runtime to try to acquire connections for any SQL Server configurations in the package. If the connection fails, the package will fail to upgrade. This will hopefully be resolved for RTM.
Script upgrade failures
If your script task / component fails to upgrade for some reason (missing assemblies, early version of VSTA, compilation error), it will block the package upgrade. Manually upgrade the package by opening it in the designer, instead.
See my post about upgrading scripts from early Katmai builds .