It aggregates data, similar you do in applying T-SQL functions like Group By, Min, Max, Avg, and Count. For example you get total quantity and Total line item for each product in Aggregate Transformation Editor. First you determine input columns, then output column name in Output Alias table in data-grid, and also operations for each Output Alias in Operation columns of the same data-grid.
Some of operation functions listed below :
- Group By
- Average
- Count
- Count Distinct : count distinct and non null column value
- Min
- Max
- Sum
Q: Explain Conditional Split transformation in SSIS?
This is just like IF condition which checks for the given condition and based on the condition evaluation, the output will be sent to the appropriate OUTPUT path. It has ONE input and MANY outputs. Conditional Split transformation is used to send paths to different outputs based on some conditions.
Step by Step Tutorial on Conditional Transformation in SSIS
Q: Explain Data Conversion Transformation in SSIS?
This component does conversion data type, similar to T-SQL function CAST or CONVERT. It is used to convert the data from one type to another.
Data Conversion Transformation in detail with Example
Q: Explain Derived column Transformation?
Derived column creates new column or put manipulation of several columns into new column. You can directly copy existing or create a new column using more than one column also.
Step by Step Tutorial on Derived Column Transformation in SSIS
Q: Explain Audit Transformation in SSIS?
It allows you to add auditing information as required in auditing world specified by HIPPA and Sarbanes-Oxley (SOX). Auditing options that you can add to transformed data through this transformation are :
Execution of Instance GUID: ID of execution instance of the package
- PackageID: ID of the package
- PackageName
- VersionID: GUID version of the package
- Execution StartTime
- MachineName
- UserName
- TaskName
- TaskID: uniqueidentifier type of the data flow task that contains audit transformation.
Q: Explain Character Map Transformation in SSIS?
It transforms some character. It gives options whether output result will override the existing column or add to new column. If you define it as new column, specify new column name. Operations available here are:
- Uppercase
- Lowercase
- Byte reversal
- Hiragana
- Katakana
- Half width
- Full width
- Linguistic casing
- Simplified Chinese
- Traditional Chinese
Q: Explain Copy column Transformation in SSIS?
This component simply copies a column to another new column. Just like ALIAS Column in T-SQL.
Q: Explain Merge Transformation in SSIS?
Merge transformation merges two paths into single path. It is useful when you want to break out data into path that handles errors after the errors are handled, the data are merge back into downstream or you want to merge 2 data sources. It is similar with Union All transformation, but Merge has some restrictions :
- Data should be in sorted order
- Data type , data length and other meta data attribute must be similar before merged.
Q: Explain Merge Join Transformation in SSIS?
Merge Join transformation will merge output from 2 inputs and doing INNER or OUTER join on the data. But if you the data come from 1 OLE DB data source, it is better you join through SQL query rather than using Merge Join transformation. Merge Join is intended to join 2 different data source.
Q: Explain Multicast Transformation in SSIS?
This transformation sends output to multiple output paths with no conditional as Conditional Split does. Takes ONE Input and makes the COPY of data and passes the same data through many outputs. In simple Give one input and take many outputs of the same data.
Q: Explain Percentage and row sampling Transformations in SSIS?
This transformation will take data from source and randomly sampling data. It gives you 2 outputs. First is selected data and second one is unselected data. It is used in situation where you train data mining model. These two are used to take the SAMPLE of data from the input data.
Q: Explain Sort Transformation in SSIS?
This component sorts the data similar to T-SQL command ORDER BY.
Q: Explain Union all Transformation in SSIS?
It works in opposite way to Merge transformation. It can take output from more than 2 input paths and combines into single output path.
No comments:
Write Comments