I have a legacy business application that outputs data to DBF files.
One facet of the system is Stock Control, and i am writing a number of Crystal Reports based around the stock data.
In the absence of any real-time connection to SQL, I have an SSIS package that runs every 5 minutes, taking approximately 40 seconds to complete.
1. Launches legacy Export application to output stock data to a DBF file
2. Imports DBF content into temp table.
3. Truncates destination table in SQL
4. Copies data from temp table into destination table using a oledb data flow.
All quite simple. (I'm not a programmer by any stretch of the imagination!)
My problem is that, during step 4, I want to lock the destination table from being read whilst data is being imported. The reason being that, if a report were run whilst rows were being written, it could result in a very misleading report; and with the frequency the routine is running this is quite a likely scenario.
I've placed step 4 within a transaction but I am still able to read from the table whilst rows are being written.
Can someone offer any suggestion as to how i can lock the table from reads whilst rows are being written? I would rather the report 'pauses' for a few seconds before reading complete data, rather than potentially returning incomplete data.