Upgrading Integration Services Packages

SQL Server Setup

When you upgrade an instance of SQL Server 2005 to SQL Server 2008, your existing SQL Server 2005 Integration Services (SSIS) packages are not automatically upgraded to the package format that SQL Server 2008 Integration Services (SSIS) uses. You will have to select an upgrade method and manually upgrade your SQL Server 2005 packages.

When you upgrade a SQL Server 2005 package, Integration Services migrates the scripts in any Script task and Script component to Microsoft Visual Studio Tools for Applications (VSTA). In SQL Server 2005, the scripts in Script tasks or Script components used Microsoft Visual Studio for Applications (VSA). For more information about changes that you might have to make to the scripts before migration and about script conversion failure, see Migrating Scripts to VSTA.

Selecting an Upgrade Method

You can use various methods to upgrade SQL Server 2005 packages. For some of these methods, the upgrade is only temporary. For others, the upgrade is permanent. The following table describes each of these methods and whether the upgrade is temporary or permanent.

Upgrade Method Type of Upgrade

Use the dtexec utility (dtexec.exe) that is installed with SQL Server 2008 to run a SQL Server 2005 package.

For more information, see dtexec Utility.

The package upgrade and script migration are temporary.

The changes cannot be saved.

Add a SQL Server 2005 package to an existing project or open a SQL Server 2005 package in SQL Server 2008 Integration Services.

The package upgrade is permanent if you save the package; otherwise, it is temporary if you do not save the package.

The script migration is permanent if you add the package to an existing project. The script migration is also permanent if you open the package and save the conversion changes; otherwise, it is temporary if you do no save the conversion changes.

Use the SSIS Package Upgrade Wizard to create or open a SQL Server 2005 Integration Services project, and upgrade multiple packages in the project.

For more information, see How to: Upgrade Integration Services Packages Using the SSIS Package Upgrade Wizard and SSIS Package Upgrade Wizard F1 Help.

The package upgrade and script migration are permanent.

Understanding Package Upgrade Results

During the package upgrade process, most components and features in SQL Server 2005 packages convert seamlessly to their counterparts in SQL Server 2008. However, there are several component and features that either will not be upgraded or have upgrade results of which you should be aware. The following table identifies these components and features.

Note:
To identify which packages have the issues listed in this table, run Upgrade Advisor. For more information, see Using Upgrade Advisor to Prepare for Upgrades.

Component or Feature Upgrade Results

Connection strings

The names of certain providers have changed in SQL Server 2008 and require different values in the connection strings.

To update the connection strings, use one of the following procedures:

  • Use the SSIS Package Upgrade Wizard to upgrade the package, and select the Update connection strings to use new provider names option.
  • In Business Intelligence Development Studio, in the Options dialog box, selec the Update connectionstrings to use new provider names option.
  • In Business Intelligence Development Studio, open the package and manually change the text of the ConnectionString property.

Lookup transformation

The package upgrade process automatically upgrades the Lookup transformation to the SQL Server 2008 version. However, the SQL Server 2008 version of this component has some additional capabilities of which you might want to take advantage.

For more information, see Lookup Transformation.

Script task and Script component

The package upgrade process automatically migrates scripts in the Script task and Script component from VSA to VSTA.

For more information about changes that you might have to make to the scripts before migration and about script conversion failure, see Migrating Scripts to VSTA.

Third-party and custom components

These components will not be upgraded. You must manually recompile these components to enable them to work with SQL Server 2008 Integration Services.

See Also