In one SQL Server Integration Services (SSIS) package Data Flow, I have two OLE DB Commands that have to run one after the other.

The problem is that I am updating the same table in a Microsoft Access 2000 Database in both steps.  So, when there are many records, the second OLE DB Command throws an error complaining about the table being locked.  Of course, the table is locked by the first OLE DB Command that needs just a few more seconds to finish what it was doing. 

So how can I give enough time for the first OLE DB Command to finish before invoking the second one?  Well, in this particular case, I am blessed with the fact that I am pretty much guaranteed that there will most likely never be more than 40 records to go through this data flow when it runs (this information is from a single provider doctor office and 40 is the most patients he can see in one day).  So, all I need is a stop gap. 

The solution is to add a Script Component between the two OLE DB Commands.  Once there, edit the Script Component and add the following two lines:

What we are doing is calling the Sleep function in C# and telling it to count to 10 (the argument is in milliseconds).  This way, the first OLE DB Command has 10 seconds to finish; thereby unlocking the table, before the next one begins – and with 40 records that should be plenty of time.

I do not claim this solution to be elegant; however, it does work as intended and it solves the problem.  I welcome more elegant solutions in the comments.

For more information about the System.Threading.Thread.Sleep() function, see this MSDN web page.

James