Adding the Data Flow Task Programmatically

SQL Server Data Tools (SSDT) includes a task called the Data Flow task, which is represented by the Microsoft.SqlServer.Dts.Pipeline.Wrapper namespace in the object model. The Data Flow task is a specialized, high-performance task, dedicated to transforming and moving data during package execution. Like other tasks, the Data Flow task is wrapped by the TaskHost object, and from the perspective of the run-time engine, this task is just another task in the package. However, the data flow contains additional objects called data flow components. These components are the components that make data move from a source to a destination, sometimes through a transformation. The components define both the direction of movement and how data is transformed. Configuring the Data Flow task involves adding components to the task, and then connecting them to establish the flow of data and achieve the intended transformation.

There are three types of components within a Data Flow task: Data Flow Sources, Data Flow Transformations, and Data Flow Destinations, shown in that order within the SSIS Designer toolbox. These types are also referred to more simply as sources, transformations, or destinations. As implied by the names, data flows from a source to a transformation, and then to a destination. This is a simplistic description of the data flow to illustrate the concept, but the Data Flow task is flexible and powerful enough to handle multiple sources, and to connect together many transformations that send output to multiple destinations.

The Data Flow task is added to a package the same way other tasks are added. After the task has been added, it is configured by adding components to the data flow task, and configuring and connecting components in the task.

Sample

The following code sample shows how to add a Data Flow task to a package. This example requires a reference to the assemblies Microsoft.SqlServer.PipelineHost, Microsoft.SqlServer.DTSPipelineWrap, and Microsoft.SqlServer.ManagedDTS.

using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

namespace Microsoft.SqlServer.Dts.Samples
{
  class Program
  {
    static void Main(string[] args)
    {
      Package p = new Package();
      Executable e = p.Executables.Add("STOCK:PipelineTask");
      TaskHost thMainPipe = e as TaskHost;
      MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe; 
    }
  }
}

Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Module Module1

  Sub Main()

    Dim p As Package = New Package()
    Dim e As Executable = p.Executables.Add("STOCK:PipelineTask")
    Dim thMainPipe As TaskHost = CType(e, TaskHost)
    Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

  End Sub

End Module

External Resources

Blog entry, EzAPI – Updated for SQL Server 2012, on blogs.msdn.com.

See Also

Concepts

Discovering Data Flow Components Programmatically