MSBI # 54 – SSIS # 20 – Data Flow Task # 6 – All Transformation in Data Flow task (Introduction and Usage) {Part – V}

Hello friends,

Let me complete this SSIS series as soon as possible so that my readers will get over all idea regarding SSIS tool as soon as possible

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

• MSBI # 36 – SSIS # 15 – Control Flow Tasks # 8 – Data Flow Task # 1
• MSBI # 38 – SSIS # 16 – Data Flow Task # 2 – Introduction {Part – I}
• MSBI # 40 – SSIS # 17 – Data Flow Task # 3 – All Source and Destination Overview-II
• MSBI # 44 – SSIS # 18 – Data Flow Task # 4 – All Transformation in Data Flow task (Introduction and Usage) {Part – III}
• MSBI # 53 – SSIS # 19 – Data Flow Task # 5 – All Transformation in Data Flow task (Introduction and Usage) {Part – IV}

In this article we have introductory information of following Transformation Task

• OLE DB Command Transformation
• Percentage Sampling Transformation
• Pivote Transformation
• Row Count Transformation
• Row Sampling Transformation
• Script Component Transformation

In this article we are going to cover few transformation available in Data Flow task at basic level, as few of we already discuss here .

As I am always surely have detailed explanation each if this task in my future post !!

OLE DB Command Transformation

OLE DB Command Transformation is most used Transformation task in SSIS task as it directly communicate with SQL Server via SQL Query

The OLE DB Command transformation runs an SQL statement for each row in a data flow

We can can configure the OLE DB Command Transformation in the following ways

• Provide the SQL statement that the transformation runs for each row
• Specify the number of seconds before the SQL statement times out.
• Specify the default code page.

The OLE DB Command transformation in the data flow engine provides for performing data-related operations.

We can log the calls that the OLE DB Command transformation makes to external data providers. we can use this logging capability to troubleshoot the connections and commands to external data sources that the OLE DB Command transformation performs.

To log the calls that the OLE DB Command transformation makes to external data providers, enable package logging and select the Diagnostic event at the package level.

Percentage Sampling Transformation

The Percentage Sampling transformation is especially useful for data mining. By using this transformation, you can randomly divide a data set into two data sets: one for training the data mining model, and one for testing the model.

The Percentage Sampling transformation creates a sample data set by selecting a percentage of the transformation input rows. The sample data set is a random selection of rows from the transformation input, to make the resultant sample representative of the input.

Use the Percentage Sampling Transformation Editor dialog box to split part of an input into a sample using a specified percentage of rows. This transformation divides the input into two separate outputs.

Percentage Sampling Transformation When you need to give out data to call centers for telesales activities, we are generally asked to create a sample set from a data segmentation. Sometimes the requirement is defined as a percentage.

The Percentage Sampling transformation uses an algorithm to select at random the number of rows according to the specified percentage.

Pivot Transformation

The Pivot transformation makes a normalized data set into a less normalized but more compact version by pivoting the input data on a column value.

• Pivot transformation is being used to transpose rows into columns just like Excel Transpose

• Pivot transformation T-SQL also, we have PIVOT command available which is more reliable and faster than SSIS

• Pivot in SSIS is not recommended generally

The Pivot transformation does much exactly what that name implies. This pivots data along an x-axis which is determined by values in a column. Another column of value are used along the y-axis to determine the columns to include.

Since this columns on the y-axis are determined by the data in the source there is some configuring in the Pivot transformation that needs to occur to properly pivot data.

Row Count Transformation

The Row Count transformation counts rows as they pass through a data flow and stores the final count in a variable.

Also this transformation stores the row count value in the variable only after the last row has passed through the transformation. Therefore, the value of the variable is not updated in time to use the updated value in the data flow that contains the Row Count transformation. You can use the updated variable in a separate data flow.

Row Sampling Transformation

The Row Sampling transformation is useful during package development for creating a small but representative dataset. You can test package execution and data transformation with richly representative data, but more quickly because a random sample is used instead of the full dataset. Because the sample dataset used by the test package is always the same size, using the sample subset also makes it easier to identify performance problems in the package.

Row Sampling Transformation The Row Sampling transformation works quite similar to the Percentage Sampling transformation to sample a data set.
The Row Sampling transformation outputs an exact number of rows as specified in the transformation.
This random selection of a precise number of rows is sometimes very useful. An example of such a scenario can be a gift allocation to the random selection of people. Suppose you’re running a campaign to introduce your new product to different segments of your customers and prospects by sending them an e-mail every week.

The Row Sampling transformation is used to obtain a randomly selected subset of an input dataset. You can specify the exact size of the output sample, and specify a seed for the random number generator

The Row Count transformation counts rows as they pass through a data flow and stores the final count in a variable.

A SQL Server Integration Services package can use row counts to update the variables used in scripts, expressions, and property expressions.

Script Component Transformation

Script Component is also one most important and highly used Task.

There is lots of Difference between Script Task and Script Component

Script Task – Used in Control flow Tab

Script Component – Used in Data Flow Tab

The Script task provides code to perform functions that are not available in the built-in tasks and transformations that SQL Server Integration Services provides. The Script task can also combine functions in one script instead of using multiple tasks and transformations. You use the Script task for work that must be done once in a package (or once per enumerated object), instead than once per data row.

We can use the Script task for the following purposes:

• Access data by using other technologies that are not supported by built-in connection types. For example, a script can use Active Directory Service Interfaces (ADSI) to access and extract user names from Active Directory.

• Create a package-specific performance counter. For example, a script can create a performance counter that is updated while a complex or poorly performing task runs.

• Identify whether specified files are empty or how many rows they contain, and then based on that information affect the control flow in a package. For example, if a file contains zero rows, the value of a variable set to 0, and a precedence constraint that evaluates the value prevents a File System task from copying the file.

We will surely have post on each task sooner !!

I am preparing for that one for you all !!

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 Website>> Dactylonomy of Web Resource