Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Login
New Account?
Recovery
Go to Login
By continuing you indicate that you agree to Terms of Service and Privacy Policy of the site.
SSIS

SSIS- Conditional Split Transform

 
Updated on Sep 30, 2020

This is a Fast Forward tutorial on Conditional Split Transformation in Microsoft SQL Server Integration Services.

Conditional Split

Conditional Split transformation is basically like CASE in VB or 'switch... case' in C programming language. This is also similar to the "Router Transform" available in Informatica or "CASE Transform" in Data Services ETL tool. Whenever there are multiple filter conditions or we want to route data to multiple pipe lines based on some conditions, we use Conditional Split transformation. The transformation evaluates expressions and directs the record 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.

Implementation

Let us create a Data Flow as below. Here we have an employee source table and we want to sent the employee data as flatfiles to individual departments with their corresponding employee information only. So for this purpose we will use Conditional Split transform.

Control Flow
Control Flow

Here we have a OLEDB Source EMP table. Next we use a Conditional Split transform to filter the data for each individual departments, here in this case it's three. And finally we use three Flat File Destination components to generate respective employee data flat files.

Data Flow
Data Flow

Double-click the Conditional Split transform to open the Editor. Here we have three layout areas one having the input columns and System & User defined variable; next we have the function libraries and operators; Finally the below area we define the Groups and Condition for data split. Let's define three Output Groups with condition/expression as shown below. Provide an expression that evaluates to a Boolean for each condition we want the transformation to test. Also we have the option to alter the name of the Default output group name.

Conditional Split Transformation Editor
Conditional Split Transformation Editor

Next Right-click the transform and select the Advanced Editor.

Conditional Split Transform Advanced Editor- Component Properties
Conditional Split Transform Advanced Editor- Component Properties

Conditional Split Transform Advanced Editor- Input Columns. The input column DEPTNO is used to evaluate the expression/condition to split.

Conditional Split Transform Advanced Editor- Input Columns
Conditional Split Transform Advanced Editor- Input Columns

Below Conditional Split Transform Advanced Editor- Input & Output Properties.

Conditional Split Transform Advanced Editor- Input & Output Properties
Conditional Split Transform Advanced Editor- Input & Output Properties

Let's check the Flat File Destination object properties. Double-click to open the editor. Select the Flat File connection object name from the Flat File Connection Manager browser.

Flat File Destination Editor- Connection Manager
Flat File Destination Editor- Connection Manager

Next we check the option Overwrite data in the file. Next we go to the Mappings tab to define the Input to Destination column mappings.

Flat File Destination Editor- Mappings
Flat File Destination Editor- Mappings

Right click the Flat File Destination object and select Show Advanced Editor.

Flat File Destination Advanced Editor- Connection Managers
Flat File Destination Advanced Editor- Connection Managers

Flat File Destination Advanced Editor- Component Properties.

Flat File Destination Advanced Editor- Component Properties
Flat File Destination Advanced Editor- Component Properties

Below Flat File Destination Advanced Editor- Column Mappings

Flat File Destination Advanced Editor- Column Mappings
Flat File Destination Advanced Editor- Column Mappings

Below Flat File Destination Advanced Editor- Input & Output Properties

Flat File Destination Advanced Editor- Input & Output Properties
Flat File Destination Advanced Editor- Input & Output Properties

Use the Conditional Split transformation to route data rows to different outputs depending on the condition. The Order in Conditional Split is important as this is the sequence in which the conditions are evaluated. A row is is sent only to the output corresponding to the first condition that evaluates to true. In case a record matches multiple group conditions it routers through the group having lower order.

PrimeChess

PrimeChess.org

PrimeChess.org makes elite chess training accessible and affordable for everyone. For the past 6 years, we have offered free chess camps for kids in Singapore and India, and during that time, we also observed many average-rated coaches charging far too much for their services.

To change that, we assembled a team of top-rated coaches including International Masters (IM) or coaches with multiple IM or GM norms, to provide online classes starting from $50 per month (8 classes each month + 4 tournaments)

This affordability is only possible if we get more students. This is why it will be very helpful if you could please pass-on this message to others.

Exclucively For Indian Residents: 
Basic - ₹1500
Intermediate- ₹2000
Advanced - ₹2500

Top 10 Articles