Considerations for Upgrading Integration Services
If SQL Server 2005 Integration Services is currently installed on your computer, you can upgrade to SQL Server 2008 Integration Services.
Note
If you want to upgrade from SQL Server 2000 Data Transformation Services (DTS) to SQL Server 2008 Integration Services, see Considerations for Upgrading Data Transformation Services.
Before Upgrading Integration Services
We recommended that you run Upgrade Advisor before you upgrade to SQL Server 2008. Upgrade Advisor reports issues that you might encounter if you migrate existing Integration Services packages to the new package format that SQL Server 2008 uses. For more information, see Using Upgrade Advisor to Prepare for Upgrades.
Upgrading Integration Services
You can upgrade by using one of the following methods:
Run SQL Server 2008 Setup and select the option to Upgrade from SQL Server 2000 or SQL Server 2005.
Run setup.exe at the command prompt and specify the /ACTION=upgrade option. For more information, see the section, "Installation Scripts for Integration Services," in How to: Install SQL Server 2008 R2 from the Command Prompt.
You cannot use upgrade to perform the following actions:
Reconfigure an existing installation of Integration Services.
Move from a 32-bit to a 64-bit version of SQL Server or from a 64-bit version to a 32-bit version.
Move from one localized version of SQL Server to another localized version.
When you upgrade, you can upgrade both Integration Services and the Database Engine, or just upgrade the Database Engine, or just upgrade Integration Services. If you upgrade only the Database Engine, SQL Server 2005 Integration Services remains functional, but you do not have the functionality of SQL Server 2008 Integration Services. If you upgrade only Integration Services, SQL Server 2008 Integration Services is fully functional, but can only store packages in the file system, unless an instance of the SQL Server 2008 SQL Server Database Engine is available on another computer.
Upgrading Both Integration Services and the Database Engine to SQL Server 2008
This section describes the effects of performing an upgrade that has the following criteria:
You upgrade both Integration Services and an instance of the Database Engine to SQL Server 2008.
Both Integration Services and the instance of the Database Engine are on the same computer.
What the Upgrade Process Does
The upgrade process does the following tasks:
Upgrades the Integration Services files, service, and tools (Management Studio and BI Development Studio). When there are multiple instances of SQL Server 2005 on the same computer, this upgrade occurs when the first instance of the Database Engine is upgraded.
Removes the SQL Server 2005 Integration Services files, service, and tools after the upgrade process finishes.
Upgrades the instance of the SQL Server 2005 Database Engine to the SQL Server 2008 version.
Moves data from the SQL Server 2005 Integration Services (SSIS) system tables to the SQL Server 2008 Integration Services 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.sysssispackagefolders 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 controll access to packages, creates three new fixed database-level roles: db_ssisadmin, db_ssisltduser, and db_ssisoperator. The SQL Server 2005 Integration Services roles of db_dtsadmin, db_dtsltduser, and db_dtsoperator are not removed, but are made members of the corresponding new roles.
If the SSIS package store (that is, the file system location managed by the Integration Services service) is the default location under \SQL Server\90, moves those packages to the new default location under \SQL Server\100.
Updates the Integration Services service configuration file to point to the upgraded instance of the Database Engine.
What the Upgrade Process Does Not Do
The upgrade process does not do the following tasks:
Does not migrate existing Integration Services packages to the new package format that SQL Server 2008 uses. For information about how to migrate packages, see Upgrading 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 SQL Server 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 SQL Server 2008 location for the dtexec utility.
What You Can Do After Upgrading
After the upgrade process finishes, you can do the following tasks:
Run SQL Server Agent jobs that run packages.
Use Management Studio to manage Integration Services packages that are stored in an instance of SQL Server 2005. However, you might have to modify the service configuration file to add the instance of SQL Server 2005 to the list of locations managed by the 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 2 in the packageformat column indicates a SQL Server 2005 Integration Services package; a value of 3 indicates a SQL Server 2008 Integration Services package. Until you migrate packages to the new package format, the SQL Server 2005 Integration Services packages have a value of 2 in the packageformat column.
You cannot use the SQL Server 2005 tools to design, run, or manage Integration Services packages. The SQL Server 2005 tools include the SQL Server 2005 versions of Business Intelligence Development Studio, the SQL Server Import and Export Wizard, and the Package Execution Utility (dtexecui.exe). The upgrade process does not remove the SQL Server 2005 tools. However, you will not able to use these tools to continue to work with SQL Server 2005 Integration Services packages on a server that has been upgraded.
By default, in an upgrade installation, Integration Services 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 SQL Server 2008. 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\100\SSIS node, and change the DWORD value of the LogPackageExecutionToEventLog setting from 1 to 0.
Upgrading only the Database Engine to SQL Server 2008
This section describes the effects of performing an upgrade that has the following criteria:
You upgrade only an instance of the Database Engine. That is, the instance of the Database Engine is now an instance of SQL Server 2008, but the instance of Integration Services and the client tools are from SQL Server 2005.
The instance of the Database Engine is on one computer, and SQL Server 2005 Integration Services 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 Database Engine are not the same as those used in SQL Server 2005. Therefore, the SQL Server 2005 versions of Management Studio and BI Development Studio cannot discover the packages in the system tables on the upgraded instance of the Database Engine. Because these packages cannot be discovered, there are limitations on what you can do with those packages:
You cannot use the SQL Server 2005 tools, Management Studio and BI Development Studio, on other computers to load or manage packages from the upgraded instance of the Database Engine.
Note
Although the packages in the upgraded instance of the Database Engine have not yet been migrated to the new package format, they are not discoverable by the SQL Server 2005 tools. Therefore, the packages cannot be used by the SQL Server 2005 tools.
You cannot use SQL Server 2005 Integration Services (SSIS) on other computers to run packages that are stored in msdb on the upgraded instance of the Database Engine.
You cannot use SQL Server Agent jobs on SQL Server 2005 computers to run SQL Server 2005 Integration Services packages that are stored in the upgraded instance of the Database Engine.