Introduction

In real-world scenarios different data sources do not provide the same structure, so there is a need to unify them in a unique structure. There are some transformations in the SSIS Data Flow task that use complex ways to apply such changes on the data stream, we call them Advanced Transformations.

There are some times when values in each row in a specified column should be transformed into columns based on a key. The Pivot Transformation comes to our help in this scenario, and in case it is the other way round, the Unpivot Transformation can be applied.

There are many times when data from the source comes in a variety of values in strings, such as "MS SQL Server", "SQLServer", and "MSSQL"—different values but the same meaning. A Fuzzy operation can detect these cases and treat them appropriately. There are two Fuzzy Transformations that will be discussed in this chapter: Fuzzy Grouping and Fuzzy Lookup.

SSIS 2012 provides a new service to cleanse data based on a dictionary. The...