LatestTechnology

SSIS Data Flow Transformations Examples

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 here are some real-world scenarios for each and every transformation. There are 28 SSIS Data Flow Transformations Examples. Check out below.

28 SSIS Data Flow Transformations Examples (Part 1)

S No Transformation Real Time Examples
1AggregateSumming / Averaging a total of the products purchased by a customer online to produce the final amount.
2AuditFor audit purpose, when we need to audit the logs to send to DBA’s for weekly or monthly auditing.
3Character MapFor sending mails (do some manipulations) to the end users to do some formatting we can use this task.
4Conditional SplitMorning 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.
5Copy ColumnMorning 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
6Data ConversionDaily monitoring of the input files and data to have proper datatype before mapping it to the table then we can use this task.
7Data Mining QueryEvaluating the input data against the analysis model to get a proper set.
8Derived ColumnAdding a title of courtesy (Mr., Mrs., Dr, etc) before the name and removing the trailing and ending spaces.
9Export ColumnWhen 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
10Fuzzy GroupingMatching the name of a customer with master and child table and use it to group and get the desired set

SSIS Examples Part 2

11Fuzzy LookupMatching the name of a customer with master and child table and use it to group and get the desired set
12Import ColumnWhen 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
13LookupEmployee 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
14MergeCombine data from multiple data source like master and child employee table and get result in single dataset.
15Merge JoinCombine 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
16Multicast Similar to the conditional split but this splits across all the parts
17OLE DB CommandUsed 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 SamplingCan be used in cases like the package should have access to only limited data.
19Pivot When data fetched from the table and do some formatting to show in the front end we can use it.
20Row CountAny point to log the count of the number of customers so we can get the count using this

SSIS Examples Part 3

21Row Sampling Same as Percentage Sampling.
22Script ComponentUsed for places where we need to use framework specific assemblies.
23Slowly Changing DimensionWhen we need to use some historic dimensions of data
24SortTo make some sorting to get the desired result. Sorting like customer who made the highest payment in a particular day.
25Term ExtractionUsed to get a data from a large set of data and get the extracted output in a formatted set.
26Term LookupUsed to get a data from a large set of data and get the extracted output in a formatted set.
27Union AllUsed to get data from different data sources and get in a single dimensional format.
28UnpivotRestructuring the format of the data for normalizing the input prior to loading.

Conclusion

So, here are some real-world examples which Mergen IT uses for the transformations. Also, these are some of the realistic usages which will help to build the business.

Show More

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Articles

Back to top button
Close