Updated March 14, 2023
Definition of SSIS Transformations
The transformation functions in SSIS are the components used for data flow to perform operations like modifying, data cleansing, joining, sorting, distributing, and merging the data. The tool has a Lookup option which is more powerful than all these components with the desired format and helps for the transformation of data and then it is passed in the data flow. The transformation of data according to the preference of the user can be possible with SSIS transformation and all the data is stored in the memory and after the data transformation, it is injected into the data workflow. Hence, in this article, we can discuss SSIS transformation in brief.
What is SSIS Transformation?
In simple, the SSIS transformation functions are the major components of the data flow which is used to execute simple functions like joining, merging, data cleaning, data distribution, aggregation, modification, and sorting. It is also used to perform the Lookup function in the data flow. So the data can be transformed into the required format and the data navigate from one form to other. All these data transformations done in SSIS are stored in memory and after adding the data is modified and injected into the data flow.
Types of SSIS Transformations
The SSIS transformation in data can be performed in two main categories like synchronous and asynchronous methods. At the time of ETL design, the synchronous transformation is made and its components are derived column and conditional split transformation where the flow of data happens in a row and the same buffer gets the output. No rows can be held characteristically and they can operate quickly without any marginal impact on the data flow.
Asynchronous transformation is in dual type one is partial blocking and the other is full blocking. The transformation of partial blocking creates a new memory buffer for the transformation output from the result of the data transformation such as union functions. The full blocking transformation requires a new buffer for memory which is the same as partial blocking. The asynchronous transformation requires a complete aggregate data block and use sort transformation.
- Aggregate: It is a type of asynchronous full block transformation that enables the data aggregation from the flow of data to several T-SQL functions which are performed in Group by statements.
- Conditional split: It is a type of synchronous transformation that enables the user to send data from a unit path to different outputs based on the given conditions of SSIS expressions.
- Data conversion: Here it is made possible by the synchronous transformation which is similar to the case or converts function in T-SQL. It is useful if the same data content is retrieved from the different outputs.
- LookUp: It enables the user to execute equijoin between different values in the input and the values are added according to the reference dataset. It is used to associate two datasets simultaneously. To combine multiple datasets, the multi-lookup transformation functions are used.
- Merge: The partial blocking combines two sorted datasets into one unit dataset which is helpful at ETL. But it cannot merge a column that contains numeric data type to a column with the character data type. The merge join function is used to combine two sorted datasets with inner, full, and left join. It also has dual input and single output but doesn’t support any error output.
- Multicast: It is a type of synchronous transformation that enables the user to distribute the input to single or multiple outputs. It is the same as for conditional split transformation which directs the input to more output.
SSIS Transformations Examples
The few examples which are important in SSIS for the business intelligence operation are data cleaning, data correcting, and data mining.
In the slowly changing dimension, the coordinates of transformation can be inserted and updated in the dimension table of the data warehouse. Here the SSIS supports SCD type 2, type 1, and type 0.
In the fuzzy group transformation, it is used to swap the mistaken words with the correct ones using grouping methods
In the term extraction method, it is used to retrieve the correct terms from data present in the raw data. It gives four different features like extract only noun, exclusion tab extract phrases of a noun, and extract both noun and noun phrases
In fuzzy lookup transformation, it involves fuzzy matching to match one or more similar matches in the reference table and swap it with the raw data concerning the reference data.
The data mining queries and data cleansing methods are also used to execute rules to process the data.
Join Transformations SSIS
In the Join transformation, there are multiple options like conditional split transformation, multicast transformation, union all transformation, merge transformation, merge join method, lookup, and cache transformations. All these features are used to combine two different datasets or two or more datasets according to the preference of the user. The conditions case or if statement is used to check and execute the result based on the given condition. Union all is used to merge data from multiple file sources like SQL tables, excel, and flat files to produce a single output.
Row transformation in SSIS:
In row transformation, the values of the existing column are updated to create a new column. The character map transformation, copy column methods, data conversion, derived column, export column, import column, script component transformation, and OLEDB command transformation are used to perform row transformation in SSIS. They are used to transform uppercase, lowercase, cast, convert, and give a new required output. The user can import or export large files, binary files, and media files from the server to file systems. The Derived column is an example of synchronous transformation which is used to create a new column and the output is retrieved from the other column. This derived column transformation provides you dual options, one the user can create a new column or he can replace it with the existing one.
Conclusion
Hence there are multiple useful options in SSIS transformation to manage the data in the SQL server. It is easy to apply and an effective process when handling multiple files from various sources. All the types of actions are performed in multiple files to get a single preferred output according to the user with the help of SSIS transformations.
Recommended Articles
This is a guide to SSIS Transformations. Here we discuss the Definition, What is SSIS Transformation, Types, examples with code implementation. You may also have a look at the following articles to learn more –
