Skip to content

Latest commit

 

History

History
143 lines (90 loc) · 17.2 KB

upgrade-integration-services.md

File metadata and controls

143 lines (90 loc) · 17.2 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Upgrade Integration Services
Upgrade Integration Services
MikeRayMSFT
mikeray
03/14/2017
sql
integration-services
upgrade-and-migration-article
Integration Services, upgrading
SSIS, upgrading
SQL Server Integration Services, upgrading
upgrading Integration Services

Upgrade Integration Services

[!INCLUDEsqlserver-ssis]

If [!INCLUDEssISversion10] or later is currently installed on your computer, you can upgrade to [!INCLUDEssISCurrent].

When you upgrade to [!INCLUDEssISCurrent] on a machine that has one of these earlier versions of [!INCLUDEssISnoversion] installed, [!INCLUDEssISCurrent] is installed side-by-side with the earlier version.

With this side-by-side install, multiple versions of dtexec utility are installed. To ensure that you run the correct version of the utility, at the command prompt run the utility by entering the full path (<drive>:\Program Files\Microsoft SQL Server\<version>\DTS\Binn). For more information about dtexec, see dtexec Utility.

Note

In previous versions of [!INCLUDEssNoVersion], by default when you installed [!INCLUDEssNoVersion] all members of the Users group in Local Users and Groups had access to the [!INCLUDEssISnoversion] service. When you install [!INCLUDEssSQL16] and later, users do not have access to the [!INCLUDEssISnoversion] service. The service is secure by default. After [!INCLUDEssNoVersion] is installed, the [!INCLUDEssNoVersion] administrator must run the DCOM Configuration tool (Dcomcnfg.exe) to grant specific users access to the [!INCLUDEssISnoversion] service. For more information, see Integration Services Service (SSIS Service).

Before Upgrading Integration Services

We recommended that you run Upgrade Advisor before you upgrade to [!INCLUDEssSQL19]. Upgrade Advisor reports issues that you might encounter if you migrate existing [!INCLUDEssISnoversion] packages to the new package format that [!INCLUDEssSQL19] uses.

Note

Support for migrating or running Data Transformation Services (DTS) packages has been discontinued in SQL Server 2012. The following DTS functionality has been discontinued.

  • DTS runtime
  • DTS API
  • Package Migration Wizard for migrating DTS packages to the next version of [!INCLUDEssISnoversion]
  • Support for DTS package maintenance in [!INCLUDEssManStudioFull]
  • Execute DTS 2000 Package task
  • Upgrade Advisor scan of DTS packages.

For information about other discontinued features, see Discontinued Integration Services Functionality in SQL Server 2016.

Upgrading Integration Services

You can upgrade by using one of the following methods:

You cannot use upgrade to perform the following actions:

  • Reconfigure an existing installation of [!INCLUDEssISnoversion].

  • Move from a 32-bit to a 64-bit version of [!INCLUDEssNoVersion] or from a 64-bit version to a 32-bit version.

  • Move from one localized version of [!INCLUDEssNoVersion] to another localized version.

When you upgrade, you can upgrade both [!INCLUDEssISnoversion] and the [!INCLUDEssDE], or just upgrade the [!INCLUDEssDE], or just upgrade [!INCLUDEssISnoversion]. If you upgrade only the [!INCLUDEssDE], [!INCLUDEssISversion10] or later remains functional, but you do not have the functionality of [!INCLUDEssISCurrent]. If you upgrade only [!INCLUDEssISnoversion], [!INCLUDEssISCurrent] is fully functional, but can only store packages in the file system, unless an instance of the [!INCLUDE ssdenoversion-md] is available on another computer.

Upgrading Both Integration Services and the Database Engine to [!INCLUDEssSQL19]

This section describes the effects of performing an upgrade that has the following criteria:

What the Upgrade Process Does

The upgrade process does the following tasks:

  • Installs the [!INCLUDEssISCurrent] files, service, and tools ([!INCLUDEssManStudio] and [!INCLUDEssBIDevStudio]). When there are multiple instances of [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14] on the same computer, the first time you upgrade any of the instances to [!INCLUDEssSQL19], the [!INCLUDEssISCurrent] files, service, and tools are installed.

  • Upgrades the instance of the [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14] [!INCLUDEssDE] to the [!INCLUDEssSQL19] version.

  • Moves data from the [!INCLUDEssISversion10] or later system tables to the [!INCLUDEssISCurrent] system tables, as follows:

    • Moves packages without change from the msdb.dbo.sysdtspackages90 system table to the msdb.dbo.sysssispackages system table.

      [!NOTE]
      Although the data moves to a different system table, the upgrade process does not migrate packages to the new format.

    • Moves folder metadata from the msdb.sysdtsfolders90 system table to the msdb.sysssisfolders system table.

    • Moves log data from the msdb.sysdtslog90 system table to the msdb.sysssislog system table.

  • Removes the msdb.sysdts*90 system tables and the stored procedures that are used to access them after moving the data to the new msdb.sysssis* tables. However, upgrade replaces the sysdtslog90 table with a view that is also named sysdtslog90. This new sysdtslog90 view exposes the new msdb.sysssislog system table. This ensures that reports based on the log table continue to run without interruption.

  • To control access to packages, creates three new fixed database-level roles: db_ssisadmin, db_ssisltduser, and db_ssisoperator. The [!INCLUDEssVersion2005] [!INCLUDEssISnoversion] roles of db_dtsadmin, db_dtsltduser, and db_dtsoperator are not removed, but are made members of the corresponding new roles.

  • If the [!INCLUDEssIS] package store (that is, the file system location managed by the [!INCLUDEssISnoversion] service) is the default location under \SQL Server\90, \SQL Server\100, \SQL Server\110, or \SQL Server\120 moves those packages to the new default location under \SQL Server\130.

  • Updates the [!INCLUDEssISnoversion] service configuration file to point to the upgraded instance of the [!INCLUDEssDE].

