SSIS - New Features Of SQL Server Integration Services (SSIS) 2012

Here are some improvements in SQL Server Integration Services 2012 from SQL Server Integration Services 2008.

1-Undo Change in SSIS Package: 

Undo was not available in SSIS Package. Once you have deleted any Task or Transformation, there was no way to undo the change. SSIS 2012 has added Undo facility. You can use Ctrl+Z or Click on the Button as shown in below picture
Fig 1: Undo in SSIS 2012

2-Group Tasks and Transformations:

The Tasks and Transformations can be grouped together. You will not be using this Group to connect to other Containers or Tasks. This grouping is just for arranging the Tasks or Transformations but you will not be able to set the properties like Containers.
Fig 2: Grouping Tasks and Transformation in SSIS 2012


3-Change the Scope of Variable/s:

In earlier versions of SSIS, to change the scope of variable from one Task to another Task or from Task level to Package level, we had to delete the variable and then create with new scope. In SSIS 2012, we can change the scope of variable without deleting and recreating it as shown below.
Fig 3: Change Variable Scope in SSIS 2012

4-Decimal Data Type Variable:

New Data Type is added for Variables as shown below. More Details Link
Fig 4: Decimal Data Type for Variable

5-Expression Character Limit

We often have to write expression in Execute SQL Task for source query. The character limit for SQL Server 2008R2 and old version are 4000. This limit is removed in SQL Server 2012 Integration Services.

6-SSIS ToolBox and Variable Button

SSIS ToolBox and Variables buttons are  added to Package Design Surface as shown below.
Fig 6: SSIS 2012 ToolBox and Variables button

7-Zoom In and Zoom Out 

For Zoom in and Zoom out a scale is added at the right bottom corner of Package design surface. Also the Button for navigation button is removed that was available in right bottom corner of Package design surface in SSIS 2008R2 and old versions.
Fig 7:Zoom in/out Scale on Package Design Surface

Fig 7.1 SSIS 2008R2 and earlier versions Package Navigation Button

8-Parameters

With new Project Deployment Model, Parameters are added to provide configuration values. The Parameter can be on Project level or Package level. We can change the value of these parameters without making any change in Package or Project itself while Package is going to execute in different environments such as DEV, QA, UAT and Prod. Parameters can be accessed by Right Clicking on Tasks, Containers etc. You will also see the Parameters while writing expressions and you can use them if required.
When you create a new Parameter, you can Add to Configuration as well from SQL Server Data Tools.
Fig 8: Parameters in SSIS 2012

9-New Added Functions for Expressions

Below are the newly added functions which can be used while we write expressions.


  • LEFT 
  • TOKEN
  • TOKENCOUNT
  • REPLACENULL

10- Row Count New GUI

Row Count GUI is simplified, The purpose of Row Count was to save the Count in user variable but Row Count Transformation in SSIS 2008R2 and earlier versions were also displaying all system variables. In SSIS 2012, it only displays the Variables which are Integer Type and does not provide the list of all variables that helps to configure this Transformation quickly.

Fig 10: Row Count GUI in SSIS 2008R2 

Fig 10.1 Row Count GUI in SSIS 2012

11- Data Viewer

Extra features of Data Viewer are removed such as see the data as Histogram,Scatter Plot and Column Chart. I have been always using Grid and never used other types and specially for debugging other charts really does not help.
Fig 11: SSIS 2008R2 Data Viewer

In SSIS 2012 , Only Grid is available and other types are removed.
Fig 11.1 : SSIS 2012 Data Viewer 


12-Pivot Transformation GUI Improvements

New Graphical User Interface is provided in SSIS 2012 for Pivot Transformation to configure it quickly and easily. Click Link to see example of SSIS 2012 Pivot Transformation and how to use it.

Fig 12: New GUI for Pivot Transformation in SSIS 2012

13-ODBC Source and ODBC Destination

ODBC Source and ODBC Destination is added in SQL Server Integration Services 2012.
Fig 13: ODBC Source and ODBC Destination in SSIS 2012

14-CDC Control Task

The CDC Control Task is added to SSIS 2012 to perform initial load for CDC enable table.
Fig 14: CDC Control Task

15-CDC Source

CDC Source is added to read the change data from CDC tables.
Fig 15: CDC Source in SSIS 2012

16- CDC Splitter

CDC Splitter Transformation is added in SSIS 2012 to split the data into multiple flows for Insert, Update and Delete Operation.
Fig 16: CDC Splitter SSIS 2012


To be continued.....




6 comments:

  1. Thanks Amir for extensive list of differences at one place.
    Keep it up!

    ReplyDelete
  2. thanks for valuable info at one place.

    ReplyDelete
  3. Thanks for the useful information! Also, I would like to add that when I worked with SQL Server Integration Services (SSIS) 2012 I needed to connect to MySQL database. So for this purpose, I found ssis data flow components https://www.devart.com/ssis/

    ReplyDelete
  4. Thank you so much for clear and understandable presentation!!!

    ReplyDelete
  5. As for the connection to database, I use odbc connection.

    ReplyDelete