Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm trying to set up an import form live database to our development database.

I trying to do this using SSIS, but the import is failing because of constraints.

E.g. I have the following tables:

  • Customers
  • Products
  • OrderLines
  • Orders

Importing Customers and Products is fine, because they do not depend on anything. But when importing OrderLines it fails because the Order is not yet created.

How do I change the order of how SSIS imports tables?

Or maybe I'm not supposed to use foreign key constraints?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

disable or remove the foreign key constraints before the data import & then enable or add them after the import eg. remove/add

--drop
alter table t1  DROP CONSTRAINT fk_1
--add
alter table t1 WITH CHECK add constraint fk_1 foreign key (fk)
references t2 (i)

disable/enable

--disable
alter table t1 NOCHECK CONSTRAINT fk_1
--enable
alter table t1 WITH CHECK CHECK CONSTRAINT fk_1

The WITH CHECK is important, otherwise SQL Server will not check the data & not trust the data

share|improve this answer
  1. Import in the following order

    1. Customers
    2. Products
    3. Orders
    4. OrderLines

Foreign Keys in one table are Primary Keys in another table. Example: trying to insert OrderLines without Orders (order number), Products (Product Number) won't work.

You can also follow @Nick Kavadias idea, but that could cause voilation of business rules (although it can be caught when you try to re-enable the Constraints) having orphan records.

share|improve this answer
    
Well. I can not find a way to change the order. –  Thomas Jespersen Oct 21 '09 at 16:43
1  
just re-arrange your queries in the SSIS task. –  Saif Khan Oct 21 '09 at 21:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.