The data flow components that combines that distribute. Total, merging, sorting, joining, cleansing. And change the data in SQL Server Integration Service. SSIS Transformation may also presenting the generating the sample data, and lookup operations. This SSIS Transformations department or section can explain the SSIS Transformations. And also explain how to do they work. In this content we have to explain the working functionalities in SSIS transformation
Different types of SSIS functional transformations:
Here there are Different types of SSIS functional transformations can classified into five types. They are
1.Business Intelligence Transformations
4.Split And Join Transformations
Business Intelligence Transformations:
This SSIS Transformation type of transformation can classified into six parts. They are
1. Fuzzy Group Transformation: The fuzzy group transformation used based on the matching decision. To detect the row if duplicates and also decreasing the number of duplicate data. This transformation can only obey the string data type. Which is only reducing the duplicate data.
2. Fuzzy Lookup Transformation: the fuzzy lookup transformation used to incoming data stream. That reference data returned by the close matches. In fuzzy logic the data was standardise and matches.
3. Term Extraction Transformation: The term extract transformation can done in the input transformation. Into the output transformation column.
4. Term Lookup Transformation: In the term Lookup transformation. To change in the test formation input transformation only to match the text database. In input column the test transformation term scanned to the lookup transformation.
5.Data Mining Query Transformation: Query transformation is the data mining model. The data mining expression prediction queries are assisting to develop the query building.
6.Data Cleansing Transformation: This transformation can used to cleansing automatic data. To controlling the total status of the data cleansing process.
Row transformation can classified into six types. They are.
1. Character Map Transformation: This transformation used to string columns character operation. And it changes the common string data.
2. Copy Column Transformation: The copy of column added to the transformation output. In this copy column transformation.
3. Data Conversion Transformation: The column data type can changed. Into the new column data type. In this data conversion transformation.
4. Derived Column Transformation: In data column to apply the expression. And to apply the new derived column also can calculated from the expressions
5. OLEDB Command Transformation: The SQL statement has parameters. That the SQL command can run in the input data.
6. Script Component Transformation: The custom transformation can used in this transformation. This transformation can used to script transform. And to apply for specialised business logic.
Row Set Transformations:
This transformation can classified into six types.
1. Total Transformation: This transformation used to combine to data transformation. Sources and it combined the group values also.
2. Row Sampling Transformation: This transformation used to acquisition. The data sampling from the data.
3. Percentage Sampling Transformation: It can defined to calculate the all data source percentage. It is subset of our data.
4. Sort Transformation: The data flow is kind to given a column data flow in this transformation. And it eliminated the duplicate values.
5. Pivot Transformation: The data column centre turned into the non-relation form. It may change the rows into columns.
6. UnPivot Transformation: This transformation used to decentralised. The non normalised format to the relational format.
Split and Join Transformations:
This transformation can explained into seven types. They are
1. Cache Transformation: The data can stored in the file. Or memory in this transformation. In future use,
2. Conditional Split: This type of transformation used to accept the input. The result expression may determined the destination to the pipe.
3. Look-Up Transformation: This type of transformation can be entered into the input. And also to set the reference table. This row set can created by the SQL server statement.
4. Merge Transformation: Two kinds of inputs combined into one single output. The value of the key columns is in every data set.
5. Merge-Join Transformation: By using the join function the combination of two data-sets. Are into one data-set in this transformation.
6. Multicast Transformation: The copy of the data-flow can be send to the workflow. More path in this transformation.
7. Union-All Transformation: The combination of many inputs into one single output. The rows are order to set into the added transformation.
In custom transformation explained in five types. They are
1. Audit Transformation: The data pipe package expose. The auditing the information in this transformation.
2. Row Count Transformation: The rows in the data flow can counted in the transformation. And stored in the variables.
3. SCD Transformation: The values of the historical member’s dimensions. And new member’s dimensions maintained in the transformation. It generate the transformation by TYPE 1 and TYPE 2 SCD’s
4. Export Transformation: In this transformation the export column can be transform. Into the data flow system.
5. Import Transformation: In this transformation the data read from the files and to fix it to the data flow