SSIS Multicast Transformation vs Conditional Split Transformation

By:   |   Comments (9)   |   Related: More > Integration Services Data Flow Transformations


Problem

In a data warehousing scenario, it's not rare to replicate data of a source table to multiple destination tables, sometimes it's even required to distribute data of a source table to two or more tables depending on some condition.  For example splitting data based on location. So how we can achieve this with SSIS?

Solution

SSIS provides several built-in transformation tasks to achieve these kinds of goals and in this tip I am going to discuss two of them:

  • Multicast Transformation and
  • Conditional Split Transformation and how they differ from each other.

Multicast Transformation

Sometimes it's required to have multiple logical copies of source data of a single source table either for applying different transformation logic on each set or having each dataset for separate consumers, Multicast transformation helps to do this.

In nutshell, a Multicast transformation is used to create/distribute exact copies of the source dataset to one or more destination datasets. In this example I am going to distribute data from [HumanResources].[Employee] table to [HumanResources].[Employee1] and [HumanResources].[Employee2] tables, please note that the multicast transformation distributes the whole set of data to all outputs, it means if the [HumanResources].[Employee] table has 290 records then both [HumanResources].[Employee1] and [HumanResources].[Employee2] tables will have 290 records each as well.

Before creating the package, lets create some work tables which we will be using in this demonstration.  These commands will just create empty tables based on the [HumanResources].[Employee] table structure in the AdventureWorks database.

-- Script #1 - Create work tables for demonstration
USE AdventureWorks 
GO 
SELECT * INTO [HumanResources].[Employee1] FROM [HumanResources].[Employee] 
WHERE 1=2 
GO 
SELECT * INTO [HumanResources].[Employee2] FROM [HumanResources].[Employee] 
WHERE 1=2 
GO 
SELECT * INTO [HumanResources].[MaleEmployee] FROM [HumanResources].[Employee] 
WHERE 1=2 
GO 
SELECT * INTO [HumanResources].[FemaleEmployee] FROM [HumanResources].[Employee] 
WHERE 1=2 
GO 

Launch SQL Server Business Intelligence Development Studio and create a new project. 

Let's start with a "Data Flow Task". Double click on it to open the Dataflow Pane and then drag an OLE DB Source on the working area and configure as shown below.

Launch SQL Server Business Intelligence Development Studio and create a new project

Then drag a Multicast Transformation and OLE DB Destination from the Toolbox as shown below and connect the tasks using the green arrow of OLE DB Source to the Multicast and then from the Multicast to the OLE DB Destination as shown below. Next right click on the OLE DB Destination and specify the destination connection manager and destination table as shown below. Please check the "Keep Identity" property on the OLE DB Destination editor to preserve the identity value coming from the source table we are using in this example.

drag a Multicast Transformation and OLE DB Destination from the Toolbox

Next drag another OLE DB Destination from the Toolbox, drag another green arrow from the Multicast to the new OLE DB Destination as shown below. Next right click on the second OLE DB Destination and specify the destination connection manager and destination table. Please check the "Keep Identity" property for this one as well.

drag another green arrow from the Multicast to the new OLE DB Destination

Once you are done with all these configuration, just hit F5 or click on the Play icon on the IDE, then you can see the execution of the package.

Please note the below image, from the source there are 290 records and both destination tables have 290 records written as well as they should.

click on the Play icon on the IDE

Multicast transformation was introduced in SSIS 2005 and further enhanced and optimized in SSIS 2008 to use multithreading while writing data to multiple destination refer to this article.


Conditional Split Transformation

Conditional Split Transformation is used to categorize the incoming source data into multiple categories as per defined conditions. What I mean here is, very much like the SWITCH CASE statement of any programming language, the conditional split transformation tests each incoming row on those defined conditions and wherever it matches with any of them it directs the row to that destination.

For example, consider you have a customer table, you want data of this table to be categorized on the basis of Age into Juniors, Adults and Seniors, another example, which I will be demonstrating in this tip, is if you have an employee table and you want data to be divided into Male and Female based on the value in the Gender column.

Conditional split also provides a default output in the case that the incoming row does not match any of the defined conditions, the row goes to the default output. This default output is normally used to move erroneous rows to a separate table.

Let's create another package and drag a Data Flow Task. Double click on it to open the Data Flow pane, now drag an OLE DB Source on the working area and configure it like this.

now drag an OLE DB Source on the working area and configure it like this

