MSBI_SSIS_Conditional Split Transformation

Conditional split transformation is used to split the data into multiple destinations depends on the condition.

Let's assume that there is a source table Employee and the destination requirement is to move the data to multiple tables, each table as a specific department number. (E.g : For DEPTNO 10 need to create a separate table,For DEPTNO 20 need to create a separate table.... etc. ) 

We can write a query in SQL Server as:
/* For DEPTNO 10 */
SELECT * FROM Employee
WHERE DEPTNO = 10

/* For DEPTNO 20 */
SELECT * FROM Employee
WHERE DEPTNO = 20

/* For DEPTNO 30 */
SELECT * FROM Employee
WHERE DEPTNO = 30

We can implement it in a SSIS package as below,

• Take a Data Flow task to control flow. 
• Edit the Data Flow as drag & drop OLEDB Source, Conditional Split Transformation and 3 OLEDB Destinations(name it as DEPT10, DEPT20, DEPT30) to Data Flow for design. 
• Edit the OLEDB Source, provide the connection and authentication details and select the Employee table. 
• Make a connection from OLEDB Source to Conditional Split Transformation.
• Edit the Conditional Split Transformation as below, 

In the "Conditional Split Transformation Editor" window expand the Columns folder, drag and drop the DEPTNO column to Condition box assign the values(Conditions), click on OK.

• Now make a connection from Conditional Split Transformation to DEPT10 Destination, it will displays a "Input Output Selection" window.
• Here first select the Output "DEPT 10" which we given in Conditional Split Transformation. Now make connections from Conditional Split Transformation to DEPT20,DEPT30 destinations same as earlier. 

**Note: "Conditional Split Default Output" is used to fetch the remaining data which is not participated in the conditions(For example DEPT NO 40,50...).

• Edit all the Destinations and provide the connections, authentication details and create a new table for each destination with respective Department number(E.g: EMP_DEPT10,EMP_DEPT20....).
• Check the data in the destination tables.