Aim – We have composed this article keeping in mind beginners who are searching Internet to learn Multicast transformation in SSIS. This post will take you through different steps which can be required to Configure SSIS Multicast Transformation. We have tried our best to explain this in very simple language and by taking an easy example. Let’s learn this SSIS transformation practically.
Definition – Multicast Transformation in SSIS is used to distribute every Input row to every row in one or more Outputs.
Where to Find – You can find SSIS Multicast transformation in Data flow level for the implementation.
Where to Use – It can be used in the scenarios where you require same Input source at different destinations/intermediate steps, either for getting input into different output formats or to apply different transformations on the same input.
Example – Suppose, you have a table in SQL Server database and you have a requirement to show this table data into different output formats such as Excel and Flat file. You can achieve this normally with creating 2 SSIS packages, one package to load data from SQL Server to Excel and another package for loading data from SQL Server to Flat file. But, If you need it into 5 different formats, then this will be a tedious task. To solve this problem, we can use Multicast transformation and can load data to different output formats in a single step. Below is a practical demonstration of this example. I am using Dim_Employee table from the Download Adventure works database in the below example.
Multi cast Transformation in SSIS
STEP 1 : Create an SSIS package
- Open SSDT (SQL Server Data Tool) in SQL Server 2012 and above versions or BIDS (Business Intelligence Development Studio) in SQL Server 2008R2 and below versions.
- Create 1 SSIS package and give it a desired name, say “MultiCast.dtsx “.
- Once you are geared up with the necessary things, your screen must be identical to the below screenshot.
- Initially, Drag and drop 1 Data Flow Task (also known as DFT) from toolbox to Control flow pane.
- To make it more professional, give it some appropriate name such as “MultiCast Example”.
STEP 2 – Understand different types of Data Sources in SSIS
- Now, let’s get into the core of this SSIS package (Data Flow pane) and play more.
- On double clicking the Data Flow Task, We will proceed into Data Flow pane where all the data transformations is done.
- To start with, we need to build a connection with our data source.
- Data sources can be of various types such Flat file for Text files, OLEDB for SQL Server, Excel source for Excel data, etc.
- As, we are fetching data from the SQL Server in this example, so appropriate data source we need to use is OLEDB Source.
- Drag and drop one OLEDB source to the Data Flow pane. Small red bubble with cross in it represents that source is not configured properly. So, get ready to configure it with proper connections.
STEP 3 – Configure data source in SSIS – OLEDB data source
- Double Click on OLEDB source to open configuration window. Believe me, data source configuration steps are really very simple.
- If you are making data source connections for the first time, then click on New button else if already created then you can proceed with choosing the required connection. For the geeks creating connections 1st time – After clicking on New button, it will lead to a popup window where you need to specify Server Name, Authentication method and Database name from which you need the data to be fetched.
- Do click on Test connection button, to check whether you are really connected with data server or need to make changes. Once your connection is successful, you will see OLEDB source editor window. This window will show you OLEDB connection manager, Data access mode and a drop down to fetch any data from the selected table as shown in the image below.
NOTE – If you wish to fetch data from more than one table, then Choose SQL query option from Data access mode. Here, you can write your query and can join N number of tables based upon your requirement.
- Click on Connections to choose columns from the selected table.
- With this, we are set with the configuration of our Data source in SSIS.
STEP 4 – Configuring SSIS Multicast Transformation
- Drag and drop Multicast transformation from the toolbox to the Data Flow pane.
- Place Multicast transformation below OLEDB source and connect them as shown below.
NOTE – If you will try to open SSIS Multi cast transformation, it will show nothing. This is because the work of this transformation is to create multiple destinations (copies) of the Input source (data). This transformation will not alter incoming data.
STEP 5 – Configure destinations in SSIS – Excel and Flatfile.
5.A. Configure Excel destination in SSIS –
- Drag and drop some appropriate destinations (as per your business logic) onto Data flow pane.
- I am using Excel destination and Flat file destinations to get the incoming source data into Excel and Text file formats respectively.
- Connect your Multicast transformation with both the destinations.
- Now, if you will double click on Multicast transformation then it will show you 2 Multicast outputs as shown below.
- Double click on Excel destination to configure it.
- Either Choose Excel connection manager from the drop down list (if already created), or Create a new connection by clicking on New button.
- Browse to your Excel destination path and tick “First tow has column names check-box”, if required.
- Click on OK button once path for the Excel is provided.
- Now, Click on New button to create a table in Excel destination for the incoming data. This will create a structure in Excel if not present earlier.
- Once structure is created in the Excel destination, Choose your desired sheet from the drop down list as demonstrated in the image below.
NOTE – Best practice is to choose excel sheet with a $ symbol because it can help you in future. Suppose, if you wish to choose only particular rows from excel instead of complete excel sheet, then we can achieve this with the use of $ symbol.
- Woohoo !!! We have successfully created the connection with Excel destination.
- In this step, we will click on Mapping tab to map the desired Available input columns with Available destination columns.
- With this Mapping step, we are done with our Excel destination configuration. You can also click on Error output tab to handle errors as per your requirement. Finally, click on OK button.
5.B. Configure Flat file destination in SSIS –
- Time to configure our next destination i.e. Flat file destination.
- Click on New button if Flat file connection manager does not exist earlier else choose from the drop down list.
- As per your requirement, Choose your Flat file format, say Delimited format as in our case.
- Other Flat file formats are Fixed width, Fixed width with row delimiters and Ragged right.
- In this step, we need to provide desired Connection manager name, any Description if you wish to, File path (location where text file is present) and other values as per your need. Below attached image is for the reference purpose.
- This is going great till now as we have established a successful connection with our Flat file.
- Now, click on Columns tab to check whether required columns are present or not.
- Clicking on Advanced tab can help you in editing Columns name and other properties related to the Input fields.
- Once you are satisfied with the settings and configurations, click on OK button.
- Click on Mappings tab to map your Available Input columns with the Available Output columns.
- Finally click on OK button to finish with Flat file configuration.
STEP 6 – SSIS Multicast package execution
- Just relax now, this is the time to sit and watch how your SSIS package will execute and what will be the output.
- P.S – Geeks who can see green signs in their package can relax. Other people with red signs, believe me, you are into a messy situation.
STEP 7 – SSIS package Outputs – Excel and Flat file
7.A. Excel output – Below Excel output is same as our Input SQL Server database Dim_Employee table.
7.B. Flat file output – Below Text file output is same as our Input SQL Server database Dim_Employee table.
With this we complete our post on Multicast transformation in SSIS and we hope you will like our efforts. We will appreciate your time for putting any query or feedback below in the comments section.