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

This article is the second part of the material dedicated to SSIS Multicast Transformation used to send the report data to multiple destinations.

In the previous section, we set up the environment for doing that task – refer to it to refresh your knowledge and recall the examples. In this section, we are going to use that data to design the Multicast Transformation solution and test-run it.

So, get back to the previously created project. We are starting the next stage of our work.

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

Create the Source Connection (ToysStoreDW)

The first thing to do is to connect to the source ToysStoreDW database.

Open the Integration Services Project in Visual Studio (SQL Server Data Tools) and double-click the multicast excel SQL package to open it.

Once the Control Flow/Data Flow tab is active, right-click anywhere in the Connection Managers area and click New OLE DB Connection… Then configure it to point to the ToysStoreDW database:

Create the Source Connection

Rename the connection to ToysStoreDW – Source:

Rename the connection to ToysStoreDW

Create the SQL Database Log Connection (Destination 2)

Create another connection pointing to the log database (ToysStoreDWLog). It helps us to track the report data.

Create another connection pointing to the log database (ToysStoreDWLog)

Note: I am using a slightly different provider this time, while the database is hosted locally. So, it is fine to use the Native OLE DB provider or the one used previously.

Test the connection and rename it as ToysStoreDWLog – Destination 2:

Test the connection and rename it as ToysStoreDWLog - Destination 2

Create the Connection for the Excel File to Hold Reporting Data (Destination 1)

This time, create a new connection as shown below:

Creating Excel Connection

Select EXCEL from the list that appears once you click New Connection:

Select EXCEL from the list that appears once you click New Connection

Locate the Excel file we created at the previous stage. This file should contain the desired table structure sheet:

Pointing  to the blank Excel file

Rename it as Report Data in Excel – Destination 1:

Rename it as Report Data in Excel – Destination 1

Creating the Data Flow Task to Add the Required Functionality

We need to create the data flow task in the Control Flow tab to start designing the package according to the requirements.

Drag a Data Flow Task from the SSIS Toolbox and drop it onto the surface of Control Flow. Name it DFT Toys Report Data:

Creating the Data Flow Task to add the required functionality

Remember to keep saving the project! You have to ensure that you won’t lose any progress if your Visual Studio becomes unresponsive for any reason.

Double-click the Data Flow Task (DFT Toys Report Data) to switch to the Data Flow tab.

Bring the OLE DB Source component to the Data Flow designer surface:

Bring the OLE DB Source component to the Data Flow designer surface

Now, double-click the component and select the ToysStoreDW source database and the Top5ToysView view in the Name of the table or the view drop-down box:

select the ToysStoreDW source database

Rename the component as SRC Top5ToysView:

Rename the component as SRC Top5ToysView

Drag and drop the Derived Column Transformation and insert the following new derived columns values as follows:

Column Name 		Expression 	 						Length
ExcelName   		Name				     		   		50
StringToyId 		Name + " (Toy Id: " + (DT_STR,5,1252)ToyId + ")" 		71
Drag and drop the Derived Column Transformation and insert the following new derived columns values

Note: We have added the two new derived columns because the Name column in the source table is VARCHAR(), while Excel accepts names like columns in Unicode (NVARCHAR). That’s why we have created the ExcelName derived column to avoid the problem of mismatching data types when transferring data.

The second derived column StringToyId is joining the ToyId with the Name columns, creating a useful piece of information for the log file. The data will be sent to that file as a result of Multicast Transformation.

Create a package-level variable called LogDateTime of the DateTime type. It is going to store the date of the particular reporting data stream. As bestselling toys may change frequently, even on daily basis, this variable helps us to see what was the list like on a particular date.

Name: LogDateTime
Date type: DateTime
Expression: GETDATE()
Create a package-level variable called LogDateTime of the DateTime type.

However, we need another derived column to turn the Unicode (NVARCHAR) StringToyId (derived column) into the simple string type VARCHAR. It is necessary for sending the data to the SQL Log database because the recipient column in the target database is in VARCHAR (not in Unicode).

We also need to add the LogDateTime derived column to use it for SQL Log Database.

Add another Derived Column Transformation to convert StringToyId derived column into SQLStringToyId. Add the derived LogDateTime column to make it compatible with the SQL log database as follows:

Derived Column Name:
SQLStringToyId
Expression:
(DT,STR,70,1252)StringToyId

Derived Column Name:
LogDateTime
Expression:
@[User::LogDateTime]
Add another Derived Column Transformation to convert StringToyId derived column into SQLStringToyId.

Name this transformation DRV SQLToyIdWithName:

Two Derived Column Transformation

Remember that we could have used Data Conversion Transformation. However, Derived Column Transformation serves the purpose as well.

Add the Multicast Transformation, name it Multicast Excel and SQL Log, and join the last Derived Column Transformation with it:

Adding Multicast Transformation to send data to two destinations

Now, add the following components from the SSIS Toolbox and join our Multicast Transformation with both of them:

  1. Excel Destination (to load data into the Excel file).
  2. OLE DB Destination (to load data into SQL Database Log).
adding components from the SSIS Toolbox and joining Multicast Transformation with both of them

Double-click Excel Destination and point to the desired Excel sheet:

Double-click Excel Destination and point to the desired Excel sheet

Switch to the Mappings section and map StringToyId from the Input Columns with the Name column in the Destinations. The rest of the mappings should be untouched as shown below:

Mapping StringToyId with Name column in Excel file

Next, double-click the OLE DB Destination component. Select the required destination and table:

double-click the OLE DB Destination component

Map the columns so that LogDateTime (derived column) should be mapped with the LogDateTime SQL database table column, and SQLStringToyId (derived column) should be mapped with the ToyWithId column of the SQL log database (ToysStoreDWLog) table (ReportLog):

Configure the properties used to insert data into a relational database using an OLE DB Provider

Rename Excel Destination to DST Excel Report, and OLE DB Destination to DST SQL Log.

Run the SSIS Multicast Transformation Package

Let us run the package to see it in action:

Run the Multicast Transformation Package

Check Both Destinations

Go to the location where the Excel file is stored:

Go to the location where the Excel file is stored

Open the Excel file and see the results:

Open the Excel file and see the results

Now run the following T-SQL script against the ToysStoreDWLog database to view the contents of the table after we ran the Multicast Transformation:

-- Connect to the Log database
USE ToysStoreDWLog

-- Check ReportData table after multicasting data package run 
SELECT * FROM dbo.ReportLog
order BY LogId
SQL Database Log was successfully populated

Congratulations! You have mastered using SSIS Multicast Transformation to send the report data to Excel and log database. Now you can keep track of what is happening with the report data.

Things to do

Try the following things to improve your skills further:

  1. Rerun the SSIS Package and see results for yourself, as you are going to observe two different instances of the top toys.
  2. Update the Toy table in the source database (ToysStoreDW) by making Colouring Pencils, Castle Playset,and Activity Books top 3 bestselling toys (increase their respective Sold column value). Then rerun the package to see the updated results.
  3. Try adding and testing another SQL view to get the Top 5 least-selling toys.
  4. Create another environment to send the report data to multiple destinations based on the sample source database CoalesceUseDB mentioned in this article

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 *