SSIS - How To Use Conditional Split Transformation in SSIS Package

Scenario:

Let’s say we get data for three countries (USA, Mexico, and Brazil) in one flat file. “The Country Name” column holds the names for countries. We have created three different tables each with country name (dbo.tbl_USA, dbo.tbl_Mexico, dbo.tbl_Brazil). We want to read data from flat file and redirect related data to each of the table.

Solution:


SSIS provide transformation called “Conditional Split” that can route data rows to different outputs depending upon the data in column. In our case we will use Country Name to generate three outputs. The transformation also provides default output. If we will get any data different from USA, Mexico, and Brazil that will be redirect to default output.

Here is the data I used in flat file
CountryName,SalePersonName,SaleAmount
uSA,aamir shahzad,100
Italy,andy,200
UsA,Mike,500
brazil,Sara,1000
INdia,Neha,200
Brazil,Barbra,200
Mexico,Anthony,500

Step 1:
Create flat file connection to your text file in which you have used above data. 

Step 2: 
Bring Conditional Split transformation to Data Flow pane as shown in figure and connect Flat File Source to Conditional Split transformation. Write expressions as shown below in snapshot. Please note that Conditional Split is case sensitive. We can use single or multiple columns and conditions in expressions depending upon the requirement.
How to use Conditional Split Transformation in SSIS Package to split data into multiple outputs 

Step 3: 
Connect Outputs to required destinations. In our example I have used Multicast transformations as Test destinations and data viewers between Conditional Split and Multicast so we can see the data. As we can see the data is redirected to required outputs depending on the Country Name. As we do not have any conditions for India and Italy so those rows are directed to Default Output.

How to use write Expressions in Conditional Split transformation to split incoming data to multiple outputs in SSIS Package

1 comment:

Subscribe to: Post Comments (Atom)