I have a requirement to read reference data from several access databases for an ETL job. The data owners have produced a file with a series of linked tables that consolidates them all into one place. However, if one of the base files is open with a lock (.ldb) file the OLEDB provider will refuse to open the linked table.
The SSIS package is set up with several data flow tasks reading from OLEDB sources. To clarify, these are reference tables, and not necessarily being updated at the time – the .mdb file has a lock (.ldb) file.
Is it possible to configure the MS jet OLEDB driver to force-open the linked tables, ignoring the locks?
A couple of alternatives that we've looked into are:
-
Configure the linked tables the other way around – put the sources in the central database and link to them from the other access DBS. The business are reluctant to do this, and we don't have the clout to force it.
-
Copy the base databases to a staging area and open the copies – this is our plan B, although it involves shifting about 2GB across a slow-ish WAN link and adds latency right at the start of the ETL job (pretty much everything is dependent on these reference tables). I'd rather avoid the latency if possible.
EDIT: See my answer – the problem was due to lack of write permission on the lock file and an ambiguous error message returned by the driver.
Best Answer
Found the problem - permissions issue in disguise. The Jet driver needs to have write access on the directory so it can update the lock files. When it can't write to the lock files it throws its toys out of the cot with a misleading error message.