SQL Server Integration Services Multicast

 

Introduction

This next introductory article on SSIS, the use of the Multicast component will be explored. This component is fairly straight forward in its use. It does exactly what it says. If you need to send data to two different places and don’t want to have to create a complex way if copying that data then the Multicast component fits perfectly. If you have been following the previous articles, you will have noticed that the data was previously being sent to a flat file for a report. Now we will incorporate the Multicast to send data to both the report file and to the database.

Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.

Implementation

First create a new SSIS package and name it Multicast. Then, using the same technique as before, copy the Data Flow component from the Derived Column package to the new package. Make sure to reconfigure the flat file connections for the two import files and their respective Flat File Sources. Now remove the link between the Derived Column output and the Flat File Destination component.

 

Find the Multicast component in the toolbox and add it to the work area. Connect the output of the Derived Column component to the Multicast component.

We chose Server Intellect for its dedicated servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.

At first glance, there are no configuration warnings associated with the Multicast component. The reason why is that there is nothing to configure. Now it is time to connect the outputs of the Multicast. Keep in mind that the Multicast component can cast to multiple targets. First, make the connection from the Multicast the Flat File Destination component and reconfigure a connection in Connection Manager for the Flat File Destination.

 

Now the data needs to get to the database. Using the technique from the Import article, drag an OLE DB Destination into the work area. Connect another Multicast output to the OLE DB Destination. Before creating an OLE DB connection, the database table needs to have the correct columns in the table. You can both delete the old table and run the included SQL script from the source folder or you can manually add a column called ProcessingCharge with a data type of money to the Transaction table. Go ahead and create a new OLE DB Connection in Connection Manager to get access to the database.

We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!

Configure the OLE DB Destination to use the newly created OLE DB Connection.

 

Click the Mappings tab and make sure that the data is going to the right column in the database especially the new ProcessingCharge column.

 

Testing the Package

Before you test the package, I would like to introduce a trouble shooting / quality assurance technique that might be of some use. There is a way to view the data during the testing of the package and to do this all you need to do is right click on one of the green links between components and select Data Viewers.

We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.

There are other options available but what we are interested in is the Data Viewer so go ahead and click the Add button. A dialog box will pop up.

 

There are different formats to display the data, select the Grid format. If you would like to have the choice of which columns to see in the grid then click the Grid tab.

 

Click OK to close this dialog box and then click OK to close the other dialog box. Now that the data viewer is configured, you will see a magnifying glass over a grid icon close the green link.

 

Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!

To see if the data ends up in both destinations, run the package as long as there are no configuration warnings. Since there is a data view configured, the package will pause processing the data at the spot where the data view is located and a dialog box will show a grid of the data at the spot where the data viewer is.

 

In the main Visual Studio window, you can actually see the processing stop. This actually gives the developer a chance to see the data during processing to make sure everything is on track.

 

Press the green arrow in the Data View window to continue finish process in the data.

I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.

You can now compare the data in the flat file with the data in the database table to make sure they are both identical.

 

What have we learned?

How to configure a Derived Column component.
How to use a Data View to look at the data during package execution.

 

Attachments