{"id":878,"date":"2023-01-30T08:05:22","date_gmt":"2023-01-30T14:05:22","guid":{"rendered":"https:\/\/www.sqlfreelancer.com\/blog\/?p=878"},"modified":"2023-01-30T08:05:22","modified_gmt":"2023-01-30T14:05:22","slug":"ssis-series-how-to-use-conditional-split","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/ssis-series-how-to-use-conditional-split\/","title":{"rendered":"SSIS Series: How to use Conditional Split"},"content":{"rendered":"<p>From <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/integration-services\/data-flow\/transformations\/conditional-split-transformation?view=sql-server-ver16\">Microsoft<\/a>, the Conditional Split transformation can route data rows to different outputs depending on the content of the data. The implementation of the Conditional Split transformation is similar to a CASE decision structure in a programming language. The transformation evaluates expressions, and based on the results, directs the data row to the specified output. This transformation also provides a default output, so that if a row matches no expression it is directed to the default output.<\/p>\n<p>You can configure the Conditional Split transformation in the following ways:<\/p>\n<ul>\n<li>Provide an expression that evaluates to a Boolean for each condition you want the transformation to test.<\/li>\n<li>Specify the order in which the conditions are evaluated. Order is significant, because a row is sent to the output corresponding to the first condition that evaluates to true.<\/li>\n<li>Specify the default output for the transformation. The transformation requires that a default output be specified.<\/li>\n<\/ul>\n<p>Let&#8217;s take a look at how this transformation might be used in the real world.<\/p>\n<p>Open Visual Studio and drag a Data Flow task into the design pane. Open the Data Flow task and drag in an OLE DB Source task. For this post, I&#8217;m going to use the AdventureWorks2019 database and the HumanResources.vEmployeeDepartment view.<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-1-1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-883\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-1-1.png\" alt=\"\" width=\"1381\" height=\"1091\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-1-1.png 1381w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-1-1-300x237.png 300w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-1-1-1024x809.png 1024w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-1-1-768x607.png 768w\" sizes=\"auto, (max-width: 1381px) 100vw, 1381px\" \/><\/a><\/p>\n<p>This view has some good data to play around with, but we&#8217;re going to focus on the Department and Start Date columns. Let&#8217;s pretend the bossman needs to see all of the Employees in the Quality Assurance (QA), Production and Sales Department in a separate database table. Bonus, he needs to see all of the Production employees split up into two more tables based on who started before and after Jan 1, 2010. All other employees can go into their own table. Got it? Great! That&#8217;s 5 total tables. QA=1, Production=2, Sales=1, Leftovers=1 Let&#8217;s go.<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-882\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-2.png\" alt=\"\" width=\"1406\" height=\"910\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-2.png 1406w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-2-300x194.png 300w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-2-1024x663.png 1024w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-2-768x497.png 768w\" sizes=\"auto, (max-width: 1406px) 100vw, 1406px\" \/><\/a><\/p>\n<p>Back in Visual Studio, drag in a Conditional Split task and connect it to our OLE DB Source.<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-3.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-884\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-3.png\" alt=\"\" width=\"827\" height=\"480\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-3.png 827w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-3-300x174.png 300w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-3-768x446.png 768w\" sizes=\"auto, (max-width: 827px) 100vw, 827px\" \/><\/a><\/p>\n<p>Open the Conditional Split task editor and you&#8217;ll see a few options (from left to right, top to bottom):<\/p>\n<ol>\n<li>We can use columns and\/or variables and parameters in our expressions that define how to split the data flow.<\/li>\n<li>We can use functions such as Date\/Time, NULL and String in our expressions that define how to split the data flow.<\/li>\n<li>These are the conditions that define how to split the data flow. These need to be set in priority order; any rows that evaluate to true for one condition will not be available to the condition that follows.<\/li>\n<\/ol>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-4.png\"><br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-885\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-4.png\" alt=\"\" width=\"951\" height=\"1045\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-4.png 951w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-4-273x300.png 273w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-4-932x1024.png 932w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-4-768x844.png 768w\" sizes=\"auto, (max-width: 951px) 100vw, 951px\" \/><\/a><\/p>\n<p>Let&#8217;s start adding some conditions for our data. First, we&#8217;ll add a condition for all of our QA Department Employees. I&#8217;ll name the output &#8220;QA&#8221; and my condition is pretty simple whereas Department == &#8220;Quality Assurance&#8221;.<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-5.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-886\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-5.png\" alt=\"\" width=\"951\" height=\"1045\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-5.png 951w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-5-273x300.png 273w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-5-932x1024.png 932w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-5-768x844.png 768w\" sizes=\"auto, (max-width: 951px) 100vw, 951px\" \/><\/a><\/p>\n<p>I&#8217;ll do the same for Production, Sales and Leftovers (everything else that doesn&#8217;t satisfy a condition). Since Leftovers is everything else we&#8217;ll just change the name of the Default Output name to identify it.<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-6-1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-890\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-6-1.png\" alt=\"\" width=\"951\" height=\"1049\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-6-1.png 951w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-6-1-272x300.png 272w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-6-1-928x1024.png 928w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-6-1-768x847.png 768w\" sizes=\"auto, (max-width: 951px) 100vw, 951px\" \/><\/a><\/p>\n<p>Let&#8217;s go ahead and add our destination tasks (except for Production since we need another condition) and link them to the appropriate condition. See below for QA as an example. When we drag our connector to our destination task we get prompted with an Input Output Selection box. Here is where we choose our Condition that will match up with our table. For the screenshot below, we&#8217;ll choose QA output for our QA destination.<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-7-1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-891\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-7-1.png\" alt=\"\" width=\"1547\" height=\"719\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-7-1.png 1547w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-7-1-300x139.png 300w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-7-1-1024x476.png 1024w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-7-1-768x357.png 768w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-7-1-1536x714.png 1536w\" sizes=\"auto, (max-width: 1547px) 100vw, 1547px\" \/><\/a><\/p>\n<p>Now that we have QA mapped, go ahead and map Sales and Leftovers.<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-8.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-889\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-8.png\" alt=\"\" width=\"894\" height=\"640\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-8.png 894w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-8-300x215.png 300w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-8-768x550.png 768w\" sizes=\"auto, (max-width: 894px) 100vw, 894px\" \/><\/a><\/p>\n<p>Looks great!\u00a0 QA, Sales and Leftovers are mapped successfully. Let&#8217;s take a look at adding another Conditional Split task for Production. Drag a Conditional Split task into the design pane and connect it to the current Conditional Split. It automatically maps to Production since it&#8217;s the only output left.<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-9.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-892\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-9.png\" alt=\"\" width=\"1150\" height=\"686\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-9.png 1150w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-9-300x179.png 300w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-9-1024x611.png 1024w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-9-768x458.png 768w\" sizes=\"auto, (max-width: 1150px) 100vw, 1150px\" \/><\/a><\/p>\n<p>From our new Conditional split task, let&#8217;s open the editor and configure the date conditions for Production. We&#8217;ll leave the Default output name box as is since we shouldn&#8217;t have any leftover data from this split.<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-10.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-893\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-10.png\" alt=\"\" width=\"953\" height=\"1045\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-10.png 953w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-10-274x300.png 274w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-10-934x1024.png 934w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-10-768x842.png 768w\" sizes=\"auto, (max-width: 953px) 100vw, 953px\" \/><\/a><\/p>\n<p>Now we can map the two new conditions to their appropriate destinations.<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-11.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-894\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-11.png\" alt=\"\" width=\"1386\" height=\"770\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-11.png 1386w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-11-300x167.png 300w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-11-1024x569.png 1024w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-11-768x427.png 768w\" sizes=\"auto, (max-width: 1386px) 100vw, 1386px\" \/><\/a><\/p>\n<p>Cross fingers and hit Execute.<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-12.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-895\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-12.png\" alt=\"\" width=\"1451\" height=\"869\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-12.png 1451w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-12-300x180.png 300w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-12-1024x613.png 1024w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-12-768x460.png 768w\" sizes=\"auto, (max-width: 1451px) 100vw, 1451px\" \/><\/a><\/p>\n<p>Yay, no red X! Let&#8217;s take a look at our SQL tables to make sure everything exported correctly.<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-13.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-896\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-13.png\" alt=\"\" width=\"1363\" height=\"990\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-13.png 1363w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-13-300x218.png 300w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-13-1024x744.png 1024w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2023\/01\/SSIS-Conditional-Split-13-768x558.png 768w\" sizes=\"auto, (max-width: 1363px) 100vw, 1363px\" \/><\/a><\/p>\n<p>Boom. Let&#8217;s go grab a bourbon!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>From Microsoft, the Conditional Split transformation can route data rows to different outputs depending on the content of the data. The implementation of the Conditional Split transformation is similar to a CASE decision structure in a programming language. The transformation evaluates expressions, and based on the results, directs the data row to the specified output. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[30,49,139],"tags":[106,83,60,50],"class_list":["post-878","post","type-post","status-publish","format-standard","hentry","category-business-intelligence","category-integration-services-ssis","category-ssis-series","tag-business-intelligence","tag-sql-server-business-intelligence","tag-sql-server-integration-services","tag-ssis"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/878","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=878"}],"version-history":[{"count":3,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/878\/revisions"}],"predecessor-version":[{"id":899,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/878\/revisions\/899"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=878"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=878"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=878"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}