1. GUI Improvements -
-Sort packages by name
-Package visualization
-Zoom
-Data flow source/destination wizard
-Grouping in dataflow
2. CDC (Change Data Capture) Task and Components -
-CDC is nothing but Incremental load loads all rows that have changed since the last load
-CDC needs to keep track of which changes have already been processed.
-CDC task does this by storing LSNs in a tracking table
-CDC source component reads from the CDC table function, based on the LSN it for from the CDC task.
-CDC transformation splits records into new rows, updated rows and deleted rows.
3. Flat File Connection Manager Changes -
-The Flat File connection manager now supports parsing files with embedded qualifiers. The connection manager also by default always checks for row delimiters to enable the correct parsing of files with rows that are missing column fields. The Flat File Source now supports a varying number of columns, and embedded qualifiers.
4. Offline Connection Managers -
-Integration Services now validates all connections before validating all of the data flow components when a package is opened and sets any connections that are slow or unavailable to work offline. This helps to reduce the delay in validating the package data flow. After a package is opened, you can also turn off a connection by right-clicking the connection manager in the Connection Managers area and then clicking Work Offline. This can speed up operations in the SSIS Designer.
5. New Functions/Expressions in SSIS 2012-
LEFT:
- You now can easily return the leftmost portion of a string rather than use the SUBSTRING function. Left syntax is the same as we know in T-SQL:
LEFT(character_expression,number)
REPLACENULL:
- You can use this function to replace NULL values in the first argument with the expression specified in the second argument. This is equivalent to ISNULL in T-SQL:
REPLACENULL(expression, expression)
TOKEN:
- This function allows you to return a substring by using delimiters to separate a string into tokens and then specifying which occurrence to return:
TOKEN(character_expression, delimiter_string, occurrence)
TOKENCOUNT:
- This function uses delimiters to separate a string into tokens and then returns the count of tokens found within the string:
TOKENCOUNT(character_expression, delimiter_string)
6. Easy Column Remapping in Data Flow (Mapping Data Flow Columns)
-When modifying a data flow, column remapping is sometimes needed
-SSIS 2012 maps columns on name instead of id
-It also has an improved remapping dialog
7. Shared Connection Managers -
-To create connection managers at the project level that can shared by multiple packages in the project. The connection manager you create at the project level is automatically visible in the Connection Managers tab of the SSIS Designer window for all packages.
-When converting shared connection managers back to regular (package) connection managers, they disappear in all other packages.
8. Scripting Enhancements-
-Now Script task and Script Component support for 4.0.
- Breakpoints are supported in Script Component
9. ODBC Source and Destination -
-ODBC was not natively supported in 2008
-SSIS 2012 has ODBC source & destination
-SSIS 2008 could access ODBC via ADO.NET
10. Reduced Memory Usage by the Merge and Merge Join Transformations –
The old SSIS Merge and Merge Join transformations, although helpful, used a lot of system resources and could be a memory hog. In 2012 these tasks are much more robust and reliable. Most importantly, they will not consume excessive memory when the multiple inputs produce data at uneven rates.
11. Undo/Redo - One thing that annoys users in SSIS before 2012 is lack of support of Undo and Redo. Once you performed an operation, you can’t undo that. Now in SSIS 2012, we can see the support of undo/redo.
-Sort packages by name
-Package visualization
-Zoom
-Data flow source/destination wizard
-Grouping in dataflow
2. CDC (Change Data Capture) Task and Components -
-CDC is nothing but Incremental load loads all rows that have changed since the last load
-CDC needs to keep track of which changes have already been processed.
-CDC task does this by storing LSNs in a tracking table
-CDC source component reads from the CDC table function, based on the LSN it for from the CDC task.
-CDC transformation splits records into new rows, updated rows and deleted rows.
3. Flat File Connection Manager Changes -
-The Flat File connection manager now supports parsing files with embedded qualifiers. The connection manager also by default always checks for row delimiters to enable the correct parsing of files with rows that are missing column fields. The Flat File Source now supports a varying number of columns, and embedded qualifiers.
4. Offline Connection Managers -
-Integration Services now validates all connections before validating all of the data flow components when a package is opened and sets any connections that are slow or unavailable to work offline. This helps to reduce the delay in validating the package data flow. After a package is opened, you can also turn off a connection by right-clicking the connection manager in the Connection Managers area and then clicking Work Offline. This can speed up operations in the SSIS Designer.
5. New Functions/Expressions in SSIS 2012-
LEFT:
- You now can easily return the leftmost portion of a string rather than use the SUBSTRING function. Left syntax is the same as we know in T-SQL:
LEFT(character_expression,number)
REPLACENULL:
- You can use this function to replace NULL values in the first argument with the expression specified in the second argument. This is equivalent to ISNULL in T-SQL:
REPLACENULL(expression, expression)
TOKEN:
- This function allows you to return a substring by using delimiters to separate a string into tokens and then specifying which occurrence to return:
TOKEN(character_expression, delimiter_string, occurrence)
TOKENCOUNT:
- This function uses delimiters to separate a string into tokens and then returns the count of tokens found within the string:
TOKENCOUNT(character_expression, delimiter_string)
6. Easy Column Remapping in Data Flow (Mapping Data Flow Columns)
-When modifying a data flow, column remapping is sometimes needed
-SSIS 2012 maps columns on name instead of id
-It also has an improved remapping dialog
7. Shared Connection Managers -
-To create connection managers at the project level that can shared by multiple packages in the project. The connection manager you create at the project level is automatically visible in the Connection Managers tab of the SSIS Designer window for all packages.
-When converting shared connection managers back to regular (package) connection managers, they disappear in all other packages.
8. Scripting Enhancements-
-Now Script task and Script Component support for 4.0.
- Breakpoints are supported in Script Component
9. ODBC Source and Destination -
-ODBC was not natively supported in 2008
-SSIS 2012 has ODBC source & destination
-SSIS 2008 could access ODBC via ADO.NET
10. Reduced Memory Usage by the Merge and Merge Join Transformations –
The old SSIS Merge and Merge Join transformations, although helpful, used a lot of system resources and could be a memory hog. In 2012 these tasks are much more robust and reliable. Most importantly, they will not consume excessive memory when the multiple inputs produce data at uneven rates.
11. Undo/Redo - One thing that annoys users in SSIS before 2012 is lack of support of Undo and Redo. Once you performed an operation, you can’t undo that. Now in SSIS 2012, we can see the support of undo/redo.
