Friends,
We come across many situations where we need to split data based on some conditions, like we mostly do in switch case statements or if else conditions in other programming languages and for doing the same we have Conditional Split task in SSIS. When ever you need to split the data coming from a source into many output paths based on some condition then CONDITIONAL SPLIT is the best bet.
It is used to transfer data to different output files or locations on satisfaction of the condition specified and content of the data. Here in this post lets see a simple example which explains about how to configure CONDITIONAL SPLIT transformation.
- Start Integration Services Project template. On the package1.dtsx file add a data flow task as shown.
- Edit the Data Flow task by selecting EDIT button on Right Click context menu. One Data Flow page is opened then drag and drop a Flat File source from toolbox to Data Flow pane.
- Now Edit the Flat File source as shown below to set the connection properties.
- When the popup opened, select NEW button to set the new Flat File connections to the FILE in which we have SAMPLE data. Now provide all the connection properties like path,delimiters ..
- Select the columns page to check whether DATA is reading as expected or not.
- If you see the Column3 in above screenshot, it is showing GENDER of Employees as either M or F. Now add a conditional split transformation to the data Flow pane and provide the output of Flat File source as input to Conditional Split transformation.
- Now edit the Conditional Split to configure the same and write the below CONDITION under CONDITION column of the transformations and provide proper OUTPUT Name as shown below.
For filtering MALE Employees – UPPER([Column 3]) == “M”
For filtering FEMALE Employees -UPPER([Column 3]) == “F”
- Now we need to add two Flat File Destination components and we can attach the conditional task output to them using the green arrow line. When ever you drag and drop on destination then it will ask for WHICK output we need to set as source as shown below and select the correct one for each destination.
- I selected Males as Input to First Flat File destination and Females as input to the second destination file. (I know i have used Males and Females which is WRONG but what to do, i did this mistake and captured Screenshots .. Please adjust .. :))
- Configure the Flat file destination for Males and Females data . Double click on the component, Click on new, Select the format as delimited, Click OK. Set the output file path and then click on Mapping tab and finally click OK. Repeat the same for the second file too.
- After configuring Click OK.
- Now execute the package and see the output. You can see the data moved to both the files by applying the condition given .. i.e Male Employees to ONE FILE and Female Employees to ANOTHER FILE.
This is it .. As simple as this .. You can easily split the data coming from input to many outputs based on condition. You can also use functions while providing the condition and you can find all the functions on the RIGHT TOP side on Conditional Split Transformation. Also remember that you can use VARIABLE values as part of SPLIT Condition. Hope you understood it .. Enjoy Coding !!
Regards,
Roopesh Babu V
