.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Real time Examples of Data Flow Transformations in SSIS - SQL Server Integration Services - Part 49

Posted By:Karthikeyan Anbarasan       Posted Date: May 04, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

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 in SQL Server Integration Services ( SSIS )
 

 

Introduction:


In my series of articles there are a few scenarios where we need to take care of some real-world samples and examples to help explain concepts to the end users. So I came up with this article to show some real-world scenarios for each and every transformation. There are 28 data flow transformations and I grouped them below to give some realistic idea from my working experience with those controls.


You can also read my other articles on SSIS from here. 


The 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-world examples where we use the transformations; these are some of the realistic usages which I have came across.

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend