Floating FB popout byF5debug

SQL Server Integration Services (SSIS) – Part 30 – Data Flow Transformations in SSIS

 

This is part 30 of the series of article on SSIS and in this article we are going to see what is Data Flow Transformations in SSIS and the list of controls that are provided in the data flow transformations followed by the series on each of the control and the usage of the controls.

Introduction

In this article we are going to see on what Data Flow Transformations in SSIS and the list of controls that are provided in the data flow transformations followed by the series on each of the control and the usage of the controls

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

[more]

Steps:

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see on what exactly the data flow transformations are and where to locate the controls under this transformation and the usage of those transformations. After opening a new project just move to the Dataflow tab in the designer window and you can see the list of Data Transformations as shown in the below image.

clip_image001

Data flow transformations are helpful to do any type of manipulations across the data which are to be transferred and used in the package.

There are 28 data flow transformation controls and the list of them are as below with a small description on what for the control is used for.

S No

Transformation

Description

1

Aggregate

Aggregates and groups values

2

Audit

Adds audit information

3

Character Map

Applies string operations to character data

4

Conditional Split

Evaluates and splits up rows

5

Copy Column

Copies a column

6

Data Conversion

Converts data to a different data type

7

Data Mining Query

Runs a data mining query

8

Derived Column

Calculates a new column from existing data

9

Export Column

Exports data from a column to a file

10

Fuzzy Grouping

Groups rows that contain similar values

11

Fuzzy Lookup

Looks up values using fuzzy matching

12

Import Column

Imports data from a file to a column

13

Lookup

Looks up values in a dataset

14

Merge

Merges two sorted datasets

15

Merge Join

Merges data from two datasets by using a join

16

Multicast

Creates copies of a dataset

17

OLE DB Command

Executes a SQL command on each row in a dataset

18

Percentage Sampling

Extracts a subset of rows from a dataset

19

Pivot

Builds a pivot table from a dataset

20

Row Count

Counts the rows of a dataset

21

Row Sampling

Extracts a sample of rows from a dataset

22

Script Component

Executes a custom script

23

Slowly Changing Dimension

Updates a slowly changing dimension in a cube

24

Sort

Sorts data

25

Term Extraction

Extracts data from a column

26

Term Lookup

Looks up the frequency of a term in a column

27

Union All

Merges multiple datasets

28

Unpivot

Normalizes a pivot table

In our upcoming articles we are going to see on each of the major control and the purpose of them.

Conclusion

So in this article we have seen on what exactly Data flow transformations are and the list of available controls to perform these transformations.

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

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Random Posts

Most Viewed

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