Transformations in a SSIS Data Flow Task

If we are trying to relate the keyword “Transformation” as in “Extraction Transformation Load” (ETL) to the Transformations in a Data Flow Task(this article), then we are absolutely correct!!!!

As the name suggests, these Transformation components that are available in a Data Flow Task are used to Transform the Data/Metadata while the data is flowing from a Source to a Destination(on the fly).

The term “Transformations” has a very wide scope in SSIS. Each transformation component is different from another and serves different purpose. For e.g. there are transformations that will aggregate the data hence changing the granularity of the Data Set, some can Union the data coming from multiple Sources and push it into one destination, some are used to filter the data etc.

Let us begin by looking into one of the commonly used(and easy to understand) Transformations is SSIS:

 The Conditional Split Transformation

Conditional Split can be defined as a component that will route data rows to different outputs depending on the content of the data.

(It is similar to a CASE decision structure in a programming language).

Consider a scenario where a flat file contains consolidated sales data and we would like to split the data based on a Gender Column coming present in source so that it can be pumped it into different destinations for carrying out further analysis.

In the above flow we are using a Conditional Split transformation to divide the data based on the gender.

One set goes into an OLEDB table(Destination 1) and the other into an Excel file(Destination 2).

How this works(functionally):

1.       Read all the data in the Flat File through the Flat File Source Component.

2.       Each record will pass through the Conditional Split transformation and will be parsed-One by one.

3.       The Conditional Split transformation will be configured in such a way that will check the value in the “Gender” column of the record.

From a functional perspective, Expression used here would be {Is the Gender_Column=”Female” or “Male”}

4.       This expression would be checked for each record that is passing through this transformation and based on the result the record would be sent to the appropriate destination.

5.       If the value is “Female” the record will be sent to OLEDB table; If the value is “Male” the record will be sent to the Excel file.    

In the coming articles, we shall explore more on the major/highly used Transformation components in SSIS.