Data Flow Nugget Transform
The "Data Flow Nugget Transform" allows you to configure a reusable data flow transform package.
Nugget Package Requirements
Your data flow nugget package must meet a few requirements to make it available for reuse in the "Data Flow Nugget Transform".
• Must have a data flow task - The "Data Flow Nugget Transform" reads a package looking for data flow tasks during setup. You must choose a package that contains a data flow task that follows the next two rules.
• The data flow task must have a placeholder source - The placeholder source will be used to define the input columns for the data flow nugget.
• The data flow task must have a terminator destination - The terminator destination is used to define the columns that will be returned from the data flow nugget package. You must select the checkbox in the "include" column for the "Data Flow Nugget Transform" to detect that is an output column.
Setting up a Data Flow Nugget
1. After you have setup a package to be used that meets the three requirements, you will choose the package in the transform editor's window. The child package can be located either on a file system, SQL server, or an SSIS Catalog. If you choose SQL Server or SSIS Catalog, you must select a connection manager that connects to the server where the package is stored.
2. Next you will map the input columns. Any of the columns that are defined in the placeholder source need to be mapped in the "Input Columns" step. The "Nugget Column" displays a drop down that contains all of the columns defined in the placeholder source. If any columns defined in the placeholder source are not mapped in the "Data Flow Nugget Transform" you will receive an error telling you to map the columns.
3. In the next step for the output columns, you will be shown the columns defined in the Terminator Destination component of your data flow nugget package. What is shown depends on whether you have an asynchronous component in your nugget package. With an asynchronous component, you will be show all of the columns marked as "Include" in the terminator destination in the "Output Columns from Nugget" grid.
4. On the variables page, you are able to map the values of variables from the package that contains the "Data Flow Nugget Transform" to the variables that exist in the child nugget package.
5. The last step is to map the connection managers from the package that contains the "Data Flow Nugget Transform" to the connection managers that exist in the child nugget package.
Executing Data Flow Nugget Transforms
To execute a package that contains a "Data Flow Nugget" you will need to use the "Advanced Execute Package Task". Here you will see the correlation between three packages that are needed to execute "Data Flow Nuggets"
Package 1 - The Nugget Package
The nugget package is the reusable package that contains any of the reusable logic that will be executed within a "Data Flow Nugget Transform".
Package 2 - The Data Flow Nugget Transform Package
This package will contain the "Data Flow Nugget Transform" that will reference Package 1.
Package 3 - The Advanced Execute Package Task Package
This package will contain an "Advanced Execute Package Task" that references Package 2 as it's "Child Package".
Setup and Use Data Flow Nugget Transform
Use Data Flow Nugget Transform with Asynchronous Components