Exporting data via DTS / Poss open file issue
-
Very simple DTS package - runs a query that exports the dataset to an Excel file.
Upon successful completion, I use xp_cmdshell to execute a batch file in the same folder as the XLS.
This batch file deletes any existing zips in that folder, zips the XLS, then deletes the XLS.
This works fine IF the xp_cmdshell is run in a separate DTS.
However, if I put that step within the export DTS -or- use Execute Package to run the package with the batch file after export, the batch file is not executed properly (if there is an existing zip its not deleted, if there is no ZIP it is created but the XLS is not added, nor is the XLS deleted at the end). The DTS package completes without error, but the problem is in the batch file - which of course is not passing back error info to xp_cmdshell.
This appears to be an open file issue (XLS is still open from the export) - I have tried using separate connections, an ActiveX script to pause for a few seconds - to no avail.
Any suggestions?
-
Check the workflow properties of the datapump task that writes to the Excel file. Make sure 'Close Connection on Completion' is selected.
--------------------
Colt 45 - the original point and click interface
-
That was it - thanks, I had run across this a while back and totally forgot about that (obviously).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply