Not Outputting a File if Zero Results

[dpgirl]

I have a DTS package that runs a query and creates an output file (with header). I'd like to know if there's a way to modify the package so that the output file is deleted if the query produces zero results.

The reason I'm trying to do this is because the output file gets e-mailed to me to every day (per a batch file that sends out an e-mail upon successful completion of the DTS package). I'd only like to get the e-mail if there are results in the output file (ignoring the header). Any ideas?

 

[AlexCuse]

Try storing the row count for the query that is output to the file in a global variable. Then use an if statement in an activeX task to delete the file if Global Var value = 0.

If you were sending your file through DTS then you could directly control whether or not the email sends in this fashion, but it sounds like you are locked into the batch file used to send at this point.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[theniteowl]

My first thought would be not to even create the output file until some output was generated.
How many records are you talking about typically?
If it is not a lot, I would build the output as a string and write it all out at once including the header.
So you would first add the header to your string variable, then build the rest from your output. If your row count ends up being 0 then you skip the section that would create the output file.

You said it only sent the email upon the successful completion of your DTS package. You could alter that so that it only sends if it made it through creating an output file. If the logic for the email is separate from your DTS package can you intentionally throw an error in DTS to prevent the email from ocurring?

At my age I still learn something new every day, but I forget two others.