Multicast Transformation in SSIS with Example

By
roopesh.valluru
-
72
26455

Friends,

Multicast transformation is useful when ever you wish to make many copies of same data or you need to move the same data to different pipelines. It has one input and many outputs. I will replicate the same input what is takes and returns many copies of the same data. Lets take a simple example to demonstrate the same.

For this I am creating a table and inserting data into it. If you wish to use existing table then you can go ahead with that.

Note – I am creating 5 more tables with the same structure which I will use as Destination tables.

create table emp
(
emp_name varchar(100),
emp_id int,
sal money,
desig varchar(50),
joindate datetime,
enddate datetime
)

Inserting data into the same table.

insert into emp values(‘sita’,123,30000,’software engineer’,12-03-2011)
insert into emp values(‘ramu’,345,60000,’team lead’,15-06-2009)
insert into emp values(‘lakshman’,567,25000,’analyst’,18-02-2011)
insert into emp values(‘charan’,789,40000,’administrator’,27-05-2011)
insert into emp values(‘akhil’,234,30000,’software engineer’,24-07-2011)
insert into emp values(‘kaveri’,456,50000,’hr’,26-12-2009)
insert into emp values(‘nimra’,678,50000,’adminidtrator’,19-06-2010)
insert into emp values(‘lathika’,891,35000,’system analyst’,23-05-2010)
insert into emp values(‘yogi’,423,70000,’tech lead’,12-09-2009)
insert into emp values(‘agastya’,323,70000,’team lead’,23-04-2008)
insert into emp values(‘agastya’,235,50000,’hr’,21-12-2009)

Now the source data is ready on which we can apply Multicast transformation and move to multiple destinations. PFB the steps to be followed.

  • Open a new project and drag a Data Flow task from toolbox in Control Flow.
  • Edit the Data Flow task by double clicking the object or by selecting EDIT button on Right click on the object. Make sure the Data Flow Page is opened as shown below.

 

  • Select OLE DB data source from data flow sources and drag and drop it in the data flow.
  • Double click on the OLE DB data source to open a new window where we can set the properties of the connection.
  • Select the connection manager and click on new button to set the connection string as shown below.

  • Set the connection to the database by providing the Server name,database name and authentication details if required.
  • After the connection is set, select Data Access mode as “Table or View” as shown below and then select the table which we created just now.

  • Drag and drop MultiCast Transformation into the Data Flow Pane and provide connection between OLE DB Source and Multicast Transformation as shown below.

  • Now from Multicast we can take many outputs and to demostrate the same drag and drop OLE DB Destination and give a connection between MultiCast transformation and OLE DB destination then EDIT the destination transformation to provide the connection details to ONE of the FIVE tables created with same structure.
  • Check the mappings and click OK.

  • Like above, we can create N number of destinations and for this demo purpose I created 5 similar destinations pointing to different tables we created as shown below.

  • In the above pic, you can see that Many outputs are coming from one Multicast transformation and the same is pointed to different OLE DB destinations. Now the package is all set to go. Trigger it and wait till all the items turn GREEN.

That is it !! you can see Multicast taking one input of 11 rows and converting it to many copies and sending to different destinations.

This is how you can configure and use Multicast transformation. You can give output of Multicast to another transformation too before passing it to destination.

Happy Coding guys !!

Regards,
Roopesh Babu V

RELATED ARTICLESMORE FROM AUTHOR

72 COMMENTS

  21. viagra and cialis online In the integrated analysis of two Phase III studies by Pryor et al,46 increases from baseline in average IELT at 12 weeks were significantly greater for either dose of dapoxetine 30 mg or 60 mg than for placebo in the subgroups of patients with baseline average IELTs 1 minute and 30 seconds

  22. The disease-oriented efficacy of dapoxetine has been shown in the studies examined here to be supported by positive effects in all patient-reported outcomes, which together indicate a significant improvement in wellbeing and quality of life cialis prices

  42. About 60 percent of men with Crohn s taking sulfasalazine a type of 5- aminosalicylic drug or 5- ASA may have abnormal sperm and infertility. doxycycline penicillin read more, other spotted fevers may occur in the United States and other parts of the world see table Some Rickettsial and Related Infections Some Rickettsial and Related Infections.

  51. Perioperative Morbidity in Cirrhotic Patients Undergoing Orthopedic Surgery Eric M cialis cheapest online prices Manic episodes Some people may have periods of mania while taking WELLBUTRIN XL, including greatly increased energy severe trouble sleeping racing thoughts reckless behavior unusually grand ideas excessive happiness or irritability talking more or faster than usual

  56. So he sent a servant with me, and when I entered the house, I found Umm Ruman downstairs, while Abu Bakr was reciting something upstairs stromectol buy online Furthermore, the patients in our trial had not received adjuvant chemotherapy and thereby the observed effects of the putative biomarkers were not biased by endocrine resistant patients who were cured by adjuvant chemotherapy

  58. Of these 30, five died of causes unrelated to their diagnosed primary cancer with no sign of recurrence three adjuvant; two observed, 16 died within 6 months of being seen and known to have been disease free seven adjuvant; nine observed, and nine died more than 6 months after being seen and were known to have been disease free five adjuvant; four observed stromectol pharmacy During the retention session, although sham operated and MCAO groups did not show any differences when tested 24 hours after training Supplemental Figure 1D; P 0

  62. George Kell s Victory Over Cancer letter, 139, p priligy results com 20 E2 AD 90 20Pharmacie 20En 20Ligne 20Viagra 20 2025 20Mg 20Viagra 20Yorumlar 25 mg viagra yorumlar We ideally would like to see a resolution of this by 1stMarch, 2014

  65. buy clomid Another study, also using the MCF 7Ca model, demonstrated that letrozole potently inhibits mammary tumor growth but does not have the estrogenic effects of tamoxifen, as measured by its uterotrophic effects 100

LEAVE A REPLY

Please enter your comment!
Please enter your name here

85 − = 75