October 28, 2002 at 1:49 am
Hi all
I have a wizz bang dts that "attempts" to process around 50 access databases in a single directory (contents are all the same). The dts dynamically alters the connection, transforms data into the appropriate table, and moves the file before looping for the next file. Unfortunatly, move is not working. When the transformation is performed, an access ldb (lock file) is created, but seems to hang around until the entire dts package is complete, therefore, my ends up in an endless loop.
For the life of me, after the successful transformation, I cant remove the lock on the access db.
Ideas??
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
October 28, 2002 at 4:31 am
One would be to just do the move at the end of instead of during the loop. The other would be to make sure you're closing the connection and setting to nothing - that should cause the lock file to clean up..I think!
Andy
October 28, 2002 at 10:17 pm
Hi
Ok, cool, i initially ripped an example from sqldts.com for looping but its a tad restrictive with access databases. In the end, I adapted this code to generate a string such as:
[file1.mdb][file2.mdb][file3.mdb]<etc>
Now, I simply loop around using some instr and mid functions to cut out the file names and process them accordingly.
Works well and like most dts stuff, its very fast.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply