Upgrade Integration Services Packages
When you upgrade an instance of SQL Server 2005 or SQL Server 2008 to the current release of SQL Server, your existing SQL Server 2008 Integration Services (SSIS) packages are not automatically upgraded to the package format that the current release SQL Server Integration Services uses. You will have to select an upgrade method and manually upgrade your 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 Migrate Scripts to VSTA.
For information on upgrading packages when you convert a project to the project deployment model, see Deploy Projects to Integration Services Server.
SQL Server 2000 Data Transformation Services Packages
Support for migrating or running Data Transformation Services (DTS) packages has been discontinued in in the current release of SQL Server Integration Services. The following DTS functionality has been discontinued.
DTS runtime
DTS API
Package Migration Wizard for migrating DTS packages to the next version of Integration Services
Support for DTS package maintenance in SQL Server Management Studio
Execute DTS 2000 Package task
Upgrade Advisor scan of DTS packages.
The following options are available for migrating DTS packages.
Migrate the packages to SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS), and then upgrade the packages to SQL Server 2012 Integration Services (SSIS).
For information about migrating DTS packages to SQL Server 2005 Integration Services (SSIS) and SQL Server 2008 Integration Services (SSIS), see Migrating Data Transformation Services Packages (2005) and Migrating Data Transformation Services Packages (2008).
Recreate the DTS packages by using SQL Server 2012 Integration Services (SSIS).
For information about the new features in SQL Server 2012 Integration Services (SSIS), see What's New (Integration Services). For an overview of the structure of Integration Services packages, see Integration Services (SSIS) Packages.
Selecting an Upgrade Method
You can use various methods to upgrade SQL Server 2005 and SQL Server 2008 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.
Note
When you run a SQL Server 2005 or SQL Server 2008 package using the dtexec utility (dtexec.exe) that is installed with the current release of SQL Server, the temporary package upgrade increases the execution time. The rate of increase in package execution time varies depending on the size of the package. To avoid an increase in the execution time, it is recommended that you upgrade the package before running it.
Upgrade Method |
Type of Upgrade |
|---|---|
Use the dtexec utility (dtexec.exe) that is installed with the current release of SQL Server to run a SQL Server 2005 or SQL Server 2008 package. For more information, see dtexec Utility. |
The package upgrade is temporary. For a SQL Server 2005 package, the script migration is temporary. The changes cannot be saved. |
Open a SQL Server 2005 or SQL Server 2008 package file in SQL Server Data Tools (SSDT). |
The package upgrade is permanent if you save the package; otherwise, it is temporary if you do not save the package. For a SQL Server 2005 package, the script migration is permanent if you save the package; otherwise, it is temporary if you do not save the package. |
Add a SQL Server 2005 or SQL Server 2008 package to an existing project in SQL Server Data Tools (SSDT). |
The package upgrade is permanent. For a SQL Server 2005 package, the script migration is permanent. |
Open a SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) project file in Visual Studio, and then use the SSIS Package Upgrade Wizard to upgrade multiple packages in the project. For more information, see Upgrade Integration Services Packages Using the SSIS Package Upgrade Wizard and SSIS Package Upgrade Wizard F1 Help. |
The package upgrade is permanent. For a SQL Server 2005 package, the script migration is permanent. |
Use the Upgrade method to upgrade one or more Integration Services packages. |
The package upgrade is permanent. For a SQL Server 2005 package, the script migration is permanent. |
Custom Applications and Custom Components
SQL Server 2005 Integration Services (SSIS) custom components will not work with the current release of SQL Server Integration Services.
You can use the current release of SQL Server Integration Services tools to run and manage packages that include SQL Server 2008 and SQL Server 2008 R2 SSIS custom components. We added four binding redirection rules to the following files to help redirect the runtime assemblies from version 10.0.0.0 (SQL Server 2008 R2) to version 11.0.0.0 (SQL Server 2012).
DTExec.exe.config
dtshost.exe.config
DTSWizard.exe.config
DTUtil.exe.config
DTExecUI.exe.config
To use SQL Server Data Tools to design packages that include SQL Server 2008 and SQL Server 2008 R2 custom components, you need to modify the devenv.exe.config file that is located at <drive>:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE.
To use these packages with customer applications that are built with the runtime for SQL Server 2012, include redirection rules in the configuration section of the *.exe.config file for the executable. The rules redirect the runtime assemblies to version 11.0.0.0 (SQL Server 2012). For more information about assembly version redirection, see <assemblyBinding> Element for <runtime>.
Locating the Assemblies
In SQL Server 2012, the Integration Services assemblies were upgraded to .NET 4.0. There is a separate global assembly cache for .NET 4, located in <drive>:\Windows\Microsoft.NET\assembly. You can find all of the Integration Services assemblies under this path, usually in the GAC_MSIL folder.
As in previous versions of SQL Server, the core Integration Services extensibility .dll files are also located at <drive>:\Program Files\Microsoft SQL Server\100\SDK\Assemblies.
Understanding SQL Server Package Upgrade Results
During the package upgrade process, most components and features in SQL Server 2005 and SQL Server 2008 packages convert seamlessly to their counterparts in the current release of SQL Server. However, there are several components 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 Use Upgrade Advisor to Prepare for Upgrades.
Component or Feature |
Upgrade Results |
|---|---|
Connection strings |
For SQL Server 2005 and SQL Server 2008 packages, the names of certain providers have changed and require different values in the connection strings. To update the connection strings, use one of the following procedures:
Note You cannot use the previous procedures to update a connection string when the connection string is stored in either a configuration file or a data source file, or when an expression sets the ConnectionString property. To update the connection string in these cases, you must manually update the file or the expression. For more information about data sources, see Data Sources. |
Lookup transformation |
For SQL Server 2005 packages, the upgrade process automatically upgrades the Lookup transformation to the current release of SQL Server Integration Services. However, the current release 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 |
For SQL Server 2005 packages, the 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 Migrate Scripts to VSTA. |
Scripts that Depend on ADODB.dll
Script Task and Script Component scripts that explicitly reference ADODB.dll may not upgrade or run on machines without SQL Server Management Studio or SQL Server Data Tools (SSDT) installed. In order to upgrade these Script Task or Script Component scripts, it is recommended that you remove the dependency on ADODB.dll. Ado.Net is the recommended alternative for managed code such as VB and C# scripts.
External Resources
Technical article, 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012, on msdn.microsoft.com.
Blog entry, Making your Existing Custom SSIS Extensions and Applications Work in Denali, on blogs.msdn.com.
Webcast, Upgrading SSIS Packages to SQL Server 2012, on channel9.msdn.com.