Interoperability and Coexistence (Integration Services)

 

SQL Server 2014 Integration Services (SSIS) can co-exist side-by-side with SQL Server 2008 Integration Services and SQL Server 2012 Integration Services.

The following table lists some of the differences between the current and earlier versions of Integration Services.

FeatureSQL Server 2014 Integration Services (SSIS)SQL Server 2012 Integration Services (SSIS)SQL Server 2008 Integration Services (SSIS)
Development environmentSQL Server 2014 Data Tools – Business Intelligence for Visual Studio 2012 CTP 2

 SQL Server 2014 Data Tools - Business Intelligence for Visual Studio 2013
SQL Server Data Tools for Visual Studio 2010

 SQL Server Data Tools – Business Intelligence for Visual Studio 2012
Business Intelligence Development Studio (Microsoft Visual Studio 2008)
Management environmentSQL Server Management StudioSQL Server Management StudioSQL Server Management Studio
Main system table in msdb for storing packagessysssispackagessysssispackagessysssispackages
Main command prompt utility for running packagesdtexec (dtexec.exe), 2014 versiondtexec (dtexec.exe), 2012 versiondtexec (dtexec.exe), 2008 version
Default root file system folderC:\Program Files\Microsoft SQL Server\120\DTSC:\Program Files\Microsoft SQL Server\110\DTSC:\Program Files\Microsoft SQL Server\100\DTS
Default root Registry keyHKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\120\SSISHKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\SSISHKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS

When you have SQL Server 2014 Integration Services installed side-by-side with SQL Server 2008 Integration Services and SQL Server 2012 Integration Services., you can perform the following tasks:

  • Design packages in SQL Server Data Tools. You use the following tools to develop and maintain packages based on corresponding versions of SQL Server.

    • Use the SQL Server 2008 version of Business Intelligence Development Studio to develop and maintain packages that are based on SQL Server 2008 Integration Services (SSIS)

    • Use the SQL Server 2012 version of SQL Server Data Tools (SSDT) to develop and maintain packages that are based on SQL Server 2014 Integration Services (SSIS).

    • Use the SQL Server 2014 version of SQL Server Data Tools (SSDT) to develop and maintain packages that are based on SQL Server 2014 Integration Services (SSIS).

  • Load and run packages. You can load and run packages that were developed in SQL Server 2008 and SQL Server 2012, in the SQL Server 2014 version of SQL Server Data Tools (SSDT). When you add the package to an existing project, the package is permanently upgraded to the format that SQL Server 2014 Integration Services uses. When you open the package file in SQL Server Data Tools (SSDT), the package is temporarily upgraded to the format that SQL Server 2014 Integration Services uses. If you save the change to the package, the package is permanently upgraded. Once saved in the format that SQL Server 2014 Integration Services uses, packages can no longer be opened in the corresponding SQL Server 2008 or SQL Server 2012 version of Business Intelligence Development Studio, nor run by the corresponding SQL Server 2008 Integration Services or SQL Server 2012 Integration Services tools.

  • Manage packages in SQL Server Management Studio. You cannot connect to an instance of the SQL Server 2014 version of the Integration Services service, from the SQL Server 2008 or the SQL Server 2012 version of Management Studio. You cannot connect to an instance of the SQL Server 2008 or the SQL Server 2012 version of the Integration Services service from the SQL Server 2014 version of Management Studio. You can use the SQL Server 2014 version of Management Studio to manage Integration Services packages that are stored in an instance of SQL Server 2012 or SQL Server 2014. You need to modify the service configuration file to add the instance of SQL Server 2012 to the list of locations managed by the service. For more information, see Configuring the Integration Services Service (SSIS Service).

  • Store packages in SQL Server. You can store packages in the following databases.

    Package formatDatabase
    SQL Server 2008 Integration Servicesmsdb database of an instance of SQL Server 2008
    SQL Server 2012 Integration Servicesmsdb database of an instance of SQL Server 2012
    SQL Server 2014 Integration Servicesmsdb database of an instance of SQL Server 2014

    On an instance of SQL Server 2014, you can import packages from an instance of SQL Server 2008 or from an instance of SQL Server 2012, but you cannot export packages to an instance of SQL Server 2008 or to an instance of SQL Server 2012.

    On an instance of SQL Server 2008 or an instance of SQL Server 2012, you cannot import packages from, nor export packages to, an instance of SQL Server 2014.

  • Run packages. You can run SQL Server 2008 Integration Services and SQL Server 2012 Integration Services packages by using the SQL Server 2014 version of the dtexec utility or of SQL Server Agent. Whenever a SQL Server 2014 Integration Services tool loads a package that was developed in SQL Server 2008 or SQL Server 2012, the tool temporarily converts, in memory, the package to the package format that SQL Server 2014 Integration Services (SSIS) uses. If the SQL Server 2008 or SQL Server 2012 package has issues that prevent a successful conversion, the SQL Server 2014 Integration Services tool cannot run the package until those issues are resolved. For more information, see Upgrade Integration Services Packages.

Show: