Multicast Transformation in SSIS sends input data to multiple destination paths without applying any sort of conditions or transformations. OR, Takes ONE Input and makes the logical COPY of data and passes the same data to multiple outputs.
SSIS Multicast Transformation is very useful when we want to apply different transformations on the same dataset in parallel. For instance, If we want to perform 2 types of transformations on same dataset then SSIS Multicast Transformation will make 2 copies of same data. On each copy we can perform different transformations.
Multicast Transformation in SSIS Example
In this example, We are going to use SSIS Multicast transformation to send the OLE DB source data (from Dimcustomers and Fact internet sales tables present in Adventure Works DW 2014) to multiple destinations such as Excel file and Text file using Excel destination and Flat File destination.
STEP 1: Drag and drop the data flow task from the toolbox to control flow and rename it as Multicast Transformation.
Double click on data flow task will open the data flow tab.
STEP 2: Drag and drop OLE DB Source, Multicast Transformation in SSIS toolbox to data flow region
STEP 3: Double click on OLE DB source in the data flow region will open the connection manager settings and provides space to write our SQL statement.
SQL Command Text
The SQL Command that we used in the above screenshot is:
SELECT Color, EnglishProductName, ListPrice, DealerPrice, EnglishDescription, Fact.SalesAmount FROM DimProduct INNER JOIN FactInternetSales AS Fact ON DimProduct.ProductKey = Fact.ProductKey
For this SSIS Multicast Transformation example, we are selecting color, English Product Name, List Price, Dealer Price, English Description from DimProduct table and Sales amount column from Fact Internet Sales table presented in [Adventure Works DW 2014]
STEP 4: Click on columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.
TIP: If we don’t want any column then please don’t add them in your SQL command.
STEP 5: Click ok. Drag the arrow downwards and attach to Multicast. There is nothing to do with SSIS Multicast Transformation because it will just transfer the data to multiple outputs. Drag and drop the Excel Destination into data flow region.
STEP 6: Now we have to provide Excel Connection Manager and table details of the destination. So double click on the Excel Destination and provide the required information
Here we selected the DimProducts.xls as our excel destination sheet
TIP: You have to create the Excel sheet before using it in the Excel Connection Manager. If you check mark the First row has column names option then, Column names will be saved in the first row of the selected Excel sheet.
Here we selected the Product_Destination table so, to check the result you have to open the Product_Destination sheet inside the DimProducts.xls
STEP 7: After configuring the table click on Mappings tab to check whether the source columns are exactly mapped to the destination columns.
Click ok to finish configuring the excel destination.
STEP 8: Drag and drop the Flat File Destination into data flow region. Now we have to provide Flat File Connection Manager and file name of the destination. So double click on the Flat File Destination and provide the required information
STEP 9: After configuring the table click on Mappings tab to check whether the source columns are exactly mapped to the destination columns.
Click ok to finish the SSIS Multicast package set up and Run the package
Check the result in Excel sheet
Check the result in Text file
Thank you for Visiting Our Blog