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, Name nvarchar(10), City nvarchar(10))

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

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

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

CREATE TABLE ConditionalSplitDemoDestination5 (A INT, Name nvarchar(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.