SSIS Parent Child Package Configuration

In this article, we will show you how to create an SSIS Parent Child Package Configuration with examples. We already explained the list of Package Configurations in our previous article. So, please refer to the Package Configuration to understand the same.

We have an SSIS File System Task Folder inside our D drive with four text files. To demonstrate this SSIS Parent Child Package configuration, we will load data from text files to the SQL Server table. The following screenshot will show you the data inside the Employee1.txt file.

Text File 1

The following screenshot shows the data inside the Employee2.txt file.

Text File 2

The below screenshot shows the data inside the Employee3.txt file.

Employee Text File 3

The following screenshot shows you the data inside the Employee4.txt file.

Employee Text File 4

Example for SSIS Parent Child Package Configuration

In this SSIS example, we will first configure the Parent Package, the next child package. Then we will create a Parent package variable (package configuration). And our goal is to access the Parent Package variable (with data) from the Child package

Configure Parent Package in SSIS

In this package, we will enumerate those Employee text files and save the file names in a string variable. To do so, First Drag and drop the Foreach Loop Container into the Control Flow region

Parent Package Configuration

Double click on it will open the Foreach Loop Editor to configure it. Within the Collections tab, Please select Foreach File Enumerator because we want to loop over the files present in our file system. Next, click the Browse button to select the folder name from our file system.

Foreach Loop Container Editor

From the below screenshot, you can see we are selecting the FILE SYSTEM TASK FOLDER folder in our D Drive. Within the Files section, we are using Wildcard *.txt because we want to enumerate text files.

Select txt files in Enumerator Configuration

Our goal is to store the file names in a variable. To do that, go to the Variable Mappings section, select the variable, and assign it to index 0. Once you complete, Click OK to finish configuring the Foreach Loop Container.

Foreach Loop Variable Mapping

Configure Child Package in SSIS

Drag and drop the data flow task from the toolbox to control flow region and rename it as Load data from Text Files to SQL Server table.

Child Package Configuration

Double click on it will open the data flow tab. Next, Drag and drop Flat File Source and OLE DB Destination from the toolbox to the data flow region.

Data Flow Task Configuration Fast and OLE DB

Double click on Flat File Source will open the Flat File Source Editor to configure the connection manager settings. If you have created Flat File Connection Manager, select it; otherwise, clicking on the New button will open the Flat File Connection Manager Editor.

Configure Flat File Connection Manager

From the below screenshot, you can observe that we are selecting the Employee1 text file from our File System Task folder.

Select the First Text File and Format Header and Delimiter

Click the OK button to close the Flat File Source Connection Manager Editor and Click on the Flat File Source Editor columns tab to verify the columns. In this tab, we can uncheck the unwanted columns also.

Verify Input and Output Columns

Double click on OLE DB Destination will open the OLE DB Destination Editor. Select the OLE DB Connection manager and select [Parent Child Destination] table present in the Database to insert the new records.

Configure OLE DB Destination Editor

Click on the Mappings tab to check whether the source columns are correctly mapped to the destination columns.

Choose Database Table

The Child package will take the Employe1 text file from the file system and save it in the SQL Server table. But we intend to load all the text files (Employee1, Employee2, Employee3, and Employee4) present in the file system to the table. For this, Please select the Flat File Connection Manager from the Connection Managers pane and right-click it to choose the Properties option from the Context menu.

Parent Child Package Configuration 16

Click on the Expressions property will open the Property Expressions Editor. Here we have to configure the Connection String settings. So, select the Connection Manager property from the drop-down box and click on the … button.

Parent Child Package Configuration 17

Once you click on the … button, it will open the Expression Builder to build the expression. Here we are dragging the FileName variable as an expression.

PropertyExpression Editor

Now, we have to specify the variable value -> location, files name, extension.

SSIS Package Configuration Using Parent Package Variable

For this SSIS Parent Child Package Configuration demo, right-clicking on the control flow region will open the context menu. Please select the Package Configurations.. option from it

Expression Builder for Multiple Text Files

Once you select the Package Configurations.., a new window called Package Configurations Organizer will open. Please checkmark the Enable Package Configurations to enable the configurations. Next, click on the Add button to add a new configuration.

Parent Child Package Configuration 20

Clicking the Add button will open a wizard. The first page is the welcome wizard. Please, the checkmark Don’t show this page again to avoid this page.

SSIS Parent Child Package Configuration Wizard

Select Configuration type: This is the page we must use to select the configuration type. By default, Integration Service selects the XML configuration file. In this example, we are explaining about SSIS Parent Package variable configuration. So, let me choose the Parent Package Variable as the configuration type and specify the Parent variable name. Remember, the variable name should be the same as the parent package variable name.

Please refer to the below links for the remaining configurations.

  1. Environment Variable
  2. Registry Entry
  3. Server Configuration
  4. XML Configuration File
  5. XML Configuration File in Environment Variable
SSIS Parent Child Package Configuration Type

Select Target Property: You have to specify the target value here. Here we have to assign the Parent package variable (FileName) to the File System Connection String variable (ChildFileName) value. It means the default value of ChildFileName is replaced with the Parent package variable (FileName) at run time.

SSIS Parent Child Package Configuration Target Property

Let me rename the SSIS Configuration name as Parent Child Package Configuration. And then click the Finish button to close the wizard.

SSIS Parent Child Package Configuration Name

Now you can see our newly created Parent Package Variable configuration.

SSIS Parent Child Package Configuration Organizer

Next, Go to the Parent Package, and Drag and drop the Execute Package Task from the toolbox to Foreach Container.

SSIS Parent Child Package Configuration 26

Double click on the Execute Package Task to configure the package. Go to the package tab to set the package location and connection string. In this example, We are executing the package located in the file system. So, Please change the ReferenceType to External reference, and select the file system as a package source.

Next, click on the New Connection to open the File Connection Manager Editor to select the file in the file system.

TIP: Please refer to Execute packages in File System article to understand the steps involved in executing the packages from the file system. Next, see Execute Package Task Project Reference article for calling packages inside the same project and Execute Packages in SQL Server for calling packages in Server.

SSIS Parent Child Package Configuration 27

From the below screenshot, you can observe we are selecting Child Package from the list of Packages.

Choosing the Child Package from File System

Click OK to select the file. If the package is secured with a password (In general, Yes), provide an appropriate password.

SSIS Parent Child Package Configuration Connection

Click OK to finish configuring the parent package. Let us Run the SSIS Parent Child Package Configuration package.

Run SSIS Parent Child Package Configuration

From the above, you can observe that our Execute Package Task is called Child Package.

Run SSIS Parent Child Package Configuration

Let us open the Management Studio and check the result.

Destination Table Data

Let me show you, What will happen if we delete the Parent Package variable configuration.

SSIS Parent Child Package Configuration 34

Now you can see that the task has failed

Parent Child Package Failed to Run