I am running an SSIS package which will replace data for a few tables from FlatFiles to existing tables in a database.
My package will truncate the tables and then insert the new data. When I run my SSIS package, I get an exception because of the foreign keys.
Can I disable the constraints, run my import, then re-enable them?
Best Answer
To disable foreign key constraints:
To re-enable:
However, you will not be able to truncate the tables, you will have to delete from them in the right order. If you need to truncate them, you need to drop the constraints entirely, and re-create them. This is simple to do if your foreign key constraints are all simple, single-column constraints, but definitely more complex if there are multiple columns involved.
Here is something you can try. In order to make this a part of your SSIS package you'll need a place to store the FK definitions while the SSIS package runs (you won't be able to do this all in one script). So in some utility database, create a table:
Then in your database, you can have a stored procedure that does this:
Now when your SSIS package is finished, it should call a different stored procedure, which does:
If you're doing all of this just for the sake of being able to truncate instead of delete, I suggest just taking the hit and running a delete. Maybe use bulk-logged recovery model to minimize the impact of the log. In general I don't see how this solution will be all that much faster than just using a delete in the right order.
In 2014 I published a more elaborate post about this here: