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.