Conditional Split Transformation in SSIS with Example

By
roopesh.valluru
-
5555
50447

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

RELATED ARTICLESMORE FROM AUTHOR

5555 COMMENTS

  54. [url=https://drugsonline.store/#]real cialis without a doctor’s prescription[/url] ed medications comparison

  69. [url=https://doxycyclineonline.store/#]doxycycline without prescription[/url] doxycycline for sale

  95. [url=https://canadiandrugs.best/#]canadian online drugstore[/url] buy prescription drugs from canada

  109. [url=https://canadiandrugs.best/#]prescription drugs[/url] ed meds online without doctor prescription

  119. [url=http://stromectolbestprice.com/#]where to buy ivermectin[/url] ivermectin for goat lice

  131. [url=https://medrxfast.com/#]discount prescription drugs[/url] pet meds without vet prescription canada

  136. [url=https://medrxfast.com/#]п»їed drugs online from canada[/url] cheap pet meds without vet prescription

  200. cialis no prescription In a short time, it which otc male sexual enhancement pills contain viagra cialis seems to which otc male sexual enhancement pills contain viagra cialis have reached which otc male sexual enhancement pills contain viagra cialis the starry realm

  210. Pfizer recommends that you follow specific precautions when buying Viagra online, including ensuring that the website requires a valid prescription; that you re purchasing from a U soft tab cialis And there is no single federal or state agency in charge of overseeing online prescription drug services

  215. I have been looking for articles on these topics for a long time. safetoto I don’t know how grateful you are for posting on this topic. Thank you for the numerous articles on this site, I will subscribe to those links in my bookmarks and visit them often. Have a nice day

  224. The first set consists of CV 3, CV 6, ST 29 bilaterally, SP 6 bilaterally, SP 9 bilaterally, governor vessel GV 20, and large intestine LI 4 bilaterally. buy clomid online goodrx metformin metformin 100 mg is 2000mg of metformin too much what is the benefit of taking metformin at night.

  228. Cheers. Quite a lot of advice.
    pay someone to write your essay [url=https://flowleadsua.com]https://flowleadsua.com/[/url] professional personal statement writing services

  235. You actually suggested that exceptionally well.
    assignment essay help [url=https://theessayswriters.com]https://theessayswriters.com/[/url] help writing a thesis statement

  236. Beneficial stuff. Thanks a lot!
    why do you want to go to college essay [url=https://homeworkcourseworkhelps.com]essays writing services[/url] cheap custom essay writing services

  238. You stated this exceptionally well.
    writing prompts for persuasive essays [url=https://theessayswriters.com]https://theessayswriters.com/[/url] best resume writing services nj

  243. You actually explained this wonderfully!
    prescription drugs canada [url=https://canadianpharmacies-24h.com]canadadrugs pharmacy[/url] canada pharmacies prescription drugs

  249. 2021 Jun 15;13 357-366. tamoxifen citrate 20mg Meagnella – Like littleb I too have spotting before full AF arrives and doc said to take on first full flow day, r u taking day 2-6, if you got it wrong by a day I don t think it would be too much of an issue as some people can take it up to day 9.

  271. You suggested it perfectly!
    prescription drugs online without [url=https://canadianpharmacylist.com]online canadian discount pharmacy[/url] canadian drugstore reviews

  281. Seriously a lot of valuable material.
    humana online pharmacy [url=https://canadianpharmacylist.com]canadian prescription drugs[/url] prescription drugs from canada

  287. You said it perfectly..
    how to write a compare and contrast essay introduction [url=https://essaywriting4you.com]https://essaywriting4you.com/[/url] pre written essays for sale

  291. Incredible a lot of excellent information.
    canadian drugs online pharmacies [url=https://canadianpharmacies-24h.com]online pharmacy usa[/url] best 10 online canadian pharmacies

  305. Amazing all kinds of fantastic facts.
    how to write narrative essay [url=https://dissertationwritingtops.com]essay typer[/url] essay writing service recommendation

  309. You actually suggested it superbly.
    writing a college admissions essay [url=https://service-essay.com]good essay writers[/url] medical personal statement writing service

  314. Incredible all kinds of useful facts.
    canadian pharmacies-247 [url=https://onlinepharmacycanadahelp.com]prescription drugs online without doctor[/url] cheap medications

  318. You actually mentioned that exceptionally well.
    write essays for me [url=https://freshappshere.com]transfer college essays[/url] i need someone to write my assignment

  325. With thanks, An abundance of forum posts!
    college essay writer for pay [url=https://theessayswriters.com]https://theessayswriters.com/[/url] case study writing service

  326. дом престарелых
    [url=https://dom-prestarelyh-vrn2.ru ]дом престарелых[/url]
    дом престарелых
    [url=https://dom-prestarelyh-samara2.ru]частный дом престарелых[/url]

  345. дом престарелых
    [url=https://dom-prestarelyh-vrn2.ru ]дом для престарелых[/url]
    дом для престарелых
    [url=https://dom-prestarelyh-samara2.ru]дом престарелых[/url]

  363. สล็อต แตก ง่าย ที่สุดของการเล่นเกมสล็อตที่ใครๆก็ประทับใจ ด้วยความมากมายของตัวเกมที่ได้มีการคัดมาอย่างดีเยี่ยม PGSLOT-TH.COM มีการแจกรางวัลโบนัสสูงที่สุดตั้งแต่ที่เคยมีมา

  394. [url=https://canadian-pharmacy.shop/#]canadian pharmacies that deliver to the us[/url] onlinepharmaciescanada com