Insert SSIS into one table at a time

Question

I have an SSIS package with the task of downloading an application outside the process for mass data entry into a single table. The problem is that several applications that are not part of the process will run at the same time while receiving multiple files at the same time. This will cause the insert to fail.

Can SQL Server Broker service data insertion? Does SQL Server or SSIS have any mechanism for handling concurrent reliable insertion?

I use SSIS 'Execute Process Task to do bulk input of a LINQ to SQL console application

Thanks.

Answer 1

It looks like you are getting timeout problems because the first time you run the SSIS packet locks the table, and all other running copies of the packet wait for the lock to be released.

There are several things you can do to confirm this. First, in SQL Server Management Studio (SSMS), open a query window, and when the situation arises, run the EXEC sp_who2 . You will see the BlkBy column in the results. The column contains an SPID value that blocks the selected process. You will probably see that one instance of your package blocks all other packages.

In the SSIS Designer, in the Data Flow task, edit the Destination component. Check the box next to "Lock table." This is probably a check that tells the process to lock the table until the data download is complete.

You have several options for solving this problem. First, is it important for one SSIS package to populate the boot data before another can be started? If the answer is “No,” you can clear the “Table Lock” checkbox in the “Destination” component. This enables SQL Server to manage concurrent data loads.

If you must allow one package to complete before running other packages, you may need to create an SSIS task that checks if the table is available for download. If the table is loading, stop the SSIS package and try again later. You can even handle this in your console application.

SQL Server does not have built-in methods for this, and the broker service sounds as if it works more than you need.