Infrastructure at your Service

Stéphane Haby

SQL Server 2012: new features for Integration Services (SSIS)

Like other SQL server 2012 products, Integration Services has a lot of new features and enhancements. I am presenting a summary of these new “goodies” (from CTP1 to RC0), which can be powerful for DBAs and developers.

Since CTP1

  • Deployment and Administration Enhancements

You have a new deployment model and ways to administer your data integration workloads in the company. The project deployment model provides the ability to define parameters for packages and projects and to modify the parameter values at runtime. More information here.

  • Object Impact and Data Lineage Analysis

You can now view information about impact and data lineage between supported Integration Services objects that are deployed to the server and supported SQL Server SQL Server objects. More information here.

  • Usability Enhancements

SQL Server Data Tools (SSDT) has been updated to help new and experienced users develop packages more easily. A new Integration Services toolbox, designer refinements, and other usability enhancements help new users become productive quickly.

  • Reduced Memory Usage by the Merge and Merge Join Transformations

Microsoft has made the Integration Services Merge and Merge Join transformations more robust and reliable. This is achieved by reducing the risk that these components will consume excessive memory when the multiple inputs produce data at uneven rates. More information here.

Since CTP2

  • New Reports for Troubleshooting Package Operations

In this release, two standard reports are available in SQL Server Management Studio to help you troubleshoot Integration Services packages that have been deployed to the Integration Services catalog. More information here.

  • Viewing Dependencies for Objects

You can now easily view dependencies for SQL Server objects and packages stored on different servers, using the Object Dependencies dialog box in SQL Server Management Studio.

  • Comparing and Merging Packages

The structure of .dtsx package files has been modified to make it easier for you to compare packages. You can also more reliably merge packages that don’t contain conflicting changes or changes stored in binary format.

  • Easier Access to Samples and Tutorials

The Getting Started window in the SSIS Designer provides links to samples, tutorials and videos. You can customize the window by adding links to additional content. For more information, see Integration Services User Interface. More information here.

The SSIS Toolbox in SQL Server Data Tools (SSDT) provides links to samples and Help content for Control Flow and Data Flow items. You can add links for custom components. More information here.

Since CTP3

This build provides additional elements like views, stored procedures and functions to help you troubleshoot performance and data issues.

Troubleshooting Capability Views, Procedures, and Functions
Get performance statistics and other information for an execution catalog.executions (SSISDB Database)
dm_execution_performance_counters (SSISDB Database)
Add, remove, and query data taps in a package data flow catalog.add_data_tap
Create a dump for a running package catalog.create_execution_dump
Set a parameter value in an instance of an execution catalog.set_execution_parameter_value (SSISDB Database)

Since RC0

  • Shared Connection Managers

This release allows you to create connection managers at the project level that can be shared by multiple packages in the project. The connection manager you create at the project level is automatically visible in the Connection Managers tab of the SSIS Designer window for all packages.

  • Parameters

Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package. More information here.

  • Undo/Redo in SSIS Designer

You can undo and redo up to 20 actions in the SSIS Designer. For a package, undo/redo is available in the Control Flow, Data Flow, Event Handlers, and Parameters tabs, and in the Variables window. More information here.


You can find all information about the new Integration Services here on the Microsoft TechNet. At the moment, I have not tested all new features but I will and you will see it soon.
My SQL Server 2012 series is to be continued…:-D



Leave a Reply

4 − = two

Stéphane Haby
Stéphane Haby

Delivery Manager