Conditional Split Transformation

Conditional Split Transformation

- The Conditional Split transformation enables us to split the data flow into multiple outputs.
- we define conditions for each branch of the split, also we can give default output
- Default output means when package gets executed, each row will be compared with all branches, when it does not follow into any category then  it will be part of Default branch.

EXAMPLE

Pre-requisite

Execute following script in DB

CREATE TABLE ConditionalSplitDemoSource (A INT, Name nvarchar(10),City nvarchar(10))

GO

INSERT INTO ConditionalSplitDemoSource (A,Name,City)

VALUES

(1,'Nisarg','Hyd'),

(2,'Megha','Hyd'),

(3,'Lalu','Meh'),

(4,'Nidhi','Ahd'),

(5,'Swara','Ahd'),

(6,'Maulik','Ahd'),

(7,'Dipa','Ahd'),

(8,'Pratibha','Del')

Go

CREATE TABLE ConditionalSplitDemoDestination1 (A INT, Namenvarchar(10), City nvarchar(10))

CREATE TABLE ConditionalSplitDemoDestination2 (A INT, Namenvarchar(10), City nvarchar(10))

CREATE TABLE ConditionalSplitDemoDestination3 (A INT, Namenvarchar(10), City nvarchar(10))

CREATE TABLE ConditionalSplitDemoDestination4 (A INT, Namenvarchar(10), City nvarchar(10))

CREATE TABLE ConditionalSplitDemoDestination5 (A INT, Namenvarchar(10), City nvarchar(10))

Steps

1. Add Data Flow Task in Control Flow
2. Inside DFT, drag source which should point to ConditionalSplitDemo table
3. Drag Conditional Split Transformation. Add the conditions like this.

3. Conditions can be re-ordered using up and down arrow. We can use any column or variable or system function to prepare condition.
4. Drag 3 OLEDB Destinations and connect them with Conditional Split using different output.

5. 3rd output is remaining, means record which does not follow any condition will be part of this bucket.

SSIS Conditional Transform Tasks

Introduction

Conditional Transform Task is used to split the data according to the condition and save the data into different destination. In this article we are going to discuss about the Conditional Transform Task.

Case Study

We have a flat file named EmployeeDeatils.txt. The flat file contains EmployeeID, Employee Name and Country of Origin. Depends on the country of Origin it segregate the records into two different texts file named EmployeeofUS.txt and EmployeeofINDIA.txt.

How to Configure the Conditional Transform

Step-1 [ Source Flat File ]

Step-2 [ SSIS Data flow Task ]

Step-3 [ Flat file source editor configuration ]

Step-4 [ Conditional Split Transformation Editor Configuration ]

Step-5 [ Flat File Destination Editor Configuration ]

Step-6 [ Run the SSIS Package ]

Step-7 [ Analyzing the Output of the SSIS Package ]

Hope you like it.