Data Flow Task

SQL Server 2005, 2008, 2008R2, 2012, 2014

SQL Server 2005, 2008, 2008R2, 2012, 2014

All Downloads ----- Join VIP ----- Questions?

Overview

Data Flow Task Plus is an SSIS control flow task and it is an extension of the standard Microsoft Data Flow Task.

Data Flow Task Plus (1.4) allows setup of dynamic data flows. The dynamic setup options can be controlled with parameters from outside. This feature lessens the need to manually open and modify the data flow design when new source and destination columns have to be accommodated.

Data Flow Task Plus also supports features similar to the features implemented for Script Task Plus. Data Flow Task Plus allows exporting of data flow logic and reuse in other packages. Keeping your SSIS data flow logic separate from your package helps you keep track of the changes independently in your source control system. Data Flow Task Plus also provides a customizable user interface for setup of your data flow. You customize the data flow setup dialog by implementing an SSIS script, which is part of the data flow task.

These features will help you be more productive and allow you to componentize your common data flow logic.

Demonstration

Demonstration VideoDemonstration Video

Setup (1.4)

The task setup is similar to the Script Task Plus dialog. It has the following user interface elements:

  • Setup tab - select data flow initialization parameters. These data flow parameters will be initialized before the data flow is executed. The page configuration is prepared with .NET attributes in the initialization script.
  • Dynamic tab - specifies dynamic data flow options. For details check below.
  • Advanced tab - specifies advanced task options.
    • General page - specifies the data flow task name and description.
    • Script page - specifies the data flow task script, which is used for Setup tab customization.
    • Expressions page - specifies standard SSIS expressions.
  • Export Data FlowExport Data Flow - exports the SSIS data flow to an external file (.dfx).
  • Link Data FlowLink Data Flow - links to the SSIS data flow from an external file (.dfx). The link can be removed by pressing the button again.

Setup (1.3 and older)

The task setup is similar to the Script Task Plus dialog. It has the following user interface elements:

  • General page - specifies the data flow task name and description.
  • Initialize page - selects the data flow initialization parameters. These data flow parameters will be initialized before the data flow is executed. The page configuration is prepared with .NET attributes in the initialization script.
  • Script page - specifies the data flow task script, which is used for Initialize page customization.
  • Expressions page - specifies standard SSIS expressions.
  • Export Data FlowExport Data Flow - exports the SSIS data flow to an external file (.dfx).
  • Link Data FlowLink Data Flow - links to the SSIS data flow from an external file (.dfx). The link can be removed by pressing the button again.

Dynamic Data Flow (1.4)

The dynamic data flow documentation is published here.

How to build the data flow setup

The initialization and user interface for a data flow is defined by implementing properties on the script EntryPoint class. The documentation and samples of Script Task Plus for defining parameters is relevant to the Data Flow Task Plus initialization setup, so we will not repeat it here. Here you will find a description of the DataFlowSetting .NET attribute, used to apply values to specific data flow component parameters.

  • Go to the Toolbox and open the Choose Toolbox Items ... dialog. Press the SSIS Control Flow Items tab and select Data Flow Task Plus.

    Add ReferenceAdd Reference

  • Drag Data Flow Task Plus into your control flow package and open the Design Script IDE.
  • Add a reference in the script project to CozyRoc.SSISPlus

    Add ReferenceAdd Reference

  • Include Imports CozyRoc.SqlServer.SSIS.Attributes in the script. It is also recommended that you include Imports System.Drawing.
    Imports System.Drawing
    Imports CozyRoc.SqlServer.SSIS.Attributes
    
    Public Class ScriptMain
  • Now you are ready to define your first Data Flow Task Plus Parameter.

DataFlowSetting .NET attribute

The DataFlowSetting .NET attribute defines a list of references to different component properties in the SSIS data flow. It defines the link between the initialization parameter and a specific SSIS data flow component parameter. You can apply one initialization parameter to multiple data flow components and properties. Each reference is a text string with separate sections. Each section is separated by a "." dot. Every reference starts with a component, followed by a "keyword" specifying the parameter type. Here is a list of sample DataFlowSetting .NET attribute reference types which are supported currently by the Data Flow Task Plus component:

  • DataReader Source.Property.PreCompile - set property PreCompile.
  • DataReader Source.Connection.IDbConnection - set connection IDbConnection.
  • DataReader Source.Column.Input.DataReader Input.DateKey - set input column DateKey in DataReader Input input.
  • DataReader Source.Column.Output.DataReader Output.DateKey - set output column DateKey in DataReader Output output.
  • DataReader Source.Column.OutputExternal.DataReader Output.DateKey - set external output column DateKey in DataReader Output output.

where DataReader Source is a reference to a data flow component.

In SSIS, different data flow elements like components, properties, connections, and columns can be referenced either by name or ID. The DataFlowSetting .NET attribute reference supports both.

Data Flow property setup

  • Sample script to set up the CommandTimeout property of the DataReader Source component.
    <DataFlowSetting(New String() {"DataReader Source.Property.CommandTimeout"})> _
    Public Property CommandTimeout() As Integer
        Get
            CommandTimeout = m_commandTimeout
        End Get
        Set(ByVal value As Integer)
            m_commandTimeout = value
        End Set
    End Property

Data Flow connection setup

  • Sample script to set up an IDbConnection connection of the DataReader Source component.
    <Connection("ConnectionType")> _
    <DataFlowSetting(New String() {"DataReader Source.Connection.IDbConnection"})> _
    Public Property Connection() As String
        Get
            Connection = m_connection
        End Get
        Set(ByVal value As String)
            m_connection = value
        End Set
    End Property
    
    <Browsable(False)> _
    Public ReadOnly Property ConnectionType() As String
        Get
            ConnectionType = "OLEDB"
        End Get
    End Property

Data Flow column setup

  • Sample script to set up a column with ID 3018 in DataReader Output output of DataReader Source component.
    <DataFlowSetting(New String() {"DataReader Source.Column.Output.DataReader Output.3018"})> _
    Public Property Column() As String
        Get
            Column = m_column
        End Get
        Set(ByVal value As String)
            m_column = value
        End Set
    End Property

Samples

  • The sample package used from the demonstration video by Jamie Thomson can be downloaded from here.
  • Here is a sample package contributed by Paul McMillan, demonstrating dynamic data flow from the source to the destination database and driven by metadata.
  • Introducing ETL Assistant – A Dynamic, Templated Approach to SSIS by Eric Whitley