Floating FB popout byF5debug

SQL Server Integration Services (SSIS) – Part 49 – Real time Examples of Data Flow Transformations in SSIS

 

This is part 49 of the series of article on SSIS

Introduction:

In my series of articles there are few scenarios where we need to take care of some real time samples and examples to explain the task to reach it to the end users. So I can up with this article on to show some real time scenarios for each and every transformation. There are 28 data flow transformations and I grouped them below to give some real time idea on my working experience with those controls.

You can look into my series of article on SSIS at the url - http://f5debug.net/tutorial/ssis.aspx

[more]

List of 28 Transformations are as follows

S No

Transformation

Real Time Examples

1

Aggregate

Summing / Averaging a total of the products purchased by a customer online to produce the final amount.

2

Audit

For audit purpose, when we need to audit the logs to send to DBA’s for weekly or monthly auditing.

3

Character Map

For sending mails (do some manipulations) to the end users to do some formatting we can use this task.

4

Conditional Split

Morning feeds which we get from different systems need to be transferred to different tables based on the feed which we get so we can use this task to do some condition check.

5

Copy Column

Morning feeds which needs to be transferred to tables need to be scanned under for cleaning spaces, empty values etc then we can go with this task

6

Data Conversion

Daily monitoring of the input files and data to have proper datatype before mapping it to the table then we can use this task.

7

Data Mining Query

Evaluating the input data against the analysis model to get a proper set.

8

Derived Column

Adding a title of courtesy (Mr., Mrs., Dr, etc) before the name and removing the trailing and ending spaces.

9

Export Column

When we get the normal files/pdf files/image files from different systems and save it under a particular folder and map it to the table master

10

Fuzzy Grouping

Matching the name of a customer with master and child table and use it to group and get the desired set

11

Fuzzy Lookup

Matching the name of a customer with master and child table and use it to group and get the desired set

12

Import Column

When we get the normal files/pdf files/image files from different systems and save it under a particular folder and map it to the table master

13

Lookup

Employee table information saved in a master file and the region wise data available across the table which can be mapped and joined to perform a joined querying operation

14

Merge

Combine data from multiple data source like master and child employee table and get result in single dataset.

15

Merge Join

Combine data from multiple data source like master and child employee table and get result in single dataset. Can use any type of join like inner, outer, left , right etc

16

Multicast

Similar to the conditional split but this splits across all the parts

17

OLE DB Command

Used when we need to do updates to all the rows of a table like update If a message sent to the entire customer who have made a payment today.

18

Percentage Sampling

Can be used in cases like the package should have access to only limited data.

19

Pivot

When data fetched from the table and do some formatting to show in the front end we can use it.

20

Row Count

Any point to log the count of the number of customers so we can get the count using this

21

Row Sampling

Same as Percentage Sampling.

22

Script Component

Used for places where we need to use framework specific assemblies.

23

Slowly Changing Dimension

When we need to use some historic dimensions of data

24

Sort

To make some sorting to get the desired result. Sorting like customer who made the highest payment in a particular day.

25

Term Extraction

Used to get a data from a large set of data and get the extracted output in a formatted set.

26

Term Lookup

Used to get a data from a large set of data and get the extracted output in a formatted set.

27

Union All

Used to get data from different data sources and get in a single dimensional format.

28

Unpivot

Restructuring the format of the data for normalizing the input prior to loading.

Conclusion:

In this article we have seen some real time examples where we use the transformations, these are some of the real time usage which I came across.

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Comments (2) -

pravin kadam
pravin kadam
12/27/2012 11:03:28 AM #

plaze send me ssrs & ssis notes sir

kalisha
kalisha
2/10/2013 5:54:11 AM #

plese send me more examples in ssis

plese give me sir

ur notice very clearly very nice  

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Advertisement

Random Posts

Most Viewed

BlogStatistic

Total posts: 617This year: 4This month: 2This week: 0Comments: 302

Month List

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter