Multicast Transformation in SSIS: Simplify Export of Data to Excel and SQL Server Simultaneously (with Example)

In professional work scenarios, we often need to load data from one report to several destinations, such as Excel files and SQL databases. One of the ways to accomplish this is using an Multicast Transformation in SSIS which accepts the data input and splits it into several outputs, creating logical data copies. As a result, you get several result copies to apply them to different purposes.

The current article clarifies the steps of gathering information, identifying problems, and designing the solution initial phase. Note that the material is meant for users with some backgrounds. It is desirable to be familiar with the SQL Server work, T-SQL scripting, and dedicated tools. You may refer to the following articles for details:

CodingSight - Sending Report Data to Excel and SQL Database using Multicast Transformation in SSIS Packages

The Case Scenario

There is a company where the internal staff and top management view and track the reported data in the form of Excel files. These files must be updated daily from the reporting databases. The SQL database must log every single update of an Excel sheet by the system. The Excel report files, in their turn, must reside in the common Windows folder accessed by the authorized staff.

Thus, our case suggests that there is one input – the data from the reporting database. But there must be two outputs, the Excel files, and the SQL Database Log updates. The solution will be designed by the Multicast Transformation in an SSIS Package.

Sending Report Data to Excel and SQL Database using Multicast Transformation

The tasks to perform in this scenario are the following:

  • Set up the reporting database.
  • Set up the SQL Log database.
  • Set up an Integration Services Project.
  • Allocate the common Windows folder for the Excel spreadsheets that get updated with time.
  • Get the Excel file with the required reporting columns.

Set up the Reporting Database (ToyStoreDW)

In the ToyStoreDW reporting database, we have a single table called Toy with the following information:

  • ToyId – the particular toy;
  • Name – the name of the toy;
  • Price – the cost of the toy;
  • Stock – the number of toys in the stock;
  • Sold – the total number of orders placed by customers for a particular toy.

Connect to your SQL Server instance and set up the sample database by executing the following T-SQL script against the master database:

-- Create sample database ToysStoreDW
USE MASTER
GO

CREATE DATABASE ToysStoreDW
GO

USE ToysStoreDW

-- Creating a table Toy
CREATE TABLE dbo.Toy
(
 ToyId INT IDENTITY(1,1),
 Name VARCHAR(50) NOT NULL ,
  Price DECIMAL(5,2) NOT NULL,
  Stock INT NOT NULL,
  Sold INT NOT NULL
	CONSTRAINT PK_Toy_ToyId PRIMARY KEY (ToyId)
)
GO

-- Populating table Toy
INSERT INTO dbo.Toy 
(Name, Price, Stock, Sold)
  VALUES 
  ('Colouring Pencils', 5.00, 160, 50),
  ('Activity Books', 10.00, 200, 70),
  ('Programmable Toy Car', 100.00, 30, 40),
  ('Building Blocks', 10.00, 100, 80),
  ('Theme Park Playset', 65.00, 120, 30),
  ('Build a Robot Kit',50.00,200,60),
  ('Flying Toy Car',75.00,50,10),
  ('Science Experiments Set',35.00,100,20),
  ('Indoor Sports Set',20.00,50,25),
  ('Castle Playset',25.00,100,47)

In our case scenario, we need to identify the top 5 bestselling toys. Since this is a reporting database (not a traditional transactional database), the best way is to write the SQL view giving us the required information.

Run the following script against the sample database ToysStoreDW:

-- Creating a SQL view to see top 5 best sellers (toys)
CREATE VIEW Top5ToysView
AS
SELECT TOP 5 * FROM dbo.Toy T
ORDER BY T.Sold DESC;
GO

Now, we need to check it. Run this newly created view against the sample database to see the results:

-- Connect to the reporting database
USE ToysStoreDW

-- View top 5 bestselling toys
SELECT * FROM dbo.Top5ToysView
Sample Database with Top 5 Bestselling Toys

Note: I am using dbForge Studio for SQL Server to write and run scripts. Your output may look different if you work with another tool, such as SQL Server Management Studio. However, the result set will be the same.

Set up the SQL Log Database (ToysStoreDWLog)

This SQL database is going to log the reporting data output results sent to Excel files via SSIS Package. Therefore, we can keep track of those files.

Execute the following T-SQL script:

-- Create sample database ToysStoreDWLog
USE master
GO

CREATE DATABASE ToysStoreDWLog
GO

USE ToysStoreDWLog

-- Creating a log table
CREATE TABLE ReportLog (
  LogId INT IDENTITY (1, 1)
 ,LogDateTime DATETIME2
 ,ToyWithId VARCHAR(150)
  CONSTRAINT PK_LogId PRIMARY KEY (LogId)
)
GO

Now we must ensure that the reporting data is logged correctly once it is sent to the Excel destination. In our example, we focus on the following data sent to Excel:

  1. The date of sending it to the Excel output.
  2. The Toy Id with Name, starting from the Top 1.

Write and execute the following script against the log database (ToyStoreDWLog):

-- Check the strategy to log data

-- Connect ToysStoreDWLog
USE ToysStoreDWLog

DECLARE @LogDate DATETIME = GETDATE() -- Initialise the variable with current date and time 
DECLARE @ToyWithId VARCHAR(150) = '6 Build a Robot Kit' -- Initialise the variable with the top-selling toys information coming from the source


SELECT
  @LogDate AS LogDate -- view the current date as LogDate

-- Insert a record into the Log table as if it was populated through the package 
INSERT INTO dbo.ReportLog (LogDateTime, ToyWithId)
  VALUES (@LogDate, @ToyWithId)

-- View the logged data
SELECT
  RL.LogId
 ,RL.LogDateTime
 ,RL.ToyWithId
FROM dbo.ReportLog RL

The outputs are:

Storing Current Date and Time
Logged Data Check Based on Current Date and Time

Set up an Integration Services Project

Open Visual Studio and create a new Integration Services Project MulticastingExcelSQLSSIS under a new solution called Multicasting Scenario Solution:

Set up an Integration Services Project to Multicast Data

Right-click on the package (Package.dtsx) under the SSIS Packages folder and rename it to multicast excel SQL:

Package Renamed

Allocate the Common Windows Folder

Choose the common windows folder carefully, as it has to be accessible by all authorized staff members.

Common Reports Folder to host Excel File that Contains Reporting Data

Get the Excel File with Required Reporting Columns

Create a blank Excel file and a table to hold the reporting data that must be readily available for the SSIS Package.

Create a new Excel file and name it Toys Report Data:

Creating a blank Excel file to load the reporting data from the SSIS Package Multicast Transformation.

Create a table with the following columns: ToyId, Name, Price, Stock, and Sold.

Table to Hold Top Selling Toys Report Data

Congratulations! We have set the scenario up for multicasting the report data to Excel and SQL database destinations.

Things to Do

Let’s improve your skills more with the following exercises:

  1. Add and test another SQL view to get the Top 5 least-selling toys.
  2. Create a similar scenario and setup based on a sample database SQLDevBlogV7 (refer to this article). The task is to create a view to getting data for any 5 articles related to the development category.
  3. Create another environment to send report data to multiple destinations based on the CoalesceUseDB sample source database (refer to A Practical Use of the SQL COALESCE Function – {coding}Sight).
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).

Leave a Reply

Your email address will not be published. Required fields are marked *