4

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?

Thomas Jespersen
  • 155
  • 2
  • 8

2 Answers2

4

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

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
1

  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.

Saif Khan
  • 1,935
  • 2
  • 20
  • 25