Then drag a Conditional Split Transformation from the Toolbox, drag the tiny green arrow of the OLE DB Source to the Conditional Split Transformation and configure it like this.

 drag the tiny green arrow of the OLE DB Source to the Conditional Split Transformation and configure it like this

Next drag an OLE DB Destination and then drag the tiny green arrow from the Conditional Split Transformation to the OLE DB Destination. At this point it will ask you the condition to use which you just setup in the previous step. For the first one I selected "Case Female" and then I setup my OLE DB Destination as shown below.  Once you are done with this OLE DB Destination configuration for Female, you need to repeat the same process for another OLE DB Destination configuration for Male.

At this point it will ask you the condition to use which you just setup in the previous step

repeat the same process for another OLE DB Destination configuration

After this has been setup you can execute the package and it should look like the below image. As you can see out of the 290 source records 84 are related to female and moved to the FemaleEmployee table and 206 records are related to male and hence moved to the MaleEmployee table.

you can execute the package and it should look like the below image

So finally, the basic difference between Multicast Transformation and Conditional Split transformation is that Multicast Transformation generates exact copies of the source data, it means each recipient will have same number of records as the source whereas the Conditional Split Transformation divides the source data on the basis of defined conditions and if no rows match with this defined conditions those rows are put on default output.

Once you are done with these practical exercises, you can dropped all the objects which we created for these demonstration with this cleanup code provided below:

-- Script #2 - Cleanup script for work tables we created
USE AdventureWorks 
GO
IF OBJECT_ID('AdventureWorks.HumanResources.Employee1') IS NOT NULL 
   DROP TABLE [HumanResources].[Employee1] 
GO 
IF OBJECT_ID('AdventureWorks.HumanResources.Employee2') IS NOT NULL 
   DROP TABLE [HumanResources].[Employee2] 
GO 
IF OBJECT_ID('AdventureWorks.HumanResources.MaleEmployee') IS NOT NULL 
   DROP TABLE [HumanResources].[MaleEmployee] 
GO 
IF OBJECT_ID('AdventureWorks.HumanResources.FemaleEmployee') IS NOT NULL 
   DROP TABLE [HumanResources].[FemaleEmployee] 
GO  
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Wednesday, February 27, 2019 - 6:53:17 AM - ankush Back To Top (79138)

SELECT col1,sum(col2)OVER (ORDER BY col1) FROM #tab1

 

1 10 10

2 20 30

3 30 60

4 40 100


Wednesday, January 20, 2016 - 5:13:31 AM - Neel Back To Top (40448)

 

Hello Arshad

Thanks for the details and screenshot. Really helped

Neel


Friday, January 9, 2015 - 3:17:28 AM - Amit K Back To Top (35877)

Hi Krishna Pakala,

 

I am not sure that you got the answer of your question till now, but here is the solution for your query:

 

create table #tab1(col1 smallint, col2 smallint)

insert into #tab1 values (1,10)

insert into #tab1 values(2,20)

insert into #tab1 values(3,30)

insert into #tab1 values(4,40)

 

select 

col1,

(

select sum(col2) from #tab1 t2

where t1.col1>=t2.col1

) as col2 

from #tab1 t1

 

I have used here a temporary table, but you can create a normal table for your scenario.

Please let me know if you need more inputs.

 

Thanks!

Amit K

 

Thursday, October 31, 2013 - 8:42:54 AM - krishna pakala Back To Top (27348)

hi sir,

Very nice Artical,

i have falt file like this 

col1,col2

1,10

2,20

3,30

4,40

this file send to this convegation

can you possible this out put like this

col1,col2

1,10

2,30

3,60

4,100

second col2 value adding one row to anther row

please tell me the Answer


Wednesday, February 27, 2013 - 2:14:50 AM - Saim Back To Top (22441)

Excellent Article


Thursday, January 3, 2013 - 5:10:15 AM - sanjana Back To Top (21244)

hi if a flat file is imported,then how to change the date of  it day by day..


Tuesday, May 8, 2012 - 4:50:19 AM - Sanjeev Back To Top (17328)

Hi Arshad,

 

how are you? Hoope you remember me :)

I read some of your articles, they really nice, well written & helpful. I just want to say your works are AWESOME. Want to see more of them.

 

Sanjeev Bollina


Tuesday, May 1, 2012 - 3:40:48 AM - satish Back To Top (17215)

Thank you so much Arshad Ali.


Wednesday, June 30, 2010 - 3:58:20 AM - MAzim Back To Top (5761)

Hi Arshad,

Very nice and precisely written article. Hope to see more on the subjcet

Warm regards,

Muhammad Azim















get free sql tips
agree to terms