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.