How can I Import Data using SQL Servers SSIS, when using foreign key constraints?

Question

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?

Answer 1

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

Answer 2

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.