Parent Child Package Configuration in SSIS

Package configuration is nothing but the way to provide the variable value without editing the package externally.

Read other type of configuration in SSIS

XML Configuration file & SQL server:

https://bageshkumarbagi-msbi.blogspot.com/2014/12/package-configuration.html

Environment variable:

https://bageshkumarbagi-msbi.blogspot.com/2020/09/ssis-package-configuration-using_12.html

Registry Entry:

https://bageshkumarbagi-msbi.blogspot.com/2020/09/ssis-package-configuration-using.html

 The Parent Package Variable configuration is a way of passing values from parent to child packages. The configuration is built on the child package and uses a variable in the parent package. The configuration is mapped to a variable in the child package, or to the property of an object in the child package.

In this demo, we will show, how we can use the parent package configuration.

Here we are creating a parent package and child package.

File example: 

The table where we are loading theses files data

                                       
We have created a child package to load the csv file.  

Now we are creating the parent package where we are taking the Foreach loop container and iterating the files and inside the Foreach loop container, we are taking the Execute package task to call the child package.

  

Configuring Foreach loop container as file enumerate

                                        

Storing this file name to a variable.

                                         

Inside the loop, we are taking the execute package task and call the child package.  

Now come to the child package.

Creating a variable to receive the parent package variable value.

   

In the child package we are creating the Flat file connection manager dynamically using expression.    

Click ok.

Now right click child package and creating the parent package configuration.

  

Click next

                                  

Click next

  

And click finish.

Now the package is ready to run.

Parent package:  

Child package

  

Before the execution of this package.

Records in the table.

  

Now running the Parent package

The package executed successfully.

  

See the records in the table.

                                       
Hope this will help to understand the parent package configuration.

ExecuteOutOfProcess property in Execute package task in SSIS

Specify whether the child package runs in the process of the parent package or in a separate process. By default, the ExecuteOutOfProcess property of the Execute Package task is set to False, and the child package runs in the same process as the parent package. If we set this property to true, the child package runs in a separate process. This may slow down the launching of the child package.

Disable property in SSIS (Dynamically Disable a Task)

We are getting 3 different types of files from business days and we need to load it into the database. We are getting these files daily. But sometimes we are getting 2 files, sometimes we are getting 1 file and someday we are not getting any files. Our package is scheduled and it is running daily. When we are not getting the expected number of files (3 files) that day our package getting failed.

To overcome this issue we need to update our package to handle such type of scenario.

For solving this issue we are using the disabled property of the task. First of all, we are checking the existent of the file if the file is available then we are setting the Disable = False for that task and if the file are not exist in this case we are setting the Disable = True for the respective task. We are setting this value dynamically. Using the script tasks we are checking the file existent and setting the variable flag value.

Let’s see the demo

We have below files  

To load this data we are developing the below package.     

Taking some variables  

We have 3 table were we are loading this files.

CREATE TABLE [dbo].[Test1](                 [SalesID] [bigint] IDENTITY(1,1) NOT NULL,                 [SalesOrderNumber] [varchar](50) NULL,                 [SalesAmount] [decimal](28, 10) NULL,                 [UnitPrice] [decimal](28, 10) NULL,                 [ExtendedAmount] [decimal](28, 10) NULL,                 [TaxAmt] [decimal](28, 10) NULL              )   CREATE TABLE [dbo].[Test2](                 [SalesID] [bigint] IDENTITY(1,1) NOT NULL,                 [SalesOrderNumber] [varchar](50) NULL,                 [SalesAmount] [decimal](28, 10) NULL,                 [UnitPrice] [decimal](28, 10) NULL,                 [ExtendedAmount] [decimal](28, 10) NULL,                 [TaxAmt] [decimal](28, 10) NULL              ) CREATE TABLE [dbo].[Test3](                 [SalesID] [bigint] IDENTITY(1,1) NOT NULL,                 [SalesOrderNumber] [varchar](50) NULL,                 [SalesAmount] [decimal](28, 10) NULL,                 [UnitPrice] [decimal](28, 10) NULL,                 [ExtendedAmount] [decimal](28, 10) NULL,                 [TaxAmt] [decimal](28, 10) NULL )

 Task: SQL_Truncate_Tbl

