SQL Server Integration Service 2012 Enahcement and Advancement

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
SQL Server  2012 Release with lots of Enhancements in Database Engine functions, SSIS, SSRS and some of new services like Data Quality Server and Master Data Service.

Of particular interest, and the focus of this Article is SSIS. So, time to elaborate one by one some of these more important enhancements in SSIS.

1. SSIS Studio

1. BIDS is replaced by SQL Server Data Tool.

It has several enhancements like grouping of task and multi-line comments in Data flow task

Grouping  and multiline comment in Data flow2. Introduction of Parameter in SSIS.

As like command line or any other application you can pass parameter to SSIS package or even from Parent to Child package as well.

Same way you can create parameters at project level as well. The parameter define at project level is available and shared with all packages in project.
SSIS Parameter3. SSIS package / project Deployment Model.

Before SSIS 2012 all packages will be deployed to File system or on SQL Server.

Now onwards, the new feature Deployment  model is there and it's maintained in Database. To deploy the project Convert project to Project Deployment Model compatible using wizard  and create deployment Catalog that after.

SSISDB CatalogSSIS project deployment

2. SSIS Control flow Task

1. New CDC (Change Data Capture) Control flow Task

This task is useful to track the Change data (state) and manage that using CDC control task.

click here for more

2. Expression Task

The task is use full to write some logic using Variables and parameter other than the some of the Control flow tasks.

Assign and merge the value of one variable to another and create text etc.

3. SSIS Data flow Tasks

1 & 2. CDC (Change Data Capture) Source and CDC Splitter Transformation Task

CDC Source is same like normal OLEDB Source.

You have to select CDC enabled DB connection as source, CDC enabled Table to track changes, state version and variable to maintain state of Change track.

CDC Source CDC splitter is split the row as per the operation and redirect to 3 different ways (Inserted, Deleted and Updated)

CDC SplitterNote: to use CDC Change track should be enabled on Database and on Table as well.

click here for more

3 &  4. ODBC Source and Destination

There is a new support to ODBC. Its same like OLEDB connections. To use this you have to create ODBC connection.

5. DQS Cleansing Transformation Task

Data Cleansing is the service to keep your data upto date and remove the redundancy or typo mistakes.

It get the data from above task compare with source or master data service fetch the appropriate to that one and save to destination.

click here for more
Hope you enjhoy some of the new and enhanced features released with SQL 2012
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Comments (1)

Good Article Alpesh

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.