Maximumerrorcount property in SSIS

The maximum number of errors that can occur before a task stops running. The default value of this property is 1. With the help of this property, we can ignore our task failure n times. Remember that we can set this property at package level as well as for each individual task.

Let’s see the demo

In this demo, we will see how Maximumerrorcount property is working. I have a business rule that my execute SQL task fail 1 time then also my package will not fail. If it fails the second time then my package will fail. I am creating a package in that I am using for loop container to iterate the task.

   

 Taking some variable which is using in this package.

   

  For loop container setting

   

Loop will be executed until flag value is true.

Taking two execute SQL task.

 

In the task, I am using below SQL query

 

First time I am passing the value of n is 0. I am failing this task forcefully the first time.

Second Execute SQL task working fine.

In this, I have written correct SQL query.

 

Now I am taking the script task for the variable control.

 

Below variable, I am setting using a script task.

MessageBox.Show("Value of n is : " + Dts.Variables["User::n"].Value.ToString() +   Environment.NewLine + "Value of Result is : " + Dts.Variables["User::result"].Value.ToString()+    Environment.NewLine+ "Value of Eresult is : " + Dts.Variables["User::Eresult"].Value.ToString());  if (int.Parse(Dts.Variables["User::n"].Value.ToString())>0)             {                 Dts.Variables["User::flag"].Value = false;             } Dts.Variables["User::n"].Value = int.Parse(Dts.Variables["User::n"].Value.ToString()) + 5;

Here I am connecting script task with OR precedence constraint

Read more about  : Precedence Constraints in SSIS

https://bageshkumarbagi-msbi.blogspot.com/2016/05/precedence-constraints-in-ssis.html

Now my package is ready to run.

 

Running this package with default Maximumerrorcount

 

Running now

OOOOOOOO!!!!!!!!!!!!

It failed.

See the error.

The error seems that "Divide by zero error encountered".

Now I am setting the property Maximumerrorcount property to 2. It means I am ignoring 1 time failure. I am setting this property on For loop container.

 

Now running this package.

First iteration this EST with Error failed.

 

Now see the second iteration.

It’s completed successfully.

So with the help of Maximumerrorcount property, we can ignore the n number of failure.