Helpful information and examples on how to use SQL Server Integration Services.
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:
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.
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:
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.
Once the packages have been chosen, the next page asks you to pick the output location. This can be:
If you choose "Save to source location", you'll be given the option to backup the packages on the next page of the wizard.
The options that appear on the next wizard page will depend on what you choose as your destination.
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:
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.
Package 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.
You can set a couple of package upgrade options in the designer through the SSIS options page (Tools
We tried to make our package upgrade as seamless as possible, but one problem we've seen people running