SSIS: Multicast Transformation

Implementing conditional logic to redirect output.

I’ll begin with two separate text files, each representing sales from two states: California and Oregon.

1-MulticastTransformation

First, I’ll add a DataFlow task and add add to it two Flat File sources each configured to pull in data from the text files.
When configuring the sources, it’s imperative to ensure the “Unicode string” data type is selected due to a quirk with SSIS when importing data into an Excel spreadsheet. Also, if your platform is Windows 64-bit, you might need to set Run64BitRunTime=false.

2-MulticastTransformation

Next, I’ll drag an Excel destination unto the designer.

Now I’ll drag a Union All transformation to which I’ll connect both Flat File sources.

4-MulticastTransformation

By opening the transformation, I can see what columns are expected as well as the final output.

5-MulticastTransformation

Next, I’ll drag a Sort transformation and connect the Union All transformation to it.
Also, I’ll configure it to sort according to the name, then territory.

Now I’ll add another transformation called Multicast and connect it to the Sort transformation.
Then, I’ll connect the Multicast transformation to the Excel destination.

Configure the Excel Destination to create a new spreadsheet.
Begin by creating a new Connection Manager for the new spreadsheet, then ensure its mappings are correct.

The package is ready for execution, upon doing so, you will see all Tasks turn green and the number of affected rows for each Tasks indicated.

By navigating to the path specified when configuring the Excel Destination Connection Manager, you will see a new Excel spreadsheet with the results from both text files displayed.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s