Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs

SSIS Parent Child Package Configuration

by suresh

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

We have SSIS File System Task Folder inside our D drive, and it has 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.

SSIS Parent Child Package Configuration 1

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

SSIS Parent Child Package Configuration 2

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

SSIS Parent Child Package Configuration 3

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

SSIS Parent Child Package Configuration 4

Example for SSIS Parent Child Package Configuration

In this SSIS example, first, we will configure the Parent Package, the next child package. Then we will create a 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 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

SSIS Parent Child Package Configuration 5

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.

SSIS Parent Child Package Configuration 6

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

SSIS Parent Child Package Configuration 7

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

SSIS Parent Child Package Configuration 8

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

SSIS Parent Child Package Configuration 9

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.

SSIS Parent Child Package Configuration 10

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 Manager, select it; otherwise, click on the New button will open the Flat File Connection Manager Editor.

SSIS Parent Child Package Configuration 11

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

SSIS Parent Child Package Configuration 12

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.

SSIS Parent Child Package Configuration 13

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.

SSIS Parent Child Package Configuration 14

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

SSIS Parent Child Package Configuration 15

Here our 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 on it to choose the Properties option from the Context menu.

SSIS 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.

SSIS 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

SSIS Parent Child Package Configuration 18

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

SSIS Package Configuration Using Parent Package Variable

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

SSIS Parent Child Package Configuration 19

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.

SSIS Parent Child Package Configuration 20

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

SSIS Parent Child Package Configuration 21

Select Configuration type: This is the page that we have to use to select the configuration type. By default, SSIS 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 specified the Parent variable name. Remember, the variable name should be the same as the parent package variable name.

SSIS Parent Child Package Configuration 22

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 the default value of ChildFileName replaced with the Parent package variable (FileName) at run time.

SSIS Parent Child Package Configuration 23

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

SSIS Parent Child Package Configuration 24

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

SSIS Parent Child Package Configuration 25

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 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.

SSIS Parent Child Package Configuration 27

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

SSIS Parent Child Package Configuration 28

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

SSIS Parent Child Package Configuration 29

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

SSIS Parent Child Package Configuration 30

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

SSIS Parent Child Package Configuration 32

Let us open the SQL Server Management Studio and check the result.

SSIS Parent Child Package Configuration 33

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

SSIS Parent Child Package Configuration 35

Placed Under: SSIS

  • What is SSIS
  • Install SQL Server Data Tools
  • Learn SSIS in 28 Days
  • Create a SSIS Project
  • Create SSIS Package
  • SSIS Connection Manager
  • OLE DB Connection Manager
  • SSIS ADO Connection Manager
  • ADO.NET Connection Manager
  • SSIS Cache Connection Manager
  • SSIS Excel Connection Manager
  • SSIS File Connection Manager
  • SSIS FTP Connection Manager
  • SSIS SMO Connection Manager
  • Source Assistance in SSIS
  • Flat File Source in SSIS
  • OLE DB Source in SSIS
  • Excel Source in SSIS
  • ADO.NET Source in SSIS
  • SSIS FLAT FILE Destination
  • SSIS OLE DB Destination
  • SSIS ADO.NET Destination
  • SSIS Transformations
  • SSIS Audit Transformation
  • SSIS Aggregate Transformation
  • SSIS Aggregate Advanced Mode
  • SSIS Cache Transformation
  • SSIS Character Map
  • SSIS Conditional Split
  • SSIS Copy Column
  • SSIS Data Conversion
  • SSIS Derived Column
  • SSIS Export Column
  • SSIS Fuzzy Grouping
  • SSIS Fuzzy Lookup
  • SSIS Import Column
  • SSIS Lookup Introduction
  • Lookup – OLE DB Connection
  • SSIS Lookup in Full Cache Mode
  • SSIS Lookup – Case Sensitivity
  • SSIS Merge Transformation
  • SSIS Merge Join Transformation
  • SSIS Merge Join – Left Outer Join
  • SSIS Merge – Right Outer Join
  • SSIS Merge Join – Full Outer Join
  • SSIS Multicast Transformation
  • SSIS OLEDB Command
  • OLEDB Command – Delete Data
  • OLEDB Command-Update Data
  • SSIS Percentage Sampling
  • SSIS Pivot Transformation 2008
  • SSIS Pivot Transformation
  • SSIS Row Count Transformation
  • SSIS Row Sampling
  • Script Component as Source
  • Script Component as Destination
  • SSIS Script as Transformation
  • SSIS Sort Transformation
  • SSIS SCD Type 0
  • SSIS SCD Type 1
  • SSIS SCD Type 2
  • SSIS Term Lookup
  • SSIS Term Extraction Intro
  • Term Extraction – Extract Nouns
  • SSIS Extract Noun Phrases
  • Extract Nouns & Noun Phrases
  • Term Extraction – Exclusion Tab
  • SSIS Unpivot Transformation
  • SSIS Union All Transformation
  • SSIS For Loop Container
  • SSIS ForEach File Enumerator
  • SSIS ForEach SMO Enumerator
  • SSIS ForEach Variable
  • SSIS Foreach NodeList
  • Foreach ADO.NET Schema Rowset Enumerator
  • SSIS Bulk Insert Task
  • SSIS Data Profiling Task
  • Execute T-SQL Statement Task
  • SSIS Execute SQL Task Intro
  • SSIS Execute SQL Task Example
  • Execute SQL Task- Single Rowset
  • Execute SQL Task – Full Row Set
  • SSIS Execute Package Task
  • Execute Packages in SQL Server
  • Execute Packages in File System
  • SSIS Execute Package Project Reference
  • SSIS File System Task
  • SSIS File System Task- Copy Files
  • File System Task-Copy Directory
  • File System Task – Delete Files
  • File System Task – Delete Folder
  • File System Task -Move Directory
  • SSIS File System Task -Move File
  • File System – Move Multiple files
  • File System Task – Rename File
  • File System Task – Set Attributes
  • SSIS FTP TASK
  • SSIS Create Local Directory
  • SSIS Create Remote Directory
  • SSIS FTP Task Send Files
  • SSIS FTP – Send Multiple Files
  • SSIS FTP Task Delete Local Files
  • FTP TASK Delete Local Directory
  • FTP Task Delete Remote files
  • SSIS Delete Remote Directory
  • SSIS FTP Task Receive Files
  • SSIS FTP Receive Multiple Files
  • SSIS Script Task
  • Transfer SQL Server Objects Task
  • Transfer SQL Table Structures
  • Transfer SQL Tables with Data
  • Transfer SQL Stored Procedures
  • Transfer User Defined Functions
  • Transfer SQL Views in SSIS
  • SSIS Web Service Task
  • SSIS XML Task-Validate XML File
  • Transform XML File using XSLT
  • XML Task-XML files Differences
  • Create SSIS Catalog
  • Package Deployment using BIDS
  • Deploy Package Using SQL
  • Deploy using SQL Server Wizard
  • SSIS Breakpoints
  • SSIS Checkpoints
  • SSIS Error Handling
  • SSIS Event Handlers
  • SSIS Transactions
  • SSIS Logging
  • SSIS Parameters
  • SSIS Package Configuration
  • Configure using SQL Server
  • Config using Registry Entry
  • Conf with Environment Variable
  • SSIS XML Configuration File
  • XML Configuration File Part 2
  • SSIS Package Protection Level
  • SSIS Incremental Load
  • Incremental Load Example 2
  • SSIS Remove Double Quotes
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy