How to Use Multicast in SSIS with Examples

This article explains how to configure the job of multicasting data with the help of SSIS Packages. Also, the article highlights the importance of Multicast Transformation in SSIS for sending data to multiple destinations as per requirement. We are going to explain the benefits of multicasting data, present a detailed multicast tutorial for your reference, and provide some professional tips to assist you in your work.

Before Multicasting Data

This and further materials will examine different aspects of the data multicasting process and SSIS testing. But first, we need to make sure to set up and configure the work environment:

1. An SQL Server instance installed locally or remotely.
2. Visual Studio with SQL Server Integration Services (extension) installed.
3. Tools for writing and executing T-SQL scripts (dbForge Studio for SQL Server or SQL Server Management Studio).
4. Sample databases.
5. The environment for multicasting data setup and tested.

The basic information necessary to understand and implement the upcoming multicasting data process is present in the previous article:

How to Set up and Test-Run the Environment to Multicast Data using SSIS Packages

Multicasting Data using Multicast SSIS Transformation

Our journey starts from sending the source rows to the two different destination databases, DeskTarget1 and DeskTarget2. We already did it at the previous stage of work described in the previous article.

Quick View on the Setup based on the Reference Article

First, let’s have a quick look at the setup environment that we got at the previous stage:

As you remember, there we asked one question in that article. If we add two Multicast Transformations one after another, would that package be successfully executed or not?

For those who have just joined, I’ll describe it briefly:

1. There are three sample databases. One is the source database, and the others are target databases.
2. We have created an Integration Services Project
3. The OLE DB Source component is set to pull data from the DesksSource source database. Find out more about SSIS components.
4. We have added Multicast Transformation to the Data Flow designer surface by connecting it with the OLE DB Source component to pull rows from it.
5. We have successfully tested the Multicast Transformation.
6. Finally, we added one more SSIS Multicast Transformation to check what happens next.

Right-click on Package and run it by clicking Execute Package:

As you see, there is nothing wrong if we connect two Multicast Transformations one after another. The only thing, it is not an efficient approach since one transformation is enough to serve the purpose.

Reset the Package

Remove the second transformation (Multicast1). Right-click on it > Delete:

Now, right-click on the Package to rename it as Multicast package:

Destination Data Check before Multicasting Data

Now, we check the destination tables of both target databases.

Run the following query against the first target database using SQL Server Management Studio or dbForge Studio for SQL Server:

-- Connect to the destination database 1
Use DeskTarget1

-- View DeskTypeTarget1 table
SELECT DT1.DeskTypeTarget1Id
      ,DT1.Name
      ,DT1.Detail FROM dbo.DeskTypeTarget1 DT1

The results are:

Similarly, check the second target database (DeskTarget2) by executing the following script:

-- Connect to the destination database 2
Use DeskTarget2

-- View DeskTypeTarget2 table
SELECT DT2.DeskTypeTarget2Id
      ,DT2.Name
      ,DT2.Detail FROM dbo.DeskTypeTarget2 DT2

The output is:

Both target databases are empty. We expect that our multicasting operation will result in populating tables in each of these databases.

Connect Multicast Transformation with OLE DB Destination (Target Database 1)

Drag the OLE DB Destination component from SSIS Toolbox and drop it onto the Data Flow Task designer surface:

Join the Multicast component with the OLE DB Destination component by the blue arrow coming out of Multicast transformation (also called success precedence constraints):

Now configure OLE DB Destination to point to the table in the target database 1 which is currently empty. Apply the following settings:

Next, check the column mappings. It should match the figure below:

Rename the OLE DB Destination component as OLE DB DST DeskTarget1:

Important Tip about Keep Identity Check box

Remember that selecting the Keep Identity check box when configuring the OLE DB Destination component means we are copying the same primary key ID values as in the destination table. They are in the source even if the target table has its way of generating ID values – the system will ignore it.

Connect Multicast Transformation with OLE DB Destination 2 (Target Database 2)

We are going to add another OLE DB Destination component and then join it with a Multicast transformation:

Right-click on the component and rename it to OLE DB DST DeskTarget2:

Configure the component to point to the second target database table in the same way we did for the first target database:

The mappings must match the following picture:

Now, check the SSIS package which must be ready to multicast the data now.

Save the package and run it to execute the multicasting data task:

Data Check after the Multicasting Package Run

We are going to check the current situation in regards to tables in each of the target databases that were empty before running the multicasting data package. Run the following T-SQL against the target database 1:

-- Connect to the destination database 1
Use DeskTarget1

-- View DeskTypeTarget1 table
SELECT DT1.DeskTypeTarget1Id
      ,DT1.Name
      ,DT1.Detail FROM dbo.DeskTypeTarget1 DT1

The output is:

Now, run the script against the second target database to check its results:

-- Connect to the destination database 2
Use DeskTarget2

-- View DeskTypeTarget2 table after multicasting package run
SELECT DT2.DeskTypeTarget2Id
      ,DT2.Name
      ,DT2.Detail FROM dbo.DeskTypeTarget2 DT2

The output is:

The multicasting data package has run successfully. We have verified the results of deploying SSIS packages data in the target databases.

Rerun the Multicasting Package

If we rerun this package, it is going to fail:

It fails because the 3 rows in the source table have already been inserted into the target tables. Therefore, when we tried to insert the same 3 rows again, the system rejected this attempt. The ID columns include primary keys, and it does not allow duplicate rows.

Reset the Target Tables and Rerun the Multicasting Package

A good approach to tackle the problem mentioned above is to reset the target tables. We empty them before running the multicasting package each time. One of the methods is to introduce an SQL Task before the data flow task to delete the target table before each new run.

Add the Reset SQL Task before the Multicasting Data Flow Task

Switch to Control Flow and add an Execute SQL Task from SSIS Toolbox as follows:

Rename it to SQL Reset Target Table 1 and configure the task in the following way:

Put the SQL Statement into the task:

Truncate TABLE dbo.DeskTypeTarget1

Next, add another SQL Task to reset the target table 2 as follows:

The Control Flow area should look like below:

Rerun the Package after resetting the tasks

Execute the package again:

Congratulations! You have successfully created an SSIS Package to multicast data from the source database to multiple destination databases. The SSIS multicast transformation: sending data to Excel is one of the suitable options, but you can use other destinations too. Besides, it is possible to split data from the source database to deliver specific portions to different target databases, the so-called conditional split SSIS. But we’ll focus on it later. Now, we’ve mastered rerunning the package as many times as needed.

Important Tips about SSIS Packages

In a professional environment, we must design SSIS Packages in a re-runnable way. It does not matter how many times they execute – they must be robust enough to achieve the desired task without terminating abnormally.

Things to do

We have learned to write an SSIS Package to multicast data to multiple destinations. This skill will be helpful for the conditional split transformation in SSIS that we are going to examine further. Now, you can use some exercises to train your new skills:

1. Remove one row from the source table and rerun the multicasting package to see the results
2. Add one more target database and a reset SQL task. Give the package a go to see if you can successfully transfer the same data to three different destinations
3. Try multicasting data for another CarPlus sample database (refer to the article Learn to Perform Product Analysis using SQL Server Full-Text Search. Part 1).