MSSQL DTS Batch file

[Jezza]

I have created a small DTS package in an old SQL 2000 database that is used to deactivate accounts that have not logged on for 90 days and as an audit requirement have to output the accounts that have been been nullified to a log file.

As the auditing was a later requirement after deployment I went the log file route and used the following code. My strategy was to create a temporary output file (expireduser.log) which contents would then be copied into archive log and then expireduser.log was deleted for next runtime, like so:

Code: Select all

copy H:\UserArchive\archive.log+H:\UserArchive\expireduser.log
del H:\UserArchive\expireduser.log

Outside of the DTS shell this works fine but as soon as I ran the DTS package it failed so I changed the batch file to

Code: Select all

type H:\UserArchive\expireduser.log >>H:\UserArchive\archive.log
del H:\UserArchive\expireduser.log

and it works fine.

The question is why?

[HansV]

Mysterious - perhaps our resident BATch file expert can shed some light on it.

[John Gray]

The only thing I would do would be to specify the destination explicitly, as in
COPY A+B A
although it shouldn't be necessary.

But then again, I don't know what a "DTS shell" is!

[Jezza]

Thanks John,
One thought I had was when the DTS package runs it may be going "too fast" for the batch file and the ony thing I can think of is that COPY requires more time to complete than TYPE. I placed a delay in the steps of the package to see if that would work and again it failed to append to the file...weird.