Delete/Rename .mdb file after successful transfer to SQL Server

[spaghettiGurl]

Hi,

I've been pulling out my hair trying to solve this problem. I have a .mdb (access database) that I want to import to SQL Server using DTS. This in itself works. The tricky bit is coming. I want to test that the file is there (this works), import the data (this works) and then rename the imported file (This doesn't work).

I've done everything I could think of from using ReleaseConnection, calling another process to do it, even renaming the existing connections, but for some reason the connection to the .mdb file doesn't not drop until the package has fully run.

Other than running a seperate package/task after this one is complete to delete/rename the file is there anyway I can get the dts package to relinquish it's hold on the Access Database that I've imported? Sorry, I hope this makes sence but I'm desperate to get this working and any help would be very appreciative!

 

[unclerico]

take a look at this page here

http://www.sqldts.com/default.aspx?292,

the second topic down.

 

[spaghettiGurl]

I've already been to this site and used this code (as such...) but it fails on the

oFSO.MoveFile sSourceFile, sDestinationFile

line. I can copy but not move or delete because the Access Database is still locked until the DTS process has finished. How do I get the DTS process to release the Access Database so I can use the above code?

 

[unclerico]

create two packages; 1 package that has an execute DTS task that references the 2nd package, after the second package comes back with a successful status, have a second step in the first package that has an On_Success workflow that points to the contents of the link above.

Package 1:
Step 1: Execute Package Task; point it to Package 2
On_Success workflow points to Step 2
Step 2: Rename the .mdb file

Package 2:
Step 1: Test existence, import data

This should work.

 

[spaghettiGurl]

I tried something just slightly different...I had 3 packages. 1 - Import Data, 2 - Rename file, 3 - Points to package 1 on success points to package 2. Sorted!

Thank you So much!