Data Flow Task | CozyRoc

Overview

Data Flow Task Plus is SSIS control flow task and it is an extension of the standard Microsoft Data Flow Task. The features we have implemented are very similar to the ones we have already 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.

The task setup is very 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 Flow - export SSIS data flow to external file (.dfx).
Link Data Flow - link to SSIS data flow from external file (.dfx). The link can be removed by pressing the button again.

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.

How to start?

Go to Toolbox and open Choose Toolbox Items ... dialog. Press SSIS Control Flow Items tab and select Script Task Plus.
Add 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 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