MSBI # 38 – SSIS # 16 – Data Flow Task # 2 – Introduction to Data Flow Task {Part – I}

Hi friends ,

As I have already started some basic explanation of Data Flow Task on my last post :

• MSBI # 36 – SSIS # 15 – Control Flow Tasks # 8 – Data Flow Task # 1 « (B)usiness (I)ntelligence Mentalist

In this post we will look further in base of Data Flow Task as follows :

• Basics of Data Flow Task
• Some Facts about Data Flow
• Data flow tab on fly
• Data flow tab
• How to Use Data Flow Task
• Basic theory for Control flow task
• Category of Data flow task
• Reference for Data Flow Task

Lets Discuss each topic one by one

Basics of Data Flow Task

• Streaming
• Unlink control flow, multiple components can process data at the same time
• Smallest unit of the data flow is a component
• Data flows move data, but are also tasks in the control flow, as such, their success or failure effects how your control flow operates
• Data is moved and manipulated through transformations
• Data is passed between each component in the data flow
• Data flow is made up of source(s), transformations, and destinations.

Some Facts about Data Flow

• The data flow task in SSIS (SQL Server Integration Services) sends data in series of buffers.
• This is bounded by DefaultBufferMaxRows and DefaultBufferMaxSize, two Data Flow properties.
• They have default values of 10,000 and 10,485,760 (10 MB), respectively. That means, one buffer will contain either 10,000 rows or 10 MB of data, whichever is less.
• You can adjust these two properties based on your scenario. Setting them to a higher value can boost performance, but only as long as all buffers fit in memory.
• The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and lets the user transform, clean, and modify data as it is moved.
• Addition of a Data Flow task to a package control flow makes it possible for the package to extract, transform, and load data.
• A data flow consists of at least one data flow component, but it is typically a set of connected data flow components: sources that extract data; transformations that modify, route, or summarize data; and destinations that load data. Components are connected in the data flow by paths.
• Each path specifies the two components that are the start and the end of the path. For more information, see Data Flow Elements.
• At run time, the Data Flow task builds an execution plan from the data flow, and the data flow engine executes the plan.
• We can create a Data Flow task that has no data flow, but the task executes only if it includes at least one data flow.

Data flow tab on fly / How to Use Data Flow Task

Just drag out Data flow task from Control Flow Task

Then to see all component and to perform Data Transformation just click on Data flow tab after selecting Data Flow Task

Now you can have All Data Flow Components

After Dragging out we need to just press on Data Flow Tab and Create Required Transformation’s

Data flow tab

Folioing is entire control flow tool set list which is big and frankly I got really hard job to collect it in one single peace

Basic theory for Data flow task

The Following theory is  directly from MSDN

The following diagram shows a Data Flow task with one data flow.

A Data Flow task can include multiple data flows.

If a task copies several sets of data, and if the order in which the data is copied is not significant, it can be more convenient to include multiple data flows in the Data Flow task.

However, the data flow engine determines order of execution when there are multiple data flows within one data flow task.

Therefore, when order is important, the package should use multiple Data Flow tasks, each task containing one data flow. You can then apply precedence constraints to control the execution order of the tasks.

The following diagram shows a Data Flow task that has multiple data flows.

Category of Data flow task

Various task exist in the following category

• Data Flow Sources
• Data Flow Transformations
• Data Flow Destination 

Reference for Data Flow Task

• MSBI # 36 – SSIS # 15 – Control Flow Tasks # 8 – Data Flow Task # 1 « (B)usiness (I)ntelligence Mentalist
• http://msdn.microsoft.com/en-us/library/ms141122.aspx
• http://msdn.microsoft.com/en-us/library/ms170425.aspx
• http://msdn.microsoft.com/en-us/library/ms135997.aspx

Thanks for visiting my blog !!

Hope you have understood basic aspect of Data Flow Task and ready to use every aspects for same

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource # 24: Sept 08–Sept 12 « Dactylonomy of Web Resource