How to Upgrade Integration Services Packages

1. Upgrade Integration Services Packages

When you upgrade an instance of SQL Server 2008 (10.0.x) 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.

2.Selecting an Upgrade Method

You can use various methods to upgrade SQL Server 2008 (10.0.x), SQL Server 2008 R2 (10.50.x), SQL Server 2012 (11.x), or SQL Server 2014 (12.x) 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.

 3.Custom Applications and Custom Components

You can use the current release of SQL Server Integration Services tools to run and manage packages that include SQL Server 2008 (10.0.x), SQL Server 2008 R2 (10.50.x), SQL Server 2012 (11.x), or SQL Server 2014 (12.x) 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 (10.50.x)), version 11.0.0.0 ( SQL Server 2012 (11.x)), or version 12.0.0.0 ( SQL Server 2014 (12.x)) to version 15.0.0.0 ( SQL Server 2019 (15.x)).

• 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 (10.0.x), SQL Server 2008 R2 (10.50.x), SQL Server 2012 (11.x), or SQL Server 2014 (12.x) 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 2019 (15.x), include redirection rules in the configuration section of the *.exe.config file for the executable. The rules redirect the runtime assemblies to version 15.0.0.0 (SQL Server 2019 (15.x)). For more information about assembly version redirection, see <assemblyBinding> Element for <runtime>.

Locating the Assemblies

In SQL Server 2019 (15.x), 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\130\SDK\Assemblies.

Understanding SQL Server Package Upgrade Results

During the package upgrade process, most components and features in SQL Server 2008 (10.0.x), SQL Server 2008 R2 (10.50.x), SQL Server 2012 (11.x), or SQL Server 2014 (12.x) 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.

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.