What the Upgrade Process Does Not Do

The upgrade process does not do the following tasks:

  • Does not remove the [!INCLUDEssISversion10] or later service.

  • Does not migrate existing [!INCLUDEssISnoversion] packages to the new package format that [!INCLUDEssSQL19] uses. For information about how to migrate packages, see Upgrade Integration Services Packages.

  • Does not move packages from file system locations, other than the default location, that have been added to the service configuration file. If you have previously edited the service configuration file to add more file system folders, packages that are stored in those folders will not be moved to a new location.

  • In [!INCLUDEssNoVersion] Agent job steps that call the dtexec utility (dtexec.exe) directly, does not update the file system path for the dtexec utility. You have to edit these job steps manually to update the file system path to specify the [!INCLUDEssSQL19] location for the dtexec utility.

What You Can Do After Upgrading

After the upgrade process finishes, you can do the following tasks:

  • Run [!INCLUDEssNoVersion] Agent jobs that run packages.

  • Use [!INCLUDEssManStudio] to manage [!INCLUDEssISnoversion] packages that are stored in an instance of [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14]. You need to modify the service configuration file to add the instance of [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14] to the list of locations managed by the service.

    [!NOTE]
    Early versions of [!INCLUDEssManStudio] cannot connect to [!INCLUDEssISCurrent] Service.

  • Identify the version of packages in the msdb.dbo.sysssispackages system table by checking the value in the packageformat column. The table has a packageformat column that identifies the version of each package. A value of 3 indicates a [!INCLUDEssISversion10] package. Until you migrate packages to the new package format, the value in the packageformat column does not change.

  • You cannot use the [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14] tools to design, run, or manage [!INCLUDEssISnoversion] packages. The [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14] tools include the respective versions of [!INCLUDEssBIDevStudioFull], the [!INCLUDEssNoVersion] Import and Export Wizard, and the Package Execution Utility (dtexecui.exe). The upgrade process does not remove the [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14]tools. However, you will not able to use these tools to continue to work with [!INCLUDEssISversion10] or later packages on a server that has been upgraded.

  • By default, in an upgrade installation, [!INCLUDEssISnoversion] is configured to log events that are related to the running of packages to the Application event log. This setting might generate too many event log entries when you use the Data Collector feature of [!INCLUDEssSQL19]. The events that are logged include EventID 12288, "Package started," and EventID 12289, "Package finished successfully." To stop logging these two events to the Application event log, open the registry for editing. Then in the registry, locate the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\SSIS node, and change the DWORD value of the LogPackageExecutionToEventLog setting from 1 to 0.

Upgrading only the Database Engine to [!INCLUDEssSQL19]

This section describes the effects of performing an upgrade that has the following criteria:

  • You upgrade only an instance of the [!INCLUDEssDE]. That is, the instance of the [!INCLUDEssDE] is now an instance of [!INCLUDEssSQL19], but the instance of [!INCLUDEssISnoversion] and the client tools are from [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], [!INCLUDEssSQL11], or [!INCLUDEssSQL14].

  • The instance of the [!INCLUDEssDE] is on one computer, and [!INCLUDEssISnoversion] and the client tools are on another computer.

What You Can Do After Upgrading

The system tables that store packages in the upgraded instance of the [!INCLUDEssDE] are not the same as those used in [!INCLUDEsql2008-md]. Therefore, the [!INCLUDEsql2008-md] versions of [!INCLUDEssManStudio] and [!INCLUDEssBIDevStudio] cannot discover the packages in the system tables on the upgraded instance of the [!INCLUDEssDE]. Because these packages cannot be discovered, there are limitations on what you can do with those packages:

  • You cannot use the [!INCLUDEsql2008-md] tools, [!INCLUDEssManStudio] and [!INCLUDEssBIDevStudio], on other computers to load or manage packages from the upgraded instance of the [!INCLUDEssDE].

    [!NOTE]
    Although the packages in the upgraded instance of the [!INCLUDEssDE] have not yet been migrated to the new package format, they are not discoverable by the [!INCLUDEsql2008-md] tools. Therefore, the packages cannot be used by the [!INCLUDEsql2008-md] tools.

  • You cannot use [!INCLUDEssISversion10] on other computers to run packages that are stored in msdb on the upgraded instance of the [!INCLUDEssDE].

  • You cannot use [!INCLUDEssNoVersion] Agent jobs on [!INCLUDEsql2008-md] computers to run [!INCLUDEssISversion10] packages that are stored in the upgraded instance of the [!INCLUDEssDE].

External Resources

Blog entry, Making your Existing Custom SSIS Extensions and Applications Work in Denali, on blogs.msdn.com.