CONDITIONAL SPLIT TRANSFORMATION

Introduction:

            Conditional split transformation can split the data rows to different outputs based on given condition. If a row is not satisfied the given condition, then that row directed to the default output.

Requirement:

            Source table (Sample_tbl) is having birds and animals information. With the help of source data I need to create “Bird” and “Animal” files (Bird file should have bird’s information and animal file should have animal’s information).

Source table data:

Package creation:

• From control flow items, drag and drop “Data flow task” into designer surface.
• Right click on “Data flow task” and select edit option then it will go to “Data flow”
• From data flow items, drag and drop “OLEDB source” into designer surface. Create the source table mappings.

• After source mappings, drag and drop “Conditional split” into designer surface.
• Connect the source green arrow to the “Conditional split” transformation.
• Edit the “Conditional split” then “Conditional split transformation editor” window will open.

• We need to write required conditions in the “Conditional split transformation editor” window to split the data.
• Case1: Type==”Bird”
• Case2: Type==”Animal”

• In the “output Name" section, rename the cases with appropriate names. Shown below screen shot.

• Click “OK” button.
• From tool box drag and drop two “Flat file target” (one is for “Birds” and other one is for “Animals”)
• Select “Conditional split” green arrow and connect to one of the “Flat file target” then “Input output selection” window will open, shown below.

• In the “Input and output selection” window select the output “Birds” by using “Output:” drop down list. Shown below screen shot.

• Click “OK”.
• Select the "Conditional split" green arrow and connect to the second “Flat file destination”. Input output selection window will open. In the selection window “Output:” drop down list select “Animals”.

• Click “OK” to close “Input output selection” Window.

• Edit the “Birds” and “Animals” Flat file destinations one after other one and give the mappings (target file path and name etc). Once mappings get finished then  execute the package.

Target files output:

Birds:

Animals: