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.