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.
- 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.
- In the Source/Destination Component Properties: set “ValidateExternalMetadata” to “False”
- Do this from within the Advanced Editor.
- 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.
- 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.
- 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.
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.
- Use a Foreach Loop Container to process any number of files, tables, entities, etc.
- Use a mapping table to specify how to match up columns/fields (if the names don’t match).
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:
- Configure a package that backs up a single file.
- Add a variable for the filename and an expression to pass the filename to the package from outside the package.
- Add a Foreach Loop Container to iterate through multiple files, backing up each one.
- Add the mapping table lookup for columns that need to be explicitly mapped (ie. their source and destination names don’t match).