<

SQL Server Integration Service 2012 Enahcement and Advancement

Published on
11,263 Points
3,563 Views
2 Endorsements
Last Modified:
Approved
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
2
Comment
1 Comment

Expert Comment

by:DipeshPrajapati
Good Article Alpesh
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month