Skip to content

upgrade-integration-services-packages.md

Latest commit

 

History

History
81 lines (56 loc) · 11.2 KB

upgrade-integration-services-packages.md

File metadata and controls

81 lines (56 loc) · 11.2 KB
title Upgrade Integration Services Packages
description Upgrade Integration Services Packages
author MikeRayMSFT
ms.author mikeray
ms.reviewer randolphwest
ms.date 05/07/2025
ms.service sql
ms.subservice integration-services
ms.topic upgrade-and-migration-article
helpviewer_keywords
Integration Services, migrating
migrating packages [Integration Services]
ms.custom sfi-ropc-nochange

Upgrade Integration Services Packages

[!INCLUDEsqlserver-ssis]

When you upgrade an instance of [!INCLUDEsql2008-md] to the current release of [!INCLUDEssNoVersion], your existing [!INCLUDEssISversion10] packages are not automatically upgraded to the package format that the current release [!INCLUDEssNoVersion] [!INCLUDEssISnoversion] uses. You will have to select an upgrade method and manually upgrade your packages.

Important

When you upgrade [!INCLUDE sssql11-md] to [!INCLUDE sssql14-md] or later versions, make sure that the original [!INCLUDE sssql11-md] instance is removed after the upgrade. If you are subscribed to Extended Security Updates for SQL Server, you are billed for both instances.

For information on upgrading packages when you convert a project to the project deployment model, see Deploy Integration Services (SSIS) Projects and Packages

Selecting an Upgrade Method

You can use various methods to upgrade [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14] 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 [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14] package using the dtexec utility (dtexec.exe) that is installed with the current release of [!INCLUDEssNoVersion], 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.

Note

For Script components referencing SSIS related assemblies which bind with version, upgrade process will not take care of but keep them unchanged. Manual update reference to the new version is needed.

Upgrade Method Type of Upgrade
Use the dtexec utility (dtexec.exe) that is installed with the current release of [!INCLUDEssNoVersion] to run a [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14] package.

For more information, see dtexec Utility.		 The package upgrade is temporary.

The changes cannot be saved.
Open a [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14] package file in [!INCLUDEssBIDevStudioFull]. The package upgrade is permanent if you save the package; otherwise, it is temporary if you do not save the package.
Add a [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14] package to an existing project in [!INCLUDEssBIDevStudioFull]. The package upgrade is permanent.
Open a [!INCLUDEssISversion10] or later project file in [!INCLUDEvsprvs], and then use the [!INCLUDEssIS] 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.
Use the xref:Microsoft.SqlServer.Dts.Runtime.Application.Upgrade%2A method to upgrade one or more [!INCLUDEssISnoversion] packages. The package upgrade is permanent.

Custom Applications and Custom Components

[!INCLUDEssISversion2005] custom components will not work with the current release of [!INCLUDEssNoVersion] [!INCLUDEssISnoversion].

You can use the current release of [!INCLUDEssNoVersion] [!INCLUDEssISnoversion] tools to run and manage packages that include [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14] [!INCLUDEssIS] custom components. We added four binding redirection rules to the following files to help redirect the runtime assemblies from version 10.0.0.0 ( [!INCLUDEsql2008r2]), version 11.0.0.0 ( [!INCLUDEssSQL11]), or version 12.0.0.0 ( [!INCLUDEssSQL14]) to version 15.0.0.0 ( [!INCLUDEssSQL19]).

  • DTExec.exe.config

  • dtshost.exe.config

  • DTSWizard.exe.config

  • DTUtil.exe.config

  • DTExecUI.exe.config

To use [!INCLUDEssBIDevStudio] to design packages that include [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14] 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 [!INCLUDEssSQL19], 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 ([!INCLUDEssSQL19]). For more information about assembly version redirection, see <assemblyBinding> Element for <runtime>.

Locating the Assemblies

In [!INCLUDEssSQL19], the [!INCLUDEssISnoversion] 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 [!INCLUDEssISnoversion] assemblies under this path, usually in the GAC_MSIL folder.

As in previous versions of [!INCLUDEssNoVersion], the core [!INCLUDEssISnoversion] 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 [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14] packages convert seamlessly to their counterparts in the current release of [!INCLUDEssNoVersion]. 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.

Component or Feature Upgrade Results
Connection strings For [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14] 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:

Use the [!INCLUDEssIS] Package Upgrade Wizard to upgrade the package, and select the Update connection strings to use new provider names option.

In [!INCLUDEssBIDevStudioFull], on the General page of the Options dialog box, select the Update connection strings to use new provider names option. For more information about this option, see General Page.

In [!INCLUDEssBIDevStudioFull], open the package and manually change the text of the ConnectionString property.

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.

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 [!INCLUDEssManStudioFull] or [!INCLUDEssBIDevStudioFull] 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.