MSBI # 44 – SSIS # 18 – Data Flow Task # 4 – All Transformation in Data Flow task (Introduction and Usage) {Part – III}

Hi all

Continuing form my last post on basics of Data flow task in which we have covered following this parts

• MSBI # 40 – SSIS # 17 – Data Flow Task # 3 – All Source and Destination Overview
• MSBI # 38 – SSIS # 16 – Data Flow Task # 2 – Introduction {Part – I}
• MSBI # 36 – SSIS # 15 – Control Flow Tasks # 8 – Data Flow Task # 1

We have introductory information for following Transformation Task

• Aggregate Transformation
• Audit Transformation
• Cache Transform
• Character Map Transformation
• Conditional Split Transformation
• Copy Column Transformation
• Data Conversion Transformation
• Data Mining Query Transformation
• Derived Column Transformation

In this we are going to cover all transformation available in Data Flow task at basic level.

We can say we will just introductory information about few transformation task in this post

Aggregate Transformation

Its just like aggregate function of SQL Server where we can perform Group by, Sum, Average, Count, Count distinct, Minimum, Maximum

At the transformation level, you configure the Aggregate transformation for performance by specifying the following values:

• The number of groups that are expected to result from a Group by operation.
• The number of distinct values that are expected to result from a Count distinct operation.
• The percentage by which memory can be extended during the aggregation
• In the aggregate task we are having options for key count, key scale, distinct key count, and distinct key scale apply at the component level when specified on the Advanced tab, at the output level when specified in the advanced display of the Aggregations tab, and at the column level when specified in the column list at the bottom of the Aggregations tab.
• In the Aggregate transformation, Keys and Keys scale refer to the number of groups that are expected to result from a Group by operation. Count distinct keys and Count distinct scale refer to the number of distinct values that are expected to result from a Distinct count operation.

Audit Transformation

Audit term itself defines this task is used for Audit Tracking of any package tasks also enables the data flow in a package to include data about the environment in which the package runs.

We can configure the Audit transformation by providing the name of a new output column to add to the transformation output, and then mapping the system variable to the output column

Cache Transform

We can say Cache Transform is buffer storing component in SSIS

We can use Cache Connection Manager with any SQL command such as joins and then Connection manager as your Source for any other transformation

The Cache connection manager does not support the Binary Large Object (BLOB) data types DT_TEXT, DT_NTEXT, and DT_IMAGE. If the reference dataset contains a BLOB data type, the component will fail when you run the package

Character Map Transformation

Character Map transformation is use as transforming of string into required format.Character Map transformation is useful for string conversion

Also operates only on column data with a string data type.

Using Character Map we can perform miner operation on database before using it into main Data Repository or Data Warehouse

We can configure the Character Map transformation in the following ways:

• Specify the columns to convert.
• Specify the operations to apply to each column.
• The Character Map transformation includes an error output, which can be used to direct truncated data to separate output

Conditional Split Transformation

This is most useful transformation in Data Flow Task where we can traverse through different condition.

In Conditional Split Transformation incoming data can be split into multiple outputs as per the conditions specified. It has a default output too that handles all the rows that do not match to any conditions

Copy Column Transformation

Copy column is use for just to make copy of given column. We can configure the Copy Column transformation by specifying the input columns to copy also we can create multiple copies of a column, or create copies of multiple columns in one operation.

Data Conversation Transformation

This is used for conversation column into required output when moving data between relational systems

We can configure the Data Conversion transformation in the following ways:

• Specify the columns that contain the data to convert and the types of data conversions to perform.
• Specify whether the transformation output columns use the quicker, but locale-insensitive, fast parsing routines that Microsoft SQL Server Integration Services provides or the standard locale-sensitive parsing routines.

Data Mining Query Transformation

The transformation that runs data mining prediction queries.One transformation can execute multiple prediction queries if the models are built on the same data mining structure.

This is mainly used to fill gaps or predict or compare values for new columns based on the input data stream.Also for evaluating the input data set against a data mining model developed with Analysis Services

Derived Column Transformation

When we have to change our input column by adding some extra information or change input transformation into required output column

The Derived Column transformation creates new column values by applying expressions to transformation input columns.

An expression can contain any combination of variables, functions, operators, and columns from the transformation input. The result can be added as a new column or inserted into an existing column as a replacement value. The Derived Column transformation can define multiple derived columns, and any variable or input columns can appear in multiple expressions.

You can use this transformation to perform the following tasks:

• Concatenate data from different columns into a derived column. \
• Extract characters from string data by using functions such as SUBSTRING, and then store the result in a derived column.
• Apply mathematical functions to numeric data and store the result in a derived column.
• Create expressions that compare input columns and variables.
• Extract parts of a date time value.

We will surely have post on each task sooner !!

I am preparing for that one !!

Hope this helps !!

Hope you have understood basic aspect of Few transformation task and ready to use every aspects for same

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource # 24: Sept 08–Sept 12 « Dactylonomy of Web Resource