Upgrade
Integration Services
SQL Server 2014
If SQL Server 2005
Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) is
currently installed on your computer, you can upgrade to SQL Server 2014
Integration Services (SSIS).
When you upgrade to
SQL Server 2014 Integration Services (SSIS) on a machine that has one of these
earlier versions of Integration Services installed, SQL Server 2014 Integration
Services (SSIS) 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.
We recommended that
you run Upgrade Advisor before you upgrade to SQL Server 2014. Upgrade
Advisor reports issues that you might encounter if you migrate existing
Integration Services packages to the new package format that SQL Server 2014
uses. For more information, see Use Upgrade Advisor to Prepare for Upgrades.
You can upgrade by
using one of the following methods:
- Run SQL Server 2014 Setup and select the option to Upgrade
from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2, or SQL
Server 2012.
- 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 Install SQL Server 2014 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 (SSIS)
or SQL Server 2008 Integration Services (SSIS) remains functional, but you do
not have the functionality of SQL Server 2014 Integration Services (SSIS). If
you upgrade only Integration Services, SQL Server 2014 Integration Services
(SSIS) is fully functional, but can only store packages in the file system,
unless an instance of the SQL Server 2014 Database Engine is available on
another computer.
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 2014.
- 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:
- Installs the SQL Server 2014 Integration Services
(SSIS) files, service, and tools (Management Studio and SQL Server
Data Tools). When there are multiple instances of SQL Server 2005 or SQL
Server 2008 on the same computer, the first time you upgrade any of the
instances to SQL Server 2014, the SQL Server 2014 Integration Services
(SSIS) files, service, and tools are installed.
- Upgrades the instance of the SQL Server 2005 or the SQL
Server 2008 Database Engine to the SQL Server 2014 version.
- Moves data from the SQL Server 2005 Integration
Services (SSIS) or SQL Server 2008 Integration Services (SSIS) system
tables to the SQL Server 2014 Integration Services (SSIS) system
tables, as follows:
- Moves packages without change from the
msdb.dbo.sysdtspackages90 system table to the msdb.dbo.sysssispackages
system table.
- 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 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, \SQL Server\100, or \SQL
Server\110 moves those packages to the new default location under \SQL
Server\120.
- 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 remove
the SQL Server 2005 Integration Services (SSIS) or SQL Server 2008
Integration Services (SSIS) service.
- Does not migrate existing Integration Services packages
to the new package format that SQL Server 2014 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 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 2014 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 2008
or SQL Server 2014. You need to modify the service configuration
file to add the instance of SQL Server 2008 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 (SSIS) package; a value of 3
indicates a SQL Server 2008 Integration Services (SSIS) package. Until you
migrate packages to the new package format, the value in the packageformat
column does not change.
- You cannot use the SQL Server 2005 or SQL Server 2008
tools to design, run, or manage Integration Services packages. The
SQL Server 2005 and SQL Server 2008 tools include the respective versions
of SQL Server Data Tools (SSDT), the SQL Server Import and Export Wizard,
and the Package Execution Utility (dtexecui.exe). The upgrade process does
not remove the SQL Server 2005 or SQL Server 2008 tools. However, you will
not able to use these tools to continue to work with SQL Server 2005
Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS)
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 2014. 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\120\SSIS node,
and change the DWORD value of the LogPackageExecutionToEventLog setting from
1 to 0.
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 2014, but the instance of Integration Services and the client tools
are from SQL Server 2005 or SQL Server 2008.
- The instance of the Database Engine is on one computer,
and 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 or SQL Server 2008. Therefore, the SQL Server
2005 or SQL Server 2008 versions of Management Studio and SQL Server Data
Tools 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 or SQL Server 2008
tools, Management Studio and SQL Server Data Tools, on other computers to
load or manage packages from the upgraded instance of the Database
Engine.
- You cannot use SQL Server 2005 Integration Services
(SSIS) or SQL Server 2008 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
or SQL Server 2008 computers to run SQL Server 2005 Integration Services
(SSIS) or SQL Server 2008 Integration Services (SSIS) packages that are
stored in the upgraded instance of the Database Engine.
Reference : http://msdn.microsoft.com/en-us/library/cc879336.aspx
No comments:
Post a Comment