Check constraints option in oledb destination in SSIS

This option is enabled when we are selecting Data access mode to “Table or view – fast load”

  

By default check, constraints value is checked.

When we are selecting the data access mode to “Table or view – fast load” our destination treated as bulk insert. But our destination may have constraints.

Constraints in SQL Server are predefined rules and restrictions that are enforced in a single column or multiple columns, regarding the values allowed in the columns, to maintain the integrity, accuracy, and reliability of that column’s data. Constrain maybe

•   PRIMARY KEY
•   FOREIGN KEY
•   CHECK
•   DEFAULT
•   NOT NULL
•   UNIQUE

Let’s see the demo how it works

Here I am creating a table with check constraints.

CREATE TABLE Sales_Table (     [SalesOrderNumber] varchar(50),     [SalesAmount] varchar(50),     [UnitPrice] varchar(50),     [ExtendedAmount] varchar(50),     [TaxAmt] varchar(50),     LoadDate DateTime DEFAULT getdate(),     LoadBy    VARCHAR(50), CONSTRAINT check_LoadDate     CHECK (LoadDate <= Getdate()) )

It checks the load date. Load date must be today date or less than it.

I am taking data flow task and source as a flat file

  

Column mapping

  

Now I am taking derive column transformation to add the load date column and assigning the load date through expression.

  

Now I am taking oledb destination

And mapping the columns

   

Unchecking the Check constraints.

 

\

Now I am running this package.

  

Load date less than Today date.

   

 

Data loaded successfully. Now I am adding the load date to future date.

  

Check constraint to unchecked.

  

Now running this package.

Data loaded successfully.

  

Even though it violated the check constraint it loads the data.

Now I have checked the check constraint.

  

Running the package.

                         

Date is future date so it will violate the rule.

Package failed.  

 

See the error.

[OLE DB Destination [40]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "The statement has been terminated.". An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "The INSERT statement conflicted with the CHECK constraint "check_LoadDate". The conflict occurred in database "Test", table "dbo.Sales_Table", column 'LoadDate'.". [OLE DB Destination [40]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "OLE DB Destination.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure. [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE DB Destination" (40) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (53). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

So we can say that Unchecking “Check Constraints” only turns off the checks for check constraints but Referential and other integrity constraints (NOT NULL, Foreign Key, Unique Key, etc.) continue to be enforced.

ForceExecutionResult property in SSIS

SSIS has the ability to force a task execution result. With the help of ForceExecutionResult property, we can set the task executing 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.

Let’s see the demo

Here I am taking a sequence container and in this container I am taking two execute sql task.

 

First execute sql task

                                  

In this task I have written an error sql script divide by zero.

   

 

2^nd execute sql task

   

In this I have written correct sql query.

   

Running package with default property.

 

Running now

 

See the execution result

   

   

Now I am setting the ForceExecutionResult success for the execute sql task

   

Now running this package.

 

It’s completed successfully even though it has error.

 

When we connected both execute sql task with precedence constrain then we need to set the ForceExecutionResult property of execute sql task to “Success”.

Running with default value

 

In this case my 2^nd execute sql task does not executed.

Now setting the value of execute sql task to ForceExecutionResult =”Success” then even execute sql task failed my 2^nd execute sql task will execute.

   

Running this package.

 

Even the 1^st execute sql task having error but here we are setting the forcefully result success.

   

When we set the ForceExecutionResult value is failure then even our correct task will fail.

See the 2^nd execute sql task.

 

Running this package

Getting failed.

 

See the execution result.

 

Hope this will help to understand use of ForceExecutionResult property in SSIS package.

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.