Transformations: Applying business rule on the data extracted from various sources is known as transformations.
We can use various transformations in the process of creating a SSIS package.
Transformations in SSIS are categorized into two types.
1. Synchronous Transformations
2. Asynchronous Transformations
Synchronous Transformations are executed directly and are fast.
Asynchronous Transformations are slow and uses buffer in the process. Asynchronous Transformations are further classified into Partially Blocking (Less Slower) and Complete Blocking (More Slower) Transformations.
Various Transformations discussed:
1. Sort Transformation (used to sort the data)
2. Aggregate Transformation (used to aggregate the data by a group)
3. Row Sampling Transformation (Used to get sample rows for testing purpose)
4. Percentage Sampling Transformation (Similar to row sampling. But if we do not know the number of rows to be retrieved for testing we use percentage sampling)
5. Union All Transformation (Used to merge data of many inputs into a single output)
6. Merge Transformation (Similar to Union All. The input data should be sorted before merging. We can merge data from different sources)
7. Character Map Transformation (Used to perform operations on strings,. Example to convert upper case string to lower case and vice versa)
8. Derived Column Transformation (used to derive a column depending on the given expression)