MSBI # 55 – SSIS # 21 – Data Flow Task # 7 – All Transformation in Data Flow task (Introduction and Usage) {Part – VI}

Hi friends ,

As I am getting more time today I want to utilized same for blogging only !!

Today only I have completed 2 post which is in series of All Transformation in Data Flow task

So here is complete sequence form my last continuing post

• 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}
• MSBI # 54 – SSIS # 20 – Data Flow Task # 6 – All Transformation in Data Flow task (Introduction and Usage) {Part – V}

As I compete list which means this is my last post on introductory information Transformation Task after this there will be single post covering single transformation task

In this article we have introductory information of following Transformation Task

• Slowly Changing Dimension Transformation
• Term Extraction Transformation
• Term Lookup Transformation
• Union All Transformation
• Unpivot Transformation
• Sort Transformation

Slowly Changing Dimension Transformation

The Slowly Changing Dimension transformation coordinates the updating and inserting of records in data warehouse dimension tables

The Slowly Changing Dimension task help us to insert and update Dimensions in in Data warehouse , Wizard’s for same is most simple among all other task

Slowly changing dimensions reflect the natural tendency to modify dimension member values over time

The Slowly Changing Dimension transformation provides the following functionality for managing slowly changing dimensions:

• Matching incoming rows with rows in the lookup table to identify new and existing rows.

• Identifying incoming rows that contain changes when changes are not permitted.

• Identifying inferred member records that require updating.

• Identifying incoming rows that contain historical changes that require insertion of new records and the updating of expired records.

• Detecting incoming rows that contain changes that require the updating of existing records, including expired ones.

Term Extraction Transformation

Term Extraction transformation uses various defined data mining algorithms to to extract nouns and OR or phrases that are passed through it and then give a score to each term or phrase based on the frequency of its occurrence

The Term Extraction transformation extracts terms from text in a transformation input column, and then writes the terms to a transformation output column. The transformation works only with English text and it uses its own English dictionary and linguistic information about English

The Text Extraction transformation uses internal algorithms and statistical models to generate its results. You may have to run the Term Extraction transformation several times and examine the results to configure the transformation to generate the type of results that works for your text mining solution.

The Term Extraction transformation has one regular input, one output, and one error output.

 

Term Lookup Transformation

Term Lookup transformation performs a lookup, it extracts words from the text in an input column using the same method as the Term Extraction transformation:

• Text is broken into sentences.

• Sentences are broken into words.

• Words are normalized.

Use the Term Lookup tab of the Term Lookup Transformation Editor dialog box to map an input column to a lookup column in a reference table and to provide an alias for each output column.

Using the Term Lookup transformation, we can count the number of times a text term occurs in the input data row and create custom word lists and word frequency statistics.

This transformation reads the terms from a lookup table to look for matches in an input column and then, by default, adds two columns named Term and Frequency to the output containing the term and the count for the term.

Union All Transformation

The Union All transformation combines multiple inputs into one output.

The transformation inputs are added to the transformation output one after the other; no reordering of rows occurs. If the package requires a sorted output, you should use the Merge transformation instead of the Union All transformation.

The first input that we need to connect to the Union All transformation is the input from which the transformation creates the transformation output. The columns in the inputs we can subsequently connect to the transformation are mapped to the columns in the transformation output.

As we have already seen The Merge transformation is similar to the Union All transformations. Use the Union All transformation instead of the Merge transformation in the following situations:

• The transformation inputs are not sorted
• The combined output does not need to be sorted.
• The transformation has more than two inputs.

Unpivot Transformation

The Unpivot transformation makes an un normalized dataset into a more normalized version by expanding values from multiple columns in a single record into multiple records with the same values in a single column.

the SSIS Unpivot transformation does the the opposite of a Pivot transformation

Unpivot transformation  will convert data in columns into rows. Data that as previously been pivoted is difficult to manipulate further- so this is where the unpivot transformation.

User interface also is also very easy  as compare to actual scripting of pivot and unpivot in T-SQL

Sort Transformation

The Sort transformation sorts input data in ascending or descending order and copies the sorted data to the transformation output

Sort Transformation can be used to sort incoming data stream. In addition, the Sort Transformation distinct option can be used to remove duplicate values from the input.

Sort Transformation is a blocking transformation meaning that the input records are accumulated until the end of input. Blocking transformations affect the performance of overall dataflow because subsequent steps cannot execute until all the records have been received and processed by the blocking transformation.

Sort Transformation uses storage on the server for temporary data during sorting. The server must have enough capacity to store the entire data set and index.

The Sort transformation includes a set of comparison options to define how the transformation handles the string data in a column. For more information.

In this way from last five post based on SSIS as provided link above we have completed all introductory information of all transformation task .

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