September 20, 2011 at 9:48 pm
Hi All,
Just wanted to check if sQL server export/import wizard takes care of referential integrity while loading data which has parent child relationships ????
I tried to check in sql server 2005 sp4 and sql server 2008 sp1.
In both it gives me an error saying INSERT conflict due to foreign key constraint violation.
To investigate further, i did into little further and i saved the underlying ssis package which will be created for export/import. When i checked the package, i see the data flow tasks are loaded parallely and not like loading parent tables first and then child tables next.
However, i found my own work around that i loaded the parent tables first and then child tables. this worked for me.
Also, the tables gets loaded absolutley fine. But sometimes it fails.
Can anyone comment on this?
Thanks in advance.
September 21, 2011 at 7:22 am
Import/Export wizard does not automatically handle referential integrity. As you mentioned, the proper way to handle this is to load the reference tables first, and then load the table with the foreign keys after a successful load of the reference tables.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
September 21, 2011 at 7:34 am
... But sometimes it fails.
Does this comment apply to the situation after you have changed the package to force things to load in the 'proper' order?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 21, 2011 at 8:51 am
Hi Phil,
Sometimes it works without making any changes to the underlying package.
September 21, 2011 at 10:04 am
I see - that's probably down to good luck in terms of timing. Much better to order things properly and then you shouldn't have these issues at all.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 21, 2011 at 10:55 am
Yes. you are right. Decided to write a new custom SSIS package.
Thanks everyone once again for sharing your thoughts.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply