Loading Data Into Table via SSIS when IGNORE_DUP_KEY is ON

Probably most of you aware of this issue and most definitely it is not a best practice, but if you wish to use in any unforseen reason please be aware.

For example we have a table with 3 columns. If you set a Unique Non-Clustered Index with all 3 columns and set IGNORE_DUP_KEY = ON, then you have to be aware of the following.

If you are using OLE DB Destination with OLE DB Provider for SQL Server it will fail the package even though it will insert correct number of rows by ignoring duplicate rows. If you want to populate this type of table without failing the package, while using OLE DB Destination, you would need to user Native OLEDB\SQL Native Client.

The Provider to use in this scenario is - SQLNCLI.1
The Provider NOT use in this scenario is - SQLOLEDB

Thanks
Sutha