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.
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:
Rename the connection to ToysStoreDW – Source:
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.
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:
Create the Connection for the Excel File to Hold Reporting Data (Destination 1)
This time, create a new connection as shown below:
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:
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:
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:
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:
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
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()
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]
Name this transformation DRV SQLToyIdWithName:
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:
Now, add the following components from the SSIS Toolbox and join our Multicast Transformation with both of them:
- Excel Destination (to load data into the Excel file).
- OLE DB Destination (to load data into SQL Database Log).
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:
Next, double-click the OLE DB Destination component. Select the required destination and table:
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):
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:
Check Both Destinations
Go to the location where the Excel file is stored:
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
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:
- Rerun the SSIS Package and see results for yourself, as you are going to observe two different instances of the top toys.
- 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.
- Try adding and testing another SQL view to get the Top 5 least-selling toys.
- Create another environment to send the report data to multiple destinations based on the sample source database CoalesceUseDB mentioned in this article
- How to Build a Simple Data Warehouse in Azure – Part 1 - September 2, 2021
- SQL Server Business Intelligence – Modern Tools and Technologies - August 9, 2021
- Learn to Use Time Intelligence in Power BI - June 4, 2021