asked
SSIS Execute SQL Task with Merge for multiple tables
Hi, I am new to SSIS process. We have an existing SSIS packages which runs daily load the data from Source DB2 and store in staging (sql server) database. Then read from staging and write to main sql server target database. In this process, we completely drop the data from staging and target databases using TRUNCATE and reload the target tables. This process is running very long. So we decided to use Merge statement with ADD/update/delete based on the change instead of dropping the data every day. So basically I have to update 4 target tables. Among these 2 of them are child tables with foreign keys. So I have created sequence container with 4 tasks. Each task has merge statement for each target table. Everything is running fine. In Delete scenario, packages fails with foreign key constraints. I need help how to handle this scenario. Your help is greatly appreciated. Thanks in advance GK
Several possibilities might warrant investigation: 1. Drop the foreign key constraints and re-apply them after processing 2. Maybe try processing the child tables before the parent tables. 3. Change the foreign key constraints to cascade deleteing 4. Remove the foreign key constraints altogether (if you're confident the foreign key constraints work properly on the source system, the argument goes that you don't need them in your ETL - that validation's already been done).
Thank you for the response. I have read about the options 1 2 & 3 already and not able to implement as I am using the MERGE statement ( process is by record level) .If I process the child table task first, I think it will give problem for adding a new row but I will try. To use cascade delete, when to use the ALTER statement with ON DELETE CASCADE in MERGE? If some example is greatly appreciated.
18 People are following this question.
