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?
Disable all table constraints
-- Enable all table constraints
In case you use a different database schemas than ".dbo" or your db is containing Pk´s, which are composed by several fields, please don´t use the the solution of Carter Medlin, otherwise you will damage your db!!!
When you are working with different schemas try this (don´t forget to make a backup of your database before!):
After doing some Fk-free actions, you can switch back with
A good reference is given at : http://msdn.microsoft.com/en-us/magazine/cc163442.aspx under the section "Disabling All Foreign Keys"
Inspired from it, an approach can be made by creating a temporary table and inserting the constraints in that table, and then dropping the constraints and then reapplying them from that temporary table. Enough said here is what i am talking about
Truncating the table wont be possible even if you disable the foreign keys.so you can use delete command to remove all the records from the table,but be aware if you are using delete command for a table which consists of millions of records then your package will be slow and your transaction log size will increase and it may fill up your valuable disk space.
If you drop the constraints it may happen that you will fill up your table with unclean data and when you try to recreate the constraints it may not allow you to as it will give errors. so make sure that if you drop the constraints,you are loading data which are correctly related to each other and satisfy the constraint relations which you are going to recreate.
so please carefully think the pros and cons of each method and use it according to your requirements
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:
Use the built-in sp_msforeachtable stored procedure.
To disable all constraints:
To enable all constraints:
To drop all the tables: