I get an access denied error when I attempt to delete and
access database file after importing it using DTS. It
appears that DTS holds on to the file until the package
completes.
Here is what I am doing. The file in question is an
Access.mdb file

If file NewFile exists rename to ProcessFile.
import the data from processfile and process it.
Delete processfile.

The delete step fails with access denied. I added a wait
before the delete and it still never released the file.
Watching the process in the browser, the .ldb file does
not appear to get removed until after the package
completes.

What is the best way around this? Spawn a new package to
do the delete or is there a way to force DTS to release
the file.

Ken

I have seen this before. Yes even though you may set "Close Connection on
completion" then DTS keeps a hook to the DB.
I have got around this by adding a job step that deletes the file after the
DTS package has run.
--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Ken" <ken.terrell (AT) masco-csc (DOT) com> wrote

Thanks,
Actually I had not set the flag "Close connection on
completion" on the transform data task(Under workflow
properties) tasks. I found that after I posted this and it
seems to be working. The delete step is a seperate
vbscript task that runs last.

If this starts to give me problems, I will have to do as
you suggest and just add a SQL job step to perform the
delete after the SQL job step that runs the package
completes.

Ken

When I have seen it the problem was with a text file and even setting this
workflow property couldn't disconnect it properly. Glad you sorted it
though

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Ken" <Ken.terrell (AT) masco-csc (DOT) com> wrote