Rajendra Gupta
Add an Excel file output in the SSIS Multicast Transformation

SSIS Multicast Transformation overview

August 22, 2019 by

This article explores the SSIS Multicast Transformation for creating different logical copies of source data.

Introduction

In the article, SSIS Conditional Split Transformation overview, we explored the Conditional Split Transformation task to split the incoming data into multiple destinations depending upon the specified condition. We use SSIS Multicast Transformation to create multiple copies of input data. Suppose we require multiple copies of the input data in TXT, CSV and SQL table format. We can use these multiple copies of the data for different transformations.

Suppose you receive stock transactions for your clients in a CSV format. You upload the data in the database and do the calculations for your clients depending upon multiple scenarios. You can use SSIS Multicast Transformation to prepare similar copies of data using SSIS. We do not need any programming or development experience to do this task.

Let’s create an SSIS package to use the SSIS Multicast Transformation.

Environment Details

In the following image, you can see the configured SQL Server Data Tools for my system.

Visual Studio version and edition

Create an SSIS package for SSIS Multicast Transformation

Launch SQL Server Data Tool to create an Integration project. Provide the Integration project name and specify the location to save the project.

Create a SSIS package

Add an OLE DB Source in the Connection Manager

In the SSIS project window, we have the following areas.

  1. Control Flow, Data Flow, Event Handlers and Package Explorer area
  2. We add different SSIS components in this area

    Add an OLE DB Source in the Connection Manager

  3. In the Connection Managers area, we can configure the different source and destination connections. You can also view all the connections in the package in this area

Let’s add an OLE DB Source connection to point the WideWorldImporters database in my local instance. Right-click on the Connection Managers, click on New OLE DB Connection.

New OLE DB Connection

It opens the OLE DB Connection Manager. Provide the details such as SQL Instance name, Authentication method (Windows or SQL authentication) and select the database name from the drop-down list.

SQL Connection Manager

You can test the connection and click Ok. You can see the configured connection in the Connection Managers area.

Available connection in the connection manager

For a better understanding, right-click on this connection and rename it with an appropriate name.

Rename a connection

Drag the Data Flow Task in the Control Flow area.

Data Flow Task in the Control Flow area

Right-click on the Data Flow Task and click on Edit.

Edit Data Flow Task

It moves to the data Flow page. In the Data Flow page, add an OLE DB Source similar to the following image.

Add OLE DB Source

Double click on this to open the OLE DB Source editor. It automatically shows the connection we created earlier. If we want a separate connection, you can click on New and configure it. In the Data access mode, select the Table or View because my source data is in the SQL table.

Select the table that contains the data. I selected Sales.Orders table from the WideWorldImporters database.

Select the table or view

Click Ok and rename the OLE DB Source to Source Data.

Rename the OLE DB Source to Source Data

Add SSIS Multicast Transformation to configure multiple destinations

Drag the Multicast transformation to the data flow tab and join the green line from Source Data to Multicast.

Add SSIS Multicast Transformation to configure multiple destinations

Now we want to add three destinations.

  • Flat File output
  • Excel file output
  • SQL Database table

Add a FLAT file output in the SSIS Multicast Transformation

We use Flat File Destination for the TXT or CSV file formats. Drag the Flat File Destination from the SSIS Toolbox to Data Flow task and join it with the Multicast operator.

Add a FLAT file output in the SSIS Multicast Transformation

It shows the Red Cross icon on Flat File Destination because we did not configure the destination. Double click on it, and it launches the Flat File Destination Editor.

New Flat File

Click on New, and it asks for the flat-file format. We can choose the formats below.

  • Delimited
  • Fixed Width
  • Fix Width with row delimiters
  • Ragged Right

Let’s go with the default format Delimited. In this format, the commas delimit the columns in the text file.

Flat File Format as Delimited

Click Ok, and it opens the Flat File Destination editor. Specify the connection name and the file path. It automatically gives the format specifications such as Header Row delimiter, Text qualifier. We will go with the default values.

In the Advanced page, we view the source columns and their properties for the destination flat file.

Flat File Configuration

Click on Mapping to view the mapping of input and output columns. By default, it does the mapping; however, we can change it if required.

Mapping between source and destination

Click Ok, and we can see the Red Cross icon is not present now on Flat File Destination.

SSIS package configuration

Add an Excel file output in the SSIS Multicast Transformation

Let’s add an Excel file destination from the SSIS toolbox and join it with the Multicast Transformation.

Add an Excel file output in the SSIS Multicast Transformation

Double click on Excel destination to configure it. In the Excel Destination Editor, click on New and specify the file path and name.

Excel File path and configuration

Click Ok, and it goes to excel destination editor. Click on New to create the required columns in the excel sheet.

Create Excel sheet

Click Ok, and you get the syntax error message.

Syntax error

Remove the quotes, and you can create the excel sheet with the required columns.

Remove the quotes in the excel sheet

Click OK and go to Mapping to review mapping between source data and destination excel sheet.

Review mapping

Click Ok, and we are ready with the Excel destination configuration.

Excel destination configuration

Add an SQL Server Destination

We require a SQL Server destination to get a copy of the source data. We can use OLE DB destination or SQL Server destination for this.

Drag an OLE DB destination and join with SSIS Multicast Transformation.

Add SQL Server Destination

Double click on this SQL Server Destination to configure it. It opens the SQL Destination Editor, and we can see the existing connection WideWorldImporters in this. Let’s use the same connection for the destination as well.

SQL Destination Editor

We want to create a new table, therefore, click on the New in front of Use a table or view. It opens the script for the CREATE TABLE statement. Give an appropriate name for the table and click Ok.

Create a new table in the SQL Destination editor

You can see the table name in the screenshot below.

Create a new table or view

Verify the mapping between the source data and destination table and click OK.

Mapping between source and destination

Click Ok, and it returns to the Control flow tab of the SSIS package. We configured three destinations as per our requirement.

Complete package configuration

Press F5 or click on the Start button to execute the package. The green tick mark on each component shows the package executed successfully.

In the following screenshot, we can verify that SSIS Multicast Transformation sends the same copy of data to each destination. We have source row count 1,111 and each destination row count is 1,111.

Result of package execution

Let’s verify data in the destination files.

  • Flat File Data

    Flat File Data

  • Excel Sheet Data

    Excel Sheet Data

  • SQL Server Data

    SQL Server Data

Derived column and SSIS Multicast Transformation

In the previous example, we use SSIS Multicast Transformation to copy the data from the source to the destination such as flat files, excel sheet and SQL tables. The use case of a multicast operator is that we can apply different transformations, aggregations, calculations on logical copies of data.

Suppose we want a new derived column in the SQL Server destination. We do not want derived columns in other destinations.

Drag a derived column from the SSIS Toolbox.

Derived column

We want to add a new column LoginID, and it should be the reverse of the PreferredName column. In the expression, we use the SQL REVERSE string function.

Add a new Derived column

In the SQL Server Destination, create a new table that contains new column Loginname. Drag the green arrow from the derived column to the SQL Server destination.

SSIS package with Multicast and Derived column

Execute the package, and it shows successful.

package execution

Let’s check the SQL Server Destination table and see the data in the new column. You can see that the LoginID column contains data reverse to data in the PreferredName column.

Verfiy the result set

Conclusion

In this article, we explored the SSIS Multicast Transformation to copy the source data into multiple destinations such as flat files, excel files and the SQL Server tables. Try exploring this SSIS component to perform tasks efficiently without any complicated programming code.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
619 Views