concurrnet inserts with execute sql task

Question

concurrnet inserts with execute sql task

Thirston Third 21

When I try to make concurrent inserts into the same table with multiple execute sql task only 1 runs at a time. How do you run multiple execute sql tasks to insert data concurrently into the same table?

Olaf Helper 47,621 Reputation points

2022-04-06T11:52:05.69+00:00

That's to less on information. How does your package design look like, tables, existing data, indexex, data to load, etc.
Thirston Third 21 Reputation points

2022-04-06T12:15:06.82+00:00

I have multiple execute sql tasks set to run simultaneously. Each tasks processes data to be imported into TableA. ExecuteSQLTask1 processes the 1st 1000 records, ExecuteSQLTask2 processes the 2nd 1000 records, etc. When run only ExecuteSQLTask1 runs. The other ExecuteSQLTasks will quickly turn green but they didn't do anything if you check the DB. It behaves like there's a lock on the table. I'm not sure how to check and unlock it if so.

1 answer

Your answer

Olaf Helper 47,621 Reputation points

2022-04-06T11:52:05.69+00:00

That's to less on information. How does your package design look like, tables, existing data, indexex, data to load, etc.
Thirston Third 21 Reputation points

2022-04-06T12:15:06.82+00:00

I have multiple execute sql tasks set to run simultaneously. Each tasks processes data to be imported into TableA. ExecuteSQLTask1 processes the 1st 1000 records, ExecuteSQLTask2 processes the 2nd 1000 records, etc. When run only ExecuteSQLTask1 runs. The other ExecuteSQLTasks will quickly turn green but they didn't do anything if you check the DB. It behaves like there's a lock on the table. I'm not sure how to check and unlock it if so.

Answer 1

ZoeHui-MSFT 41,551

Hi @Thirston Third ,

Have you added the Precedence Constraints between the tasks?

If you only run the ExecuteSQLTask2, will the table update?

If possible, please enable the profiler trace in SSMS and then run the package to catch the trace so that we could do more analysis.

Regards，

Zoe

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Thirston Third 21 Reputation points

2022-04-09T17:16:52.903+00:00

I don't have any precedence constraints between the tasks. If I only run any of the task alone it will run successfully. If I run more than one task at a time only the 1st task runs successfully. I think the table is being lock by MS design even if I disable the lock_escalation. I'll try working with sql profiler.

Share via

concurrnet inserts with execute sql task

1 answer

Your answer