This is a Fast Forward tutorial on Conditional Split Transformation in Microsoft SQL Server Integration Services.

Conditional Split

Conditional Split transformation is basically like CASE in VB or 'switch... case' in C programming language. This is also similar to the "Router Transform" available in Informatica or "CASE Transform" in Data Services ETL tool. Whenever there are multiple filter conditions or we want to route data to multiple pipe lines based on some conditions, we use Conditional Split transformation. The transformation evaluates expressions and directs the record to the specified output. This transformation also provides a default output, so that if a row matches no expression it is directed to the default output.

Implementation

Let us create a Data Flow as below. Here we have an employee source table and we want to sent the employee data as flatfiles to individual departments with their corresponding employee information only. So for this purpose we will use Conditional Split transform.

Control Flow

Here we have a OLEDB Source EMP table. Next we use a Conditional Split transform to filter the data for each individual departments, here in this case its three. And finally we use three Flat File Destination components to generate respective employee data flat files.

Data Flow

Double-click the Conditional Split transform to open the Editor. Here we have three layout areas one having the input columns and System & User defined variable; next we have the function libraries and operators; Finally the below area we define the Groups and Condition for data split. Lets define three Output Groups with condition/expression as shown below. Provide an expression that evaluates to a Boolean for each condition we want the transformation to test. Also we have the option to alter the name of the Default output group name.

Conditional Split Transformation Editor

Next Right-click the transform and select the Advanced Editor.

Conditional Split Transform Advanced Editor- Component Properties

Conditional Split Transform Advanced Editor- Input Columns. The input column DEPTNO is used to evaluate the expression/condition to split.

Conditional Split Transform Advanced Editor- Input Columns

Below Conditional Split Transform Advanced Editor- Input & Output Properties.

Conditional Split Transform Advanced Editor- Input & Output Properties

Lets check the Flat File Destination object properties. Double-click to open the editor. Select the Flat File connection object name from the Flat File Connection Manager browser.

Flat File Destination Editor- Connection Manager

Next we check the option Overwrite data in the file. Next we go to the Mappings tab to define the Input to Destination column mappings.

Flat File Destination Editor- Mappings

Right click the Flat File Destination object and select Show Advanced Editor.

Flat File Destination Advanced Editor- Connection Managers

Flat File Destination Advanced Editor- Component Properties.

Flat File Destination Advanced Editor- Component Properties

Below Flat File Destination Advanced Editor- Column Mappings

Flat File Destination Advanced Editor- Column Mappings

Below Flat File Destination Advanced Editor- Input & Output Properties

Flat File Destination Advanced Editor- Input & Output Properties

Use the Conditional Split transformation to route data rows to different outputs depending on the condition. The Order in Conditional Split is important as this is the sequence in which the conditions are evaluated. A row is is sent only to the output corresponding to the first condition that evaluates to true. In case a record matches multiple group conditions it routers through the group having lower order.


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • SSIS- Derived Column Transform

    This is a Fast Forward tutorial on Derived Column Transformation in Microsoft SQL Server Integration Services.

  • SSIS- Union All Transform

    This is a Fast Forward tutorial on Union All Transformation in Microsoft SQL Server Integration Services.

  • SSIS- Conditional Split Transform

    This is a Fast Forward tutorial on Conditional Split Transformation in Microsoft SQL Server Integration Services.

  • SSIS- Aggregate Transform

    This is a Fast Forward tutorial on Aggregate Transformation in Microsoft SQL Server Integration Services.

  • SSIS- Merge Join Transform

    This is a Fast Forward tutorial on Merge Join Transformation in Microsoft SQL Server Integration Services.

  • SSIS- Multicast Transform

    This is a Fast Forward tutorial on Multicast Transformation in Microsoft SQL Server Integration Services.

  • SSIS- Lookup Transform

    This is a Fast Forward tutorial on Lookup Transformation in Microsoft SQL Server Integration Services.