Set ForceExecutionResult value during the Run time (Fail package forcefully at run time based on the condition)

We have the requirement below.

We are loading the CSV file in the staging table and if we are getting any error during the load we need to redirect that row in the Stage Log table. If any record redirected in this case we need to fail the package.

We can fail the package using the script task we can fail the package to set the task property ForceExecutionResult as a failure during the run time when our condition matches.

SSIS has the ability to force a task execution result. With the help of ForceExecutionResult property, we can set the task executing the result.   

 By default the ForceExecutionResult value is none. It means this value assign during the execution.

We can set the execution result forcefully by setting this property.

 Read here: ForceExecutionResultproperty in SSIS

https://bageshkumarbagi-msbi.blogspot.com/2019/08/forceexecutionresult-property-in-ssis.html

In this demo, we will see how to set this property at run time.

Below is the file    

   

We have created two table

·         sales_Stg (Stage table to load the file data)

·         sales_Stg_Error_Log(Error log table for sales if any error comes we will store that row in this table)

Create script for both tables.

CREATE TABLE sales_Stg(                 SalesOrderNumber varchar(30) NULL,                 SalesAmount decimal(18, 10) NULL,                 UnitPrice decimal(18, 10) NULL,                 ExtendedAmount decimal(18, 10) NULL,                 TaxAmt decimal(18, 10) NULL,                 Created_Date date NULL,                 created_by varchar(100) NULL )  CREATE TABLE sales_Stg_Error_Log(                 SalesOrderNumber varchar(50) NULL,                 SalesAmount varchar(50)  NULL,                 UnitPrice varchar(50) NULL,                 ExtendedAmount varchar(50) NULL,                 TaxAmt varchar(50) NULL,                 Created_Date date NULL,                 created_by varchar(100) NULL )

 After creating this table we have designed the below package. 

 SQL_Truncte_STG   

This task we are using to truncate both tables in each load.   

Data Flow Task

Now we are taking the data flow task and loading the file into the stage and if we get any error in the load we are redirecting that row into the stage error log table.  

In the derived column we are adding some columns and doing the data conversion.

  

Now we need to click on the Configure error output in the derived column.   

And selecting the Error and Truncation Direction and selecting the Redirect Row option.  

Click ok.

Now we are taking the two OLEDB destinations one for loading the data into the stage and other one is for load the error load and doing the mapping for both tables.   

Now in the control flow, we are taking the execute SQL task and getting the count of the log table, and setting the count of the variable.

SQL_Get_Error_Count

                               
Now we are taking the Expression task in this task we are setting the ForceExecutionResult property values based on the count of the error log.

If there is any record in the log table it means that we need to fail the package. If there is no record in the log table we need to execute the remaining package.

Expression Task 

In the property window, we are setting the ForceExecutionResult and click on the expression.  

Now in the expression, we are checking the count and setting the property value. 

Below is the numeric value for this property

                

Property name	value
None	-1
Success	0
Failure	1
Completion	2

In the expression, we are setting the same

If error count >=0 in this case we are setting the value 1 (Failure) and if not in that case we are setting the value 0 (Zero) (Success).

After that, we have taken the Dummy Execute SQL take to check the rest package is running or not.   

Now our package is ready to run.

See the file     

All data are correct.

Now we are loading this file.

See the records in the table.  

Now running the package.  

Package executed successfully.

See the records in the table.  

No records in the error log table.

Now in the file, we are adding some corrupt data to fail the package.  

TaxAmt column data type is Decimal but we are passing the string value.

Now running the package. 

The package gets failed.

All data are loading successfully in the stage and error log table.  

7 rows inserted into the stage and 3 rows redirected into the Error log table.

Now see the records in the table.

                          

In this way, we can load the files and afterload in the stage we can fail our package.

Another way to fail our package using the script task.

See in the next post.