In this article we will show you, How to create a SSIS Parent Child Package Configuration with example. We already explained the list of Package Configurations in our previous article. So, please refer SSIS Package Configuration to understand the same.
We have File System Task Folder inside our D drive, and it has four text files. To demonstrate this configuration, we will load data from text files to SQL Server table. Following screenshot will show you the data inside the Employee1.txt file.
Following screenshot show you the data inside the Employee2.txt file.
Below screenshot shows the data inside the Employee3.txt file.
Following screenshot show you the data inside the Employee4.txt file.
SSIS Parent Child Package Configuration Example
In this example first, we will configure Parent Package, next child package, then we will create Parent package variable (package configuration). And our goal to access the Parent Package variable (with data) from the child package
Configure Parent Package in SSIS
In this package, we will enumerates those Employee text files, and save the file names in a string variable. In order to do so, First Drag and drop the Foreach Loop Container into the Control Flow region as shown below
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 on the Browse button to select the folder name from our file system.
From the below screenshot you can see, we are selecting 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
Our goal to store the file names in a variable. In order to do that, go to Variable Mappings section and select the variable and assign it to index 0 as we shown in below screenshot. Once you are done, Click OK to finish configuring the Foreach Loop Container.
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
Double click on it will open the data flow tab. Next, Drag and drop Flat File Source and OLE DB Destination from toolbox to data flow region.
Double click on Flat File Source will open the Flat File Source Editor to configure the connection manager settings. If you had created Flat File Connection Manger then select it otherwise, click on the New button will open the Flat File Connection manager Editor.
From the below screenshot you can observe that, we are selecting Employee1 text file from our File System Task folder.
Click OK button to close the Flat File Source Connection manager Editor and Click on Flat File Source Editor columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.
Double click on OLE DB Destination will open the OLE DB Destination Editor. Select the OLE DB Connection manager and select [SSIS ForEach Loop File numerator] table present in the [SSIS Tutorials] Database to insert the new records.
Click on Mappings tab to check whether the source columns are exactly mapped to the destination columns.
Here our Child package will take the Employe1 text file frm file system, and save it in the SQL Server table. But our intention is to load all the text files (Employee1, Employee2, Employee3 and Employee4) present in the file system to table. For this, Please select the Flat File Connection Manger from the Connection Managers pane and right-click on it to select the Properties option from the Context menu.
Click on the Expressions property will open the property Expressions Editor. Here we have to configure the Connection String settings so, select the Connection manger property from drop down box and click on the … button as shown below.
Once you click on the … button, it will open the Expression Builder to build the expression. Here we are dragging the FileName variable as expression
Now, we have to specify the variable value -> location, files name, extension
SSIS Package Configuration Using Parent Package Variable
Right-click on the control flow region will open the context menu. Please select the Package Configurations.. option from the context menu
Once you select the Package Configurations.., a new window called Package Configurations Organizer will be opened. Please check mark the Enable Package Configurations to enable the configurations. Next, click on the Add button to add new configuration.
Clicking the Add button, will open a wizard. First page is the welcome wizard, please check mark Don’t shown this page again to avoid this page.
Select Configuration type: This is the page that we have to use to select the configuration type. By default XML configuration file is selected by the SSIS. In this example we are explaining about SSIS Parent Package variable configuration. So, let me select the Parent Package Variable as the configuration type, and specified the Parent variable name. remember, variable name should be exactly same as the parent package variable name.
Select Target Property: You have to specify the target value here. Here we have to assign the Parent package variable (FileName) to File System Connection String variable (ChildFileName) value. It means, default value of ChildFileName will be replaced with the Parent package variable (FileName) at run time.
Let me rename the Configuration name as Parent Child Package Configuration, and Click Finish button to close the wizard.
Now you can see our newly created Parent Package Variable configuration.
Next, Go to the Parent Package, and Drag and drop the Execute Package Task from toolbox to Foreach Container.
Double click on the Execute Package Task to configure the package. Go to the package tab to configure 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 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 Execute packages in File System article to understand the steps involved in executing the packages from file system. Next, refer SSIS Execute Package Task Project Reference article for calling packages inside the same project and Execute Packages in SQL Server using SSIS Execute Package Task for calling packages in SQL Server.
From the below screenshot you can observe, we are selecting Child Package from the list of SSIS Packages.
Click ok to select the file. If the package is secured with password (In general Yes) then provide appropriate password.
Click OK to finish configuring the parent package. Let us Run the package
From the above you can observe that, our Execute Package Task is calling Child Package.
Let us open the SQL Server Management Studio and check the result.
Let me show you, What will happen if we delete the Parent Package variable configuration.
Now you ca that the task has failed
Thank You for Visiting Our Blog
