Overview of Data Flow Task Plus Configuration

If this is your first time using Data Flow Task Plus to create a dynamic package, please be sure to watch the demonstration videos which illustrate how to configure a package using Data Flow Task Plus. No matter what sources and destinations you’re working with, you can use the following checklist to set up a dynamic package. The steps are intended to be generic and not specific to a particular source or destination.

  • Use Data Flow Task Plus in place of the standard Data Flow Task.
    • It can be found in the SSIS Toolbox next to the Control Flow canvas.

Toolbox

  • In the data flow for each component or connection manager: delete all statically-defined columns and replace them with the THUNK_COLUMN.
    • This is done from the Advanced Editor for components.
    • To open the Advanced Editor, right-mouse click on the component and select “Show Advanced Editor” from the list.
    • For some sources/destinations (e.g. Flat Files), this step must be done in the Connection Manager.

THUNK_COLUMN

  • In the Source/Destination Component Properties: set “ValidateExternalMetadata” to “False”
    • Do this from within the Advanced Editor.

ValidateExternalMetadata

  • After removing the source/destination columns, always edit the components using the Advanced Editor.
    • Using the regular editor can cause all the columns to be refreshed and they will need to be removed again.

Advanced Editor

  • In the Data Flow Task Plus Properties: set “Delay Validation” to “True”.
    • To open the Properties, right-mouse click on Data Flow Task Plus and choose “Properties” from the list.

DelayValidation

  • Enable the dynamic capability for the source and destination components in Data Flow Task Plus.
    • Get to this screen by opening the editor for Data Flow Task Plus and clicking on the “Dynamic” tab.

Dynamic


Other Configuration Considerations

  • To maximize control from outside the package, use variables and expressions for names of elements of the package such as tables, files, entities, worksheets and resources.

Expression

  • Use a Foreach Loop Container to process any number of files, tables, entities, etc.

For Each Loop

  • Use a mapping table to specify how to match up columns/fields (if the names don’t match).

Mapping Table


If this is the first time you’ve worked with Data Flow Task Plus, we suggest that you start with a simple package and gradually add each element to it in a step by step fashion. Here’s an example of the iterations you might go through to create a package that needs a mapping table and is backing up many files into a database:

  1. Configure a package that backs up a single file.
  2. Add a variable for the filename and an expression to pass the filename to the package from outside the package.
  3. Add a Foreach Loop Container to iterate through multiple files, backing up each one.
  4. Add the mapping table lookup for columns that need to be explicitly mapped (ie. their source and destination names don’t match).