DTS Packages & a Connection to Access

Question

Using DTS I'm dynamically creating an access database. After the file is created (which works at this point) I'm having the DTS package post it via HTTP to another site. (This HTTP is just how it has to be done -- part of the spec.)

But when the package posts the file, the posted version comes out as a 0 byte file. The code to post the file is tested and well implemented code. I'm sure it works. A colleague told me that he believes this "0-byte" error occurs because the Access connection hasn't closed the access file. He said that the DTS package must complete for the access file to be closed.

The Question is: How do I get Access to close the Access file before the step to post the file executes?

Just in case it is needed, the steps are:

• Create the Access file, set a pre-defined access connection object to this new file
• Create the Table in Access
• Use a Transform Data task to Populate the Access Database (from a SQL Server)
• Post the file via HTTP (predefined/Pretested Libaary is being used)

Thank you in advance for your help!

Answer 1

I believe your colleague is correct, the posting via HTTP most likely will need to live outside of the DTS, or you might need to look into a VBS way of closing the connection, but I'm not sure if that is possible.

Answer 2

The answer, I just found out, is that just before I post the file (or after I'm done writing to the file) have the workflow close the connection

I can accomplish this by checking the "Close Connection on completion" checkbox on the options tab of the Workflow Properties dialog.

Thank you all!