Integration Services Enhancements on SQL 2012

  • Article

 What is Integration Services?

SQL Server Integration Services (SSIS) is a tool to allow for gathering of data from diverse sources, scrubbing and homogenizing the data into a format for unified use, and loading that data into databases, spreadsheets, text files, or any other OLE-DB compliant destination.

History

In SQL Server 7.0 and 2000, ETL capabilities have been provided by Data Transformation Services (DTS). With the advent of SQL Server 2005, Microsoft decided that a wide range of ETL related innovations implemented in the new version justified its re-branding, resulting in the introduction of SQL Server Integration Services (SSIS).

SQL 2012 SSIS Enhancements

General Interface Changes

SSIS Toolbox: The SSIS Toolbox add tasks and data flow components to a package, rather than the Visual Studio toolbox that you use in earlier versions of Integration Services.

Parameters: The package designer includes a new tab to open the Parameters window for a package. Parameters allow you to specify run-time values for package, container, and task properties or for variables.

Variables button: This new button on the package designer toolbar provides quick access to the Variables window.

SSIS Toolbox button: This button allows you to open the SSIS Toolbox when it is not visible.

Getting Started: Provides access to links to videos and samples that you can use to learn how to work with Integration Services.

Zoom control: You can zoom in or out to a maximum size of 500 percent of the normal view or to a minimum size of 10 percent, respectively.

Shared Connection Managers: The Connections Manager folder is the new container for connection managers that you want to share among multiple packages. To create a shared connection manager, follow these steps:

  1.  Right-click the Connections Manager folder and select New Connection Manager.
  2.  In the Add SSIS Connection Manager dialog box, select the desired connection manager type, and then click the Add button.
  3.  Supply the required information in the editor for the selected connection manager type, and then click OK until all dialog boxes are closed

Undo and Redo: Is the newly added ability to use Undo and Redo while developing packages in the SQL Server Data Tools.You can now make edits in either the control flow or data flow designer surface, and use Undo to reverse a change or Redo to restore a change that you had just reversed. This capability also works in the Variables window, and on the Event Handlers and Parameters tabs. You can also use Undo and Redo when working with project parameters. You can also use Ctrl+Z for Undo or Ctrl+Y for Redo

Package Sort By Name: Sort the list of packages without closing the project by right-clicking the SSIS Packages folder, and selecting Sort By Name.

Status Indicators: The user interface now displays icons in the upper-right corner of each item to indicate success or failure

Control Flow

Apart from the general enhancements to the package designer interface, there are two notable updates for the control flow.

Expression Task: The purpose of this task is to make it easier to assign a dynamic value to a variable.

Execute Package task: Include a new property, ReferenceType, which you use to specify the location of the package to execute. If you select Project Reference, you then choose the child package from the drop-down list. If you select External Reference, you configure the path to the child package just as you do in earlier versions of Integration Services.

Dataflow

The data flow also has some significant updates. Some user interface changes have also been made to simplify the process and help you get your job done faster when designing the data flow.

Source and Destination Assistants: Add the Source Assistant to the data flow design surface by using drag-and-drop or by double-clicking the item in the SSIS Toolbox, which opens the Add New Source dialog box

Merge and Merge Join Transformations: Allow you to collect data from two inputs and produce a single output of combined results. Integration Services introduce a mechanism to better manage memory pressure. This memory management mechanism operates automatically with no additional configuration of the transformation necessary.

DQS Cleansing transformation: Is a new data flow component that you use in conjunction with Data Quality Services (DQS). Its purpose is to help you improve the quality of data by using rules that are established for the applicable knowledge domain. You can create rules to test data for common misspellings in a text field or to ensure that the column length conforms to a standard specification.

Collapsible Grouping: Consolidate data flow components into groups and expand or collapse the groups. To create a group, follow these steps:

  1.  On the data flow design surface, use your mouse to draw a box around the components that you want to combine as a group. If you prefer, you can click each component while pressing the Ctrl key.
  2.  Right-click one of the selected components, and select Group. A group containing the components displays in the package designer, as shown below.
  3.  Click the arrow to the right of the group label to collapse the group.

 

 

Deployment

With this deployment model type, the unit of deployment is a project, stored as an ISPAC file, which in turn is a collection of packages and parameters. You deploy the project to the Integration Services Catalog. Instead of configurations, you use parameters to assign values to package properties at run-time. Before executing a package, you must create an execution object in the catalog and optionally assign parameter values or environment references to the execution object.

This catalog is essentially a SQL application - a user database on a SQL instance (SSISDB) with a set of stored procedures and a T-SQL API.

Catalog Creation: Installation of Integration Services on a server does not automatically create the catalog. To do this, follow these steps:

1. In SQL Server Management Studio, connect to the SQL Server instance, right-click the Integration Services folder in Object Explorer, and select Create Catalog.

2. In the Create Catalog dialog box, select the Enable CLR Integration checkbox. This feature is required to manage Integration Services functionality.

3. Optionally, you can select the Scan For Automatic Execution Of Integration Services Stored Procedure At SQL Server Startup checkbox. This stored procedure performs a cleanup operation when the service restarts and adjusts the status of packages that were executing when the service stopped.

4. Notice that the catalog database name cannot be changed from SSISDB, so the final step is to provide a strong password, and then click OK. The password creates a database master key that Integration Services uses to encrypt sensitive data stored in the catalog.

Logging and Troubleshooting Tools

The built-in reports in SQL Server Management Studio for Integration Services provide information on package execution results for the past 24 hours, performance, and error messages from failed package executions. Hyperlinks in each report allow you to drill through from summary to detailed information to help you diagnose package execution problems.

 Package Format Changes

The goal of these changes was to make the SSIS package format easier to read, and easier to diff when working with source control systems.

SSIS 2008

 SSIS 2012

Summary

One of the biggest changes is the introduction of the project deployment model to simplify changes to package values at run-time. A side benefit of the project deployment model is the catalog which captures information about packages, validations, and execution results in tables that you can query through views or by using built-in reports. This access to information gives you greater visibility into Integration Services than was not possible in previous versions without extensive customization.

“The opinions and views expressed in this blog are those of the author and do not necessarily state or reflect those of Microsoft”