Updated March 14, 2023
What is SSIS Multicast?
SSIS’s Multicast Transformation distributes data to numerous target paths without even any precondition or alterations. In other words, it takes ONE input and creates a conceptual COPY of the data before passing it to many outputs. Multicast transformation makes it possible to have several logical copies of input data from a single data source, either for applying different transformation logic to every set or even for having each dataset for various consumers. Multicast Transformation is a synchronous transformation, which means that the information is not blocked before being sent to the destinations.
How to use SSIS multicast?
Every row is directed to each output using the Multicast transformation. Because Multicast Transformation creates perfect copies of the source data, each receiver will receive the same number of records as the source. Let’s say we need several copies of the input data in TXT, CSV, and SQL table formats. These multiple copies of data can be used for many different transformations. When we wish to apply many transformations to almost the same dataset at the same time, SSIS Multicast Transformation comes in handy. If we’re going to apply two sorts of modifications on the same dataset, for example. Then, using SSIS Multicast Transformation, duplicate the data twice. After that, we can apply various changes to each duplicate.
Requirements to work with:
The SQL Server Integration Services extension is installed in Visual Studio. SQL Server is installed, either locally or remotely.
Create SSIS multicast
For the same data, multicast transformation creates numerous pipelines.
To examine and change the properties for each transformation output, open the Multicast Transformation Editor dialogue box.
This package makes use of the following SSIS features:
1. Complete the SQL task
2. OLE DB Connection Manager (OLE DB Connection Manager)
3. Wizard for configuring SSIS packages
SSIS Multicast Transformation: Create an SSIS package:
To build an Integration project, open SQL Server Data Tool. Provide the name of the Integration project and the place where it should be saved.
This opens the OLE DB manager. To open the OLE DB Source Editor, double-click upon that. It instantly displays the link we established earlier. We can create a new connection by clicking New and configuring it. Because the source data is in a SQL table, choose Table or View in the Data Access option. Choose the table that contains the information.
In the Connection Manager, create an OLE DB source:
The following areas are available in the SSIS project window:
Control Flow, Data Flow, Event Handlers, and Package Explorer are all areas in which one can work. In this section, we could add several SSIS components.
We may configure the various source and destination connections in the Connection Managers area. Users can also examine all of the package’s connections in this area. To point the database in the local instance, add an OLE DB Source connection. Right-click the Connection Managers and select New OLE DB Connection from the context menu. And the OLE DB Connection Manager appears. Enter information like as the name of the SQL Instance, the authentication type (Windows or SQL authentication), and the database name from the drop-down selection.
Establish several recipients using SSIS Multicast Transformation:
Connect the green line between Source Data to Multicast by dragging the Multicast transformation to the data flow tab. Finally, add the destination files. Here I have used three destination files under the multicast. Save it and run the packages to observe how a data source divides the data stream into separate tributaries for other Tasks using Multicast, and the data in each tributary is identical; only one difference is the actions of the multiple Different Tasks. The two branch data streams are also implemented in parallel, which considerably improves efficiency.
SSIS multicast Examples
In this scenario, I have one parent table (patient table), and I need to distribute the data from this table to three separate targets (Flat file, Excel file, and OLEDB destinations).
Step-1: Drag and drop Data Flow Task into Control Flow. Drag and drop the appropriate source (OLEDB Source) into the Data Flow and configure it. Followed by the connection manager name and the respective source table.
Creating a Flat file connection to the source file which is shown above. In this case, I have used the below data in a text file.
The table is: cust table
| Area Name | Person Name | Amount |
| LA | Antony | 200 |
| Brazil | Sara dhae | 300 |
| Texas | Nancy kaeth | 800 |
| Toronto | Thomas | 400 |
| Bolivia | watson | 500 |
Initially, we have data in the cust table but the destination files are empty.
Select *from cust
Select * from dest1
Select * from dest1
This query displays the output of the cust table the other two files are empty.
Connect the source and Multicast Transformation by dragging and dropping Multicast Transformation.
Step-2:
We can get a lot of outputs through Multicast. All targets (Flat File, Excel, and OLEDB Destinations) should be connected to Multicast and configured. Users can see information statistics to the AGGREGATE component via Multicast in parallel while extracting data from the source to the dest1 table and saving to the dest2table if we save the package and conduct this Data Flow Task.
Step-3:
Run the respective package. There seem to be 5 rows from the source in the final output snapshot, and then two outputs are made using Multicast Transformation. Thus, each output dataset is a copy of the input dataset. In an Excel sheet, look at the results of the SSIS Multicast Transformation.
The Multicast transformation was among the most perplexing processes for newcomers to SSIS, not because it has so many options, but because it practically has none! Developers can use transformations to copy a single path and make multiple copies of the data in the pipeline if they need to.
Conclusion
Finally, this article explains how to use SSIS (SQL Server Integration Services) Packages to configure the job of multicasting data. In addition, the article emphasizes the need of using Multicast Transformation in SSIS to deliver data to numerous locations as needed.
Recommended Articles
This is a guide to SSIS Multicast. Here we discuss What is SSIS Multicast, How to use SSIS multicast, examples with code implementation. You may also have a look at the following articles to learn more –
