New SSIS 2012 Designer Features
- 9/15/2012
- The Integration Services Designer
- Configuration and Deployment
- New Tasks and Data Flow Components
- Control Flow
- Data Flow
- Scripting
- Expressions
- Summary
New Tasks and Data Flow Components
Change Data Capture
SQL Server 2012 introduces a new set of components that make it easier to perform Change Data Capture (CDC) with SQL Server and Oracle. The components include a CDC Control task, a CDC Source component, and a CDC Splitter transformation.
CDC Control Task
The CDC Control task (Figure 4-16) is used to set and track the state of your CDC operations.
){kind=link}
Figure 4-16 The CDC Control task.
You’ll typically use this task to mark start and end dates for your CDC process, or to retrieve the next range of dates to process. The task will store all of the state information needed by the CDC Source component in a variable. You can also persist the state information in a database table to maintain state across package executions. Figure 4-17 shows the CDC Control Task Editor user interface.
)
Figure 4-17 The editor for the CDC Control task.
CDC Source
The new CDC Source Data Flow component (Figure 4-18) is used to retrieve changed rows from a CDC-enabled table. The CDC Source works with both SQL Server and Oracle sources.
)
Figure 4-18 The CDC Source component.
CDC Splitter
The CDC Splitter transform works a lot like the Conditional Split transform. It will automatically split rows coming from a CDC Source component. Incoming rows will be sent down the Insert, Update, or Delete paths, based on the CDC operation type of the row (Figure 4-19).
)
Figure 4-19 The CDC Splitter transform processes rows from the CDC Source component.
Expression Task
The Expression task (see Figure 4-20) can be used to explicitly set a variable value using an SSIS expression.
){kind=link}
Figure 4-20 The Expression task.
The task gives you control over when a value is applied to a variable, and can be used as an alternative to setting the variable’s EvaluateAsExpression property to True. Unlike expressions set directly on the variable (using EvaluateAsExpression), the Expression task evaluates only at runtime, not design time. This means that the variable value saved within the package file (.dtsx) is not modified by the Expression task. Figure 4-21 provides an example of an expression being set on the User::FilePath variable. Note the syntax is slightly different from regular SSIS property expressions in that it includes the name of the variable to which you are assigning the value.
)
Figure 4-21 The Expression task uses the same Expression Builder control as SSIS property expressions.
DQS Cleansing Transform
The DQS Cleansing transform allows you to cleanse data within your data flow using Data Quality Services, a new product in SQL Server 2012. This transform allows you to add one or more columns to domains within a DQS Knowledge Base. At runtime, incoming data will be sent to the DQS server for cleansing.
ODBC Source and Destination
SQL Server 2008 supported Open Database Connectivity (ODBC) through ADO.NET, but compatibility and performance with certain ODBC providers was not optimal. SSIS has increased its support for ODBC in SQL Server 2012 by providing new Source and Destination components in the Data Flow. Both Source and Destination make use of ODBC connection managers, and the Destination component supports Bulk Insert for most providers.