SSIS- Conditional Split Transform

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.

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 it's three. And finally we use three Flat File Destination components to generate respective employee data flat files.

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. Let's 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.

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

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

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

Let's 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.

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.

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

Flat File Destination Advanced Editor- Component Properties.

Below Flat File Destination Advanced Editor- Column Mappings

Below 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.