SSIS Transformations(Transforms)
Transformations are a key component to the Data Flow that change data to a format that we like it.
Below are the couple of Transforms.
➤➤Aggregate—Aggregates data from a transform or source similar to a GROUP BY statement
in T-SQL.
➤➤ Conditional Split—Splits the data based on certain conditions being met. For example, if the
State column is equal to Florida, send the data down a different path. This transform is similar
to a CASE statement in T-SQL.
➤➤ Data Conversion—Converts a column’s data type to another data type. This transform is
similar to a CAST statement in T-SQL.
➤➤ Derived Column—Performs an in-line update to the data or creates a new column from a
formula. For example, you can use this to calculate a Profit column based on a Cost and
SellPrice set of columns.
➤➤ Fuzzy Grouping—Performs data cleansing by finding rows that are likely duplicates.
➤➤ Fuzzy Lookup—Matches and standardizes data based on fuzzy logic. For example, this can
transform the name Jon to John.
➤➤ Lookup—Performs a lookup on data to be used later in a transformation. For example, you
can use this transformation to look up a city based on the ZIP code.
➤➤ Multicast—Sends a copy of the data to an additional path in the workflow and can be
used to parallelize data. For example, you may want to send the same set of records to
two tables.
➤➤ OLE DB Command—Executes an OLE DB command for each row in the Data Flow. Can
be used to run an UPDATE or DELETE statement inside the Data Flow.
➤➤ Row Count—Stores the row count from the Data Flow into a variable for later use by,
perhaps, an auditing solution.
➤➤ Script Component—Uses a script to transform the data. For example, you can use this to
apply specialized business logic to your Data Flow.
➤➤ Slowly Changing Dimension—Coordinates the conditional insert or update of data in a
slowly changing dimension during a data warehouse load.
➤➤ Sort—Sorts the data in the Data Flow by a given column and removes exact duplicates.
➤➤ Union All—Merges multiple data sets into a single data set.
➤➤ Unpivot—Unpivots the data from a non-normalized format to a relational format.
Below are the couple of Transforms.
➤➤Aggregate—Aggregates data from a transform or source similar to a GROUP BY statement
in T-SQL.
➤➤ Conditional Split—Splits the data based on certain conditions being met. For example, if the
State column is equal to Florida, send the data down a different path. This transform is similar
to a CASE statement in T-SQL.
➤➤ Data Conversion—Converts a column’s data type to another data type. This transform is
similar to a CAST statement in T-SQL.
➤➤ Derived Column—Performs an in-line update to the data or creates a new column from a
formula. For example, you can use this to calculate a Profit column based on a Cost and
SellPrice set of columns.
➤➤ Fuzzy Grouping—Performs data cleansing by finding rows that are likely duplicates.
➤➤ Fuzzy Lookup—Matches and standardizes data based on fuzzy logic. For example, this can
transform the name Jon to John.
➤➤ Lookup—Performs a lookup on data to be used later in a transformation. For example, you
can use this transformation to look up a city based on the ZIP code.
➤➤ Multicast—Sends a copy of the data to an additional path in the workflow and can be
used to parallelize data. For example, you may want to send the same set of records to
two tables.
➤➤ OLE DB Command—Executes an OLE DB command for each row in the Data Flow. Can
be used to run an UPDATE or DELETE statement inside the Data Flow.
➤➤ Row Count—Stores the row count from the Data Flow into a variable for later use by,
perhaps, an auditing solution.
➤➤ Script Component—Uses a script to transform the data. For example, you can use this to
apply specialized business logic to your Data Flow.
➤➤ Slowly Changing Dimension—Coordinates the conditional insert or update of data in a
slowly changing dimension during a data warehouse load.
➤➤ Sort—Sorts the data in the Data Flow by a given column and removes exact duplicates.
➤➤ Union All—Merges multiple data sets into a single data set.
➤➤ Unpivot—Unpivots the data from a non-normalized format to a relational format.
Comments