Learn to Multicast Data Using SSIS Packages

Total: 1 Average: 5

This article describes configuring the job of multicasting data with the help of SSIS (SQL Server Integration Services) Packages. Also, it highlights the importance of Multicast Transformation in SSIS for sending data to multiple destinations as per requirement.

Additionally, we are going to explain the benefits of multicasting data and provide some professional tips.

CodingSight - Learn to Multicast Data Using SSIS Packages

Before Multicasting Data

General Requirements

As a general rule of thumb, we need the following requirements met:

  1. An SQL Server instance installed locally or remotely.
  2. Visual Studio with SQL Server Integration Services (extension) installed.
  3. A big plus is having dbForge Studio for SQL Server or SQL Server Management Studio installed, as we need to write and execute T-SQL scripts for quick checks.
  4. Sample databases created and configured.
  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

This reference covers the following aspects:

  • Basic concepts of multicasting data;
  • Setting up sample databases (DesksSource, DeskTarget1, DeskTarget2);
  • Setting up an environment to start using Multicast SSIS Transformation;
  • Testing the setup environment.

Multicasting Data using Multicast SSIS Transformation

Our journey to multicast data starts from sending the source rows to the two different destination databases, DeskTarget1 and DeskTarget2.

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:

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.
  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:

Package ran successfully with two Multicast Transformations

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:

Removing second Multicast Transformation

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

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:

The table in the first target database is empty

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:

Second target database is also empty before multicasting data via SSIS Package

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 on to the Data Flow Task designer surface:

Drag the OLE DB Destination component from SSIS Toolbox and drop it on to 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):

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:

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:

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

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

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:

Adding second 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:

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:

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:

The mappings must match the following picture

Now, check the SSIS package which must be ready to multicast the data 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:

Multicast package successful run

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:

Target database 1 (DeskTarget1)

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:

Target database 2 (DeskTarget2)

The multicasting data package has run successfully. We have verified the data in the target databases.

Rerun the Multicasting Package

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

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:

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:

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:

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

The Control Flow area should look like below:

Run Multicasting Task

Rerun the Package after resetting the tasks

Execute the package again:

Multicasting data package rerun successfully after reset SQL tasks have been introduced

Congratulations! You have successfully created an SSIS Package to multicast data from the source database to multiple destination databases. Also, 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

Now that you can write an SSIS Package to multicast data to multiple destinations, improve your skills further:

  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).
Haroon Ashraf

Haroon Ashraf

Haroon's deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional. He began his professional life as a computer programmer more than 10 years ago working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data. His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).