Unanswered: need to re-set path to access file each time I import it in DTS task
I have struggled with this one for a while.
I have a DTS task that imports an Access file into a table in SQL Server. The Access file has security setup since it is the backend for a HR application.
I defined my Access file connection in DTS by setting the path the the mdb file, supplying user and password and under advanced properties I add the workgroup file to JetOLEdbystem Database. Once set it works fine.
This is the scenario:
Access file is copied from another server nightly to the server running DTS
DTS task is scheduled and runs each night opening the file and importing some of its data into SQL Server
as soon as a new copy of the Access file is put on the SQL Server, the DTS task fails, saying the file mdb file is already in use or the user set in the connection properties is not valid. The only way I can get it back to work is to open the connection properties, re-set the path (it does show the right path, but when you 'Browse' to it, it ends up showing the My Documents folder. Then I have to go into advanced properties, remove the workgroup file, click OK, go back into advanced properties and add the workgroup file, click OK and then it works again.
It is not a network/share permission etc. issue as outlined in MS Knowledge Base Article: How to Run a DTS Package as a Scheduled Job
I think it is either related to the fact that the file is copied each night (new update date?) or something with the workgroup file.
Anyone seen this before? I clueless and can't run this job automatically...
I run a simpe NT Task to copy the file from one server to another. This happens after all users left the application that uses this MDB file. Also the DTS job doesn't start until 2 hours after the copy of the file finished.
The odd thing is that if I manually unset/set the path to the file and the workgroup file in the connection properties it works fine...