What I've done in the past is set up several global variable(s).

Create an Active-X task that updates the variable(s) between each failure and the sendmail task. 

For me the Active-X task was...

Use the On Failure to send an email.  I believe it is the send mail task.  Check out this link for suggested use http://www.sqldts.com/235.aspx.

If you set it up you...

I'd try the following:

select count(isnull(col1,"Null",col1) from testing_bug goup by col1

That should avoid the error all together and include the count for records with Null.

If memory serves....

Select Top 65000 from TableA into a temp table (TableB) and move that into Excel.

Create the 2nd sheet then Select * From TableA Where ID Not In (TableB).

Should work,...

This sounds like a perfect time to use a DTS Package.  Set up one DataSource (Your SQL Server) and one Destination (Your XLS file).

Set up a datapump task using either...

You'll need to create and Active-X task that creates and Excel object.  Once that is done you can create a for each loop based upon your PromoRef.

You will then be...

I would concur with your analysis.  If you decide that your ability to redo an import is easier and quicker than the tradeoff of having to fully log the import...

Waiting for the daily email will pay off for you.

Using an Active-X control in your DTS will give you access to the Excel Objects.  From there you will be able to...

Sure it can, but that one maybe better off as a udf.  The levels of nested cases could get ugly.  You could use case statements in your UDF, make it...

Eliminate the UDF here and ave the overhead.

An inline case statement will work for you.

SELECT CASE WHEN GMS_PR_ST_DESC.LOCALITY_NAME = ""

                   THEN GMS_PR_ST_DESC.TOWN_NAME

                   ELSE GMS_PR_ST_DESC.

Sounds like the perfect place for a for each loop or a cursor.  (personally I hate the cursors, but this is one place it fits rather well)

As long as you...

Drew, what your describing sounds like it will fit well with a UDF.  It may also work just as in-line sql in an update or insert statement too.

I agree with Dick...

I've done somthing similar using VBA in MS Access.  To do this in a DTS package you'd have to create an active-x task to replace the sendmail portion of your package. ...

How about scheduling a DTS package to handle your transactions.  You can set the schedule to kick of hourly or every minute if you have to.

If I remember correctly the issue with this is that the SQLAdmin account needs to have write access to the drive/folder on the server where you want to place the...