Data Flow Task

Overview

Data Flow Task Plus is 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 supports also 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 provides also customizable user interface for setup of your data flow. You customize the data flow setup dialog by implementing an SSIS script, part of the data flow task.

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

Demonstration

Setup (1.4)

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

  • 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 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 data flow task name and description.
    • Script page - specifies data flow task script, which is used for Setup tab customization.
    • Expressions page - specifies standard SSIS expressions.
  • Export Data FlowExport Data Flow - export SSIS data flow to external file (.dfx).
  • Link Data FlowLink Data Flow - link to SSIS data flow from 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 features:

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

Dynamic Data Flow (1.4)

Data Flow Task Plus can dynamically modify source and destination components at runtime.

Supported data flow components:

Type Component
Source ADO NET, Excel, Flat File, OLE DB, XML, Data Flow, EDI
Destination ADO NET (SQL 2008 only), DataReader, Excel, Flat File, OLE DB, Recordset, SQL Server, Informix, Oracle, DB2, Data Flow.

The task constructs the extra available columns, assisted by the user-specified options below.

Enabled

Set component to be processed dynamically.

FilterType (Source)

Select filter source type. This property has the options listed in the following table.

Value Description
FilterDirect Set to a regular expression that defines the filter. Selecting the value displays the dynamic option FilterDirect.
FilterFile Select a file that contains the filter. Selecting the value displays the dynamic option FilterFile.
FilterVariable Set to a variable that defines the filter. Selecting the value displays the dynamic option FilterVariable.
FilterDirect (Source)

Type the filter regular expression.

Related Topics: Regular Expression Editor

FilterFile (Source)

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager

FilterVariable (Source)

Select an existing user-defined variable, or click <New variable...> to create a variable. The filter can be regular expression, ICollection or ADODB Recordset.

Related Topics: Integration Services Variables, Add Variable

IsMappingVariable (Destination)

Indicates whether the mapping is stored in a variable. This property has the options listed in the following table.

Value Description
True The mapping is stored in a variable. Selecting the value displays the dynamic option MappingVariable.
False The mapping is specified in a File connection manager. Selecting the value displays the dynamic option Mapping.
Mapping (Destination)

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager

MappingVariable (Destination)

Select an existing user-defined variable, or click <New variable...> to create a variable. The mapping can be specified as simple key to value text, IDictionary or ADODB Recordset.

Related Topics: Integration Services Variables, Add Variable

ColumnDelimiter (Flat File Delimited Source and Destination)

Specify flat file column delimiter.

ColumnsCountVariable (Flat File Fixed and Ragged Right Source)

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

ColumnWidth (Flat File Ragged Right Source)

Specify flat file ragged right column width.

Configuration (EDI Source)

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager

How to build 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 Data Flow Task Plus initialization setup, so we will not repeat it here. Here you will find description of DataFlowSetting attribute, used to apply parameter value to specific data flow component setting.

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

    Add ReferenceAdd Reference

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

    Add ReferenceAdd Reference

  • Include Imports CozyRoc.SqlServer.SSIS.Attributes in script. It is also recommended to 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 attribute

DataFlowSetting attribute defines a list of references to different component properties in the SSIS data flow. It defines the link between initialization parameter and specific SSIS data flow component setting. 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 with "." dot. Every reference starts with component, followed by "keyword" specifying setting type. Here is a list of sample DataFlowSetting attribute reference types, which are supported currently by 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 data flow component.

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

Data Flow property setting

  • Sample script to setup CommandTimeout property of 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 setting

  • Sample script to setup IDbConnection connection of 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 setting

  • Sample script to setup 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 can be download from here.