This task is used to truncate all 3 tables.

Script task:

SCR_Validate_Files_Set_Disable_Fl_FF_Conn_Str

This task is used to check the existent of the files and set the disable flag value and flat file connection manager.

By default, we are setting the disable values as false.  

Below is the C# code

try             {                 //Getting the File dir path                 string File_Dir = Dts.Variables["User::File_Path"].Value.ToString();                 //setting the file full path value into the variable                 string File_Full_Path1= File_Dir +@"\"+ Dts.Variables["User::File_Nm1"].Value.ToString();                 string File_Full_Path2= File_Dir +@"\"+ Dts.Variables["User::File_Nm2"].Value.ToString();                 string File_Full_Path3= File_Dir +@"\"+ Dts.Variables["User::File_Nm3"].Value.ToString();                   Dts.Variables["User::conn1"].Value = File_Full_Path1;                 Dts.Variables["User::conn2"].Value = File_Full_Path2;                 Dts.Variables["User::conn3"].Value = File_Full_Path3;                   //setting the disable flag value. if file doesn't exits the set true.                 if (!File.Exists(File_Full_Path1))                     Dts.Variables["User::File_Fl1"].Value = true; //setting disable is true when file doesn't exist                 if (!File.Exists(File_Full_Path2))                     Dts.Variables["User::File_Fl2"].Value = true; //setting disable is true when file doesn't exist                 if (!File.Exists(File_Full_Path3))                     Dts.Variables["User::File_Fl3"].Value = true; //setting disable is true when file doesn't exist                   Dts.TaskResult = (int)ScriptResults.Success;             }             catch(Exception ex)             {                 Dts.Events.FireError(0, "Download", "Failed: " + ex.Message, string.Empty, 0);                   // Quit Script Task unsuccesful                 Dts.TaskResult = (int)ScriptResults.Failure;             }

In Sequence Container we are taking the Data Flow Task and taking Source as flat File and destination as OLEDB destination.  

In the Data Flow task, we need to go to property expression.    

Click on the expression.  

Click ok.

We are configuring same property for all data flow task.

The package is ready to execute.  

Before running this package.

Records in the tables    

Case 1 : All files on the folder

Files on the folder.

                              
Package executed successfully.  

See the records in the table.    

Case 2:  Two  files on the folder     

Now running the package. 

See here in folder file3 is not exist that’s y task 3 is not executed. We are setting the disable flag as true dynamically. 

See records in the table.

                              
There are no records in the table 3.

Case 3: one file in the folder.  

Now running the package. 

Only one file exists in the folder so that only one task executed.   

Case 4: if there is no file exist in the folder.

In this case package will execute without fail.  

Executing the package. 

See the records in the table.  

Case 5: if we did not set the disable flag dynamically and files not exist in the folder in this case the package will fail.

We are removing the dynamic disable flag from task 1.  

See the folder in the file  

See file1 is not existed.

Now we are running the package.

Package gets filed.

Error: 

Hope this will help to use of the disable the flag in the package.

HasExpressions property in SSIS

The HasExpressions property is available on all SSIS objects that support expressions, with the exception of variables. The property enables us to easily identify which objects have expressions.

By default the value of HasExpressions is false.  

Let’s see below example

Here we are taking a execute sql task.  

Using this execute sql task we are inserting a record in the table.

Now we are setting the disable property of this task.

Read More about: Disable property in SSIS (Dynamically Disable a Task)

Here we are taking the variable as Boolean 

Now we are setting the disable flag property dynamically.  

Click ok.

Fx logo is create on the task. And HasExpressions property is true.  

HasExpressions is indicate that this task has expression.