Search

SSIS ADO.NET destination component in bulk insert mode disables foreign key CHECK Constraint by BenAust

Active

3
0
Sign in
 to vote
Type: Bug
ID: 766249
Opened: 10/5/2012 12:39:13 AM
Access Restriction: Public
2
Workaround(s)
0
User(s) can reproduce this bug
In a SSIS ppackage, when ADO.NET destination component is used in bulk insert mode it changes the CHECK constraint on foreign keys to NO CHECK on the destination table

http://bennyaustin.wordpress.com/2012/10/04/ssis-ado-net-destination-in-bulk-insert-mode-and-foreign-keys/
Details (expand)

Product Language

English

Version

SQL Server 2012 - Developer Edition

Category

Integration Services (DTS)

Operating System

Windows 7 Ultimate

Operating System Language

English

Steps to Reproduce

1. Create a table that has a foreign key to a reference table. Enable the foreign key constraint (WITH CHECK)
2. Load data into this table using SSIS package. Use ADO.NET as Destination and select the option "Use Bulk Insert when possible”

Actual Results

After data in loaded in the destination table, you would notice that the foreign key check constraint of the destination table is disabled and it changes to WITH NO CHECK.

Expected Results

The CHECK constraint on Foreign Key should not be changed to WITH NO CHECK by the ADO.Net Destination component. Occurs only when the "Use Bulk Insert when possible” option is selected

Platform

X64

Virtualization

 
File Attachments
0 attachments
Sign in to post a comment.
Sign in to post a workaround.
Posted by BenAust on 10/5/2012 at 12:40 AM
use OLEDB destination instead
Posted by BenAust on 10/5/2012 at 12:40 AM
Disable foreign keys before data loading and enable foreign keys after the data is loaded
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement