TRUNCATE TABLE query unable to execute in SSIS because of foreign key

TRUNCATE TABLE query unable to execute in SSIS because of foreign key

I have a table Person which contains 2 fields.In my another database i have a Participant Table(also have 2 columns).From Participant Table i have to insert values into Person Table. but before every insertion,i want truncate the person Table.

I have try it out with linking Execute Sql task to Data flow task.But it is showing error that a Primary Foreign key relation exists there.

Answer by rlobban for TRUNCATE TABLE query unable to execute in SSIS because of foreign key

If a table in sql server has foreign key references then you can't truncate. instead in your execute sql task use delete without a where clause.

delete from person_table  

If you are really adamant about truncating the table, you could drop the foreign key constraints, then truncate the table then recreate the foreign key constraints. Providing of course, that the user you are running the package as has the privileges to do so.

Answer by Diego for TRUNCATE TABLE query unable to execute in SSIS because of foreign key

Create an "Execute SQl" task and run DELETE FROM person after this task, run your import.

DELETE FROM will give the same result as TRUNCATE TABLE, but if the table has a foreign key pointing to it, it cant be truncated. You have to use the delete command

Answer by Pondlife for TRUNCATE TABLE query unable to execute in SSIS because of foreign key

If I understand correctly, SSIS has nothing to do with your real problem, which is that you want to truncate a table that is referenced by a foreign key constraint. That question has already been answered: Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

Answer by HLGEM for TRUNCATE TABLE query unable to execute in SSIS because of foreign key

You won't be able to delete either unless cascading deletes is turned on (or you delete the child records first). Why is this a problem you ask, why can't I do what I want to do? Because if you do then you may lose the integrity of the data. Suppose I have records in table2 which relate to records in table 1. Suppose further that table1 has an autogenerated id. If I could truncate that table, then I leave those records i ntable 2 hanging out without any record to reference them, they have become orphaned. Well but I'm putting the data back in you say. But then they will have new id numbers and you will still lose the relatinoship tothe related data.

Can you drop the FK and truncate and insert and recreate the FK. Yes you can but it is a poor practice and you should not unless you are also recreating those related records.

The best practice is to use a MERGE statement to update or insert depending on what you need.

Answer by Muflix for TRUNCATE TABLE query unable to execute in SSIS because of foreign key

In SSIS Transfer SQL Server Objects Task Set Property DeleteFirst to TRUE

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72