Passing Parameters from Parent to Child Packages in SSIS

This document explains how to pass parameters from a parent package to a child package in SSIS. The scenario involves ingesting files from a folder into relevant database tables, with each file requiring a separate package. In each package, a data flow is created to move data from the file to the database table. The file name is used as the package name and database table name for automation purposes. Additionally, a main_control package is created to loop through each file in the folder and call the respective child package from the loop.

Solution

        1.       Create a project connection manager named "STG" for the database connection.

        2.       In the child package:

a.       Create a package connection manager named "CSV" for the flat file connection.

b.       Create a parameter named "CurrentFile" and assign a dummy value (e.g., 'place_holder.csv').

c.       Create necessary tasks to ingest the file (e.g., truncate table, data flow, archive file, etc.).

        3.       In the main_control package:

a.       Create a Foreach Loop container and use the Foreach File Enumerator.

b.       Create a variable named "current_file".

c.       In the Variable Mappings of the Foreach Loop container, map the "current_file" variable to index 0.

d.       Add an Execute Package task within the Foreach Loop container.

                                                               i.      In the Parameter bindings, select "CurrentFile" as the child package parameter and select "User::current_file" as the binding parameter or variable.

                                                             ii.      Add an expression for the PackageName property and specify the following expression: @[User::current_file] + ".dtsx".

Conclusion

This document has provided instructions on passing parameters from a parent package to a child package in SSIS. By following the outlined steps, you can successfully ingest files from a folder into relevant database tables using separate packages for each file. The file name is utilized for package and table naming, streamlining the automation process.