CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlssis
Laki
Laki

Reputation: 99

Foreign key relationship can't be created (SQL Server)

I created a SQL Server database first (2 tables) and then tried to load data through SSIS data flow task. At the last step an error has occurred.

When I remove a relationship between two tables in the database, the SSIS task is completed successfully and the data is loaded! But, after I load data into the tables, I can't create relationship between them.

Based on this you can conclude that a relationship can be created when there is no data in a table. Just to mention, data types are the same in both tables.

How could I work out a solution?

Thank you!

Upvotes: 0

Views: 206

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46281

It seems the error in SSIS is due to a foreign key violation. The purpose of the foreign key relationship is to prevent you for loading bad data. When you loaded without the FK, you inserted bad data and cannot create a (trusted) foreign key constraint afterward.

The solution is to either fix the source data or modify your package to avoid inserted data that doesn't exist in the referenced table. The latter can be done with a lookup task, sending found rows down the happy path to the target table. You could either ignore not found rows or write those to an error table or file.

Upvotes: 3

Related Questions