Multicast, Union All and Row Count Transformations

The Multicast Transformation:

Multicast transformation creates a copy of the dataset that is passed to it.

If there is a scenario where we pull data from a Source but want to send that to two or more destinations, Multicast is the way to go.

Implementing a Multicast to copy Employee data coming from OLEDB source to load 3 different files.

The UNION ALL Transformation:

This is quite the opposite of Multicast transformation.

UNION ALL is used to merge multiple datasets into one.

(This is similar to SQL’s “UNION ALL”).

                                                            Combine(UNION ALL) data coming from Flat file and Excel and insert into an OLEDB Destination

Row Count:

A Row count transformation stores the number of rows passing through it in a SSIS variable.

This information can be used to functionally manipulate the flow of the package.

 

           The Row count transformation will store the actual number records that passed through it in a variable “File_Row_Count”(highlighted above).

In the above scenario: if we can decide on further execution of the package based value stored in the “File_Row_Count” variable.

(In most cases we abandon the further processing of the file, if we know that there was no data passed into it).