Before anyone starts
learning/designing dataflow in SSIS, one should know what is been
offered by Microsoft in SSIS toolbox. Most people dont know how many
transformations are actually available in dataflow and they choose some
alternative workaround to performs operations which can be done easily
using available transformatoins. In this article i am going to provide
just a brief of all the transformations available in dataflow and
categorise them. After reading this article one should be aware of what
is been offered in SSIS designer, and it will be helpfull for all those
bignners to become aware of available transformations.
Also Categorization helps us to remember transformations, as there are lot.. its difficult to remember :)
Ok so lets start...
Transformations are divided into four categories
1. Row level transformations
2. Rowset transformations
3. Multiple Input-output transformations
4. Advance Transformations.
Row Level Transformations
Row level transformations performs operations on data row by row. These transformatoins are most common in use and can be configured easily. Following are the transformations which comes under this category.
Rowset Transformation performs operations on multiple rows, these kind of transformations are memory intensive, but can be functionally very usefull. Below are the list of transformations which comes under this category.
As the category name implies, these transformations requires multiple input and it generates multiple outputs.
These transformations provides functionality to combine or branch data and give the data flow the overall ability to process data from one or more sources to one or more destinations.
Below is the list of transformations which fall under this category..
The transformations fall under this category provides ability to perform advance operations on data in pipeline of SSIS package.
Thanks..
Also Categorization helps us to remember transformations, as there are lot.. its difficult to remember :)
Ok so lets start...
Transformations are divided into four categories
1. Row level transformations
2. Rowset transformations
3. Multiple Input-output transformations
4. Advance Transformations.
Row Level Transformations
Row level transformations performs operations on data row by row. These transformatoins are most common in use and can be configured easily. Following are the transformations which comes under this category.
- Audit
- Cache Transform
- Character Map
- Copy Column
- Data Conversion
- Derived Column
- Export Column
- Import Column
- Row Count
Rowset Transformation performs operations on multiple rows, these kind of transformations are memory intensive, but can be functionally very usefull. Below are the list of transformations which comes under this category.
- Aggregate
- Percent Sampling
- Pivot
- Row Sampling
- Sort
- Un pivot
As the category name implies, these transformations requires multiple input and it generates multiple outputs.
These transformations provides functionality to combine or branch data and give the data flow the overall ability to process data from one or more sources to one or more destinations.
Below is the list of transformations which fall under this category..
- Conditional Split
- Lookup
- Merge
- Merge Join
- Multicast
- UnionAll
The transformations fall under this category provides ability to perform advance operations on data in pipeline of SSIS package.
- OLE DB Command
- Slowly Changing Dimension
- Data Mining Query
- Fuzzy Grouping
- Fuzzy Lookup
- Script Component
- Term Extraction
- Term Lookup
Thanks..
No comments:
Post a Comment