Here are some improvements in SQL Server Integration Services 2012
from SQL Server Integration Services 2008.
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
