Hello friends, today will learn about Multicast task in SSIS. Multicast task is used for making multiple copy of same data or dataset and pass those datasets in different pipelines. It has one input and multiple outputs with same data. Lets take an example of multicast task and understand how it works.
First of all we will create a table “StudentDetail” and insert few rows into it using below SQL script.
Create table StudentDetail(Name Varchar(100),Subject Varchar(50),Age int)
Insert into StudentDetail values(‘Harshit’,’English’,20)
Insert into StudentDetail values(‘Amit’,’Hindi’,22)
Insert into StudentDetail values(‘Vikas’,’Maths’,19)
Insert into StudentDetail values(‘Rohit’,’biology’,23)
Insert into StudentDetail values(‘Manish’,’Chemistry’,21)
Now we will take a “Data flow” task in “Control flow” region as shown below.
We will double click on the “Data flow” task that will take us into the “Data flow” region. In “Data flow” region, we will take a “OLEDB Source” and select “StudentDetail” table as source table(As shown below).
We will click on “OK” button to save the settings and take a “Multicast” task and connect it with the “OLEDB Source” as shown below.
Now we will create three more tables which will have same schema as “StudentDetail” table has using below SQL script.
Create table StudentDetail_Copy1(Name Varchar(100),Subject Varchar(50),Age int)
Create table StudentDetail_Copy2(Name Varchar(100),Subject Varchar(50),Age int)
Create table StudentDetail_Copy3(Name Varchar(100),Subject Varchar(50),Age int)
Lets take three “OLEDB Destination” tasks and connect each task with “Multicast” task as shown below.
Now we will configure each “OLEDB Destination” task one by one, we will right click on the first “OLEDB destination” task(From the left) and click on “Edit” menu that will bring a new screen(OLEDB destination editor) in front of us(As shown below).
As you can see in the above screenshot that I have selected “StudentDetail_Copy1” table as destination table, similarly we will select “StudentDetail_Copy2” and “StudentDetail_Copy3” table respectively for next two “OLEDB destination” tasks and click on the “OK” button to save the “OLEDB Destination” tasks configurations.
Now we are done with our package, lets execute it by running “F5” key and see the results.
Green color of all the tasks indicates that our package ran successfully, lets check all three destination table to see if all the tables have same data or not.
We can see in the above screenshot that all the destination tables have same data. We can use Multicast task to replicate the same data and pass it to different destination tables. I hope you will find this article useful.
879 total views, 1 views today