Summary: Here we will be doing a demonstration for conditional split transformation provided in SSIS. To demonstrate this we will upload data from two text file to two different table based on some condition. Below is a screen shot of text file which will be uploaded.
Here we will upload all rows with PlantType Seasonal to Seasonal table and all Plant Type Perennial to other table named Perennial.
In our example we will use following objects/task of SSIS:
1) ForEach Loop container
2) Data Flow Task
3) Flat File Source
4) Conditional Split Transformation
5) ADO.Net Destination
Below is a well draft sample on how to use Conditional Split transformation.
Step 1: Click on Start —-> Programs —–> Microsoft SQL Server 2008 —-> SQL Server Business Intelligence Development Studio
Step 2: Now On below screen Go to File —-> New —-> Project
Step 3: By clicking Project we will get below window. In this window select Integration Services Project and give a proper name to solution and project. Then click OK.
Step 4: You will get following screen. As you can see, it has four tabs Control Flow, Data Flow, Event Handlers and Package Explorer.
Step 5: While staying on Control Flow tab, from toolbox side menu drag and drop For Each Loop Container on Control Flow Pane. And inside drag a Data Flow task, your screen will look like below. I have edit name as per my requirement.
Step 6: Configure you Foreach loop container as below. Right click on For Each Loop Container select EDIT. Below screen will appear, fill in the required properties:
Below is the list of properties and values to be supplied
Enumerator: For each File Enumerator
Folder: Give full path of folder in which we need to iterate.
Files: *.* (this will iterate through all files)
Retrieve file name: Fully qualified
Step 7: Now go to Variable Mappings. Create a new variable as shown on screen. Then click OK
Step 8: Now double click on DataFlow task and this will take you to Data Flow tab as shown below. Here drag Flat File Source, Conditional Split transformation and two ADO.Net Destination components from side menu. Join them with each other as shown below.
Step 9: As we have two input files, we need to configure Flat File Connection Manager in such a way that it takes both of them. To do it, click on the FlatFile connection manager you have created, in my case it is Data File. Then press F4 to open Properties menu for Flat File connection manager. There go to Expression as shown below. Both things are highlighted in red.
or
Step 10: Click on doted button of Expression and it will open Property Expression Editor. Select ConnectionString from Property and click on expression, it will open Expression Builder there select the variable which you have created to store each file name in the folder. In our case it is vFLowerSourceFile. By setting this we are saying that connection will be done to each file which this variable holds. And then this will process all files present in the folder. Now click OK.
Step 11: Configure Flat File source with the files which you want to import. Now double click on Conditional Split and configure it as shown below:
Here you can see we have specified two conditions
a) PlantType==”Seasonal”
b) PlantType==”Perenial”
So when this task will run, it will split the flow of rows to two different destination based on condition
Default Output Name: This is default condition which means if program encounters any row which does not satisfy any mentioned condition then it will flow through this default one.
Step 12: When you join green arrow from conditional split transformation towards ADO.Net destination it will pop up a window, there you need to specify which condition row will go to which destination. This is shown below
Step 13: Configure ADO.Net destination component as below. Mention the data source and table where you want to direct rows
Step 14: Now run the package and you will see all green task as shown here (obviously when you configure everything correctly J )
Step 15: Now you can check SQL server table to check whether rows has been correctly inserted or not.
So here we have seen how to use conditional split transformation. This transformation is very helpful while uploading data. We can have n numbers of conditions here.
Thanks Pallavi for the clean document.
Regards,
Roopesh Babu V
