Data Transformation defined as core component in the Data flow of package In SSIS, It is part of the data flow to which we apply our business logic To change and manipulate the input data into the required format before Loading
Kosmik Technologies provides MSBI online training in Hyderabad and also Provide other online training in Hyderabad, They can provide learning classes And certification, better faculty in Hyderabad will provide.
Different types of data Transformations available in SSIS.
1. TOTAL: The transformation applies total functions to Copies and column values the results of the transformation Output, besides total functions, the transformation provides GROUP BY clause, which you can use to specify groups to add Across, The Transformation supports following operations, AVERAGE, LARGEST, SMALLEST, COUNT, SUM, COUNT DISTINCT, GROUP BY An example of Total Transformation:
2. AUDIT: Task-level Metadata and Adds Package such as Package Name, Execution Instance, Machine Name, Package ID, etc
3. UNPIVOT: Used for demoralizing the data structure by converts columns into rows In case of building Data Warehouses
4. COPY COLUMN: Add copy of column to the output, we can later transform the Copy keeping the original for auditing
5. CHARACTER MAP: Character Map Transformation is very useful, used to convert data. When it comes to string formatting in SSIS Upper case, lower case
6. EXPORT COLUMN: Used to export an Image specific column from the database To flat file
7. DATA CONVERSION: Converts columns data types from one to another type. It stands for Explicit Column Conversion
.
8. DERIVED COLUMN: Create new column from given expressions.
9. FUZZY LOOKUP: Used for Pattern Matching and Ranking based on fuzzy logic.
10. CONDITIONAL SPLIT: used to split the input source data based on condition.
11. DATA MINING QUERY: It Used to perform data mining query over analysis
Services and manage Predictions Graphs and Controls.
12. FUZZY GROUPING: Groups the rows in the dataset that contain similar values.
13. IMPORT COLUMN: Reads image specific column from the database onto a flat file.
14. LOOKUP Performs the lookup of a given reference object set to a data Source, it used to find exact matches.
15. MERGE: Merges two sorted data sets of same column structure into a single output.
16. MERGE JOIN: Merges two sorted datasets into a single dataset using a join.
17. MULTICAST, it used to create or distribute exact copies of the source dataset To one or more destination datasets
18. ROW COUNT: Stores the resulting row count from the data flow or transformation Into different rows.
19. ROW SAMPLING: Captures sample data by using row count of the rows in Dataflow specified by rows or percentage.
20. UNION ALL: Merge many datasets into a single dataset.
21. PIVOT: Used for Normalization of data sources to reduce by Converting rows into columns
Different Types of data Transformation with examples:
Row Transformation: Each value manipulated. In Run transformation, The buffers can reuse for other purposes like following OLEDB Data Destinations, OLEDB Data source Other blocking transformations within the package, Other Row transformation within the package
Examples of Row Transformations: Audit, Copy Column, Character Map
2. Blocking Transformation:
These can use the buffer space allocated for available Row transformation and get new buffer space allocated only for Transformation
Examples: Merge, Multicast, Export, Column, Lookup, Import, Conditional Split
3. Blocking Transformation:
It will make use of reserve buffer and does not share buffer space from Other transformation.
Examples: Total, Sort, Cache Transformation