SSIS - Load Data To Multiple Destinations From Single Source [ How To Use Multicast Transformation]

Scenario:

Sometime we have requirement to read the data from single source and then load to multiple destinations. Let’s say we are getting a flat file as source and we have to load this file into one of the SQL Server table as well load into Oracle Database Table.

Solution:

We can use Multicast transformation in SSIS. Multicast transformation takes one input and creates multiple outputs. Each of the output is replica of input.

Step 1:
Create Flat File connection to your source file. In this example I am using below data in text file.
CountryName,SalePersonName,SaleAmount
uSA,aamir shahzad,100
Italy,andy,300
UsA,Mike,500
brazil,Sara,1000
INdia,Neha,200
Brazil,Anna,200
Mexico,Anthony,500
Mexico,Anthony,test500

Step 2:

In data flow task, use the flat file source and then connect flat file source with multicast. we need to load two destination tables ( SQL Server and Oracle). I have used two OLE DB Destinations and configured them. You can use destinations according to your requirements. Connect The output of Multicast to SQL Server Table and then connect other output to second Destination as shown in below snapshot.

Final Output:
As we can see in the final output snapshot, there are 8 rows from source and then two outputs are created by using Multicast Transformation. Each output data is replica of input data.


5 comments:

  1. I think SSIS PostgreSQL and its functions are always useful whenever there is a need to look for some very good problem solving strategies.

    SSIS PostgreSql Write

    ReplyDelete
  2. i really like this article please keep it up. simplybuzzes

    ReplyDelete
  3. When I needed to transfer data to Oracle database, I used devart odbc driver for oracle. So you can try it for your task.

    ReplyDelete
  4. Tamil Nadu Directorate of Government Examinations Every Year Publish and Distribution in 6th, 7th, 8th, 9th, 10th Class, TN Board Every Year Academic Year Close Month of April, Every Year Academic Year Open Month of Jun, Every Year Tamil Nadu State Wise 6th, 7th, 8th, 9th, 10th Class Students More then 50 Laks of Students Chang in Various Primary School Class, TN 10th Class Textbook 2023 Here we are Providing the Facility to Download Latest Edition Tamil Nadu 6th, 7th, 8th, 9th, 10th Class Textbook 2023, Students You can Download for Hindi, English and Urdu Medium Schools. TN DGE high School Examination Board Government agency has Developed the new Textbooks at the High School Standard in Prepare by Senior experts.

    ReplyDelete