question

sqlaskgk avatar image
sqlaskgk 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
processtaskdata-processingexecute-sql-taskexecute-tsql-task
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
dataminor avatar image
dataminor answered
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).
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.