Post reply

SSIS Errro propogation with for each loop

  • July 23, 2012 at 12:57 pm

    #264458

    Hi Guys

    Just written my first SSIS package, which loops through all excel files in a folder and imports to SQL server. If there are no errors, it moves the file to a "Done" folder; if there are errors, it moves the file to an error folder and writes an error log (named ERR_<Filename>.txt) too. It then moves onto the next file. (Everything is in a single sequence container)

    What I'd like to do at the end of all files being processed get a count of all files that failed. If this is > 0 send an e-mail with a summary of all errors.

    I can't figure out to get the error info at the end of the sequence

    Any help appreciated

    Regards

    Mark

  • July 23, 2012 at 1:50 pm

    #1516455

    Hi Mark,

    You could use a package variables to keep a running count of failed files, and maybe another to keep summary details of the errors that are handled.

    or

    You could use SSIS Logging and store the results to a table, then query that table for the last run. Look for the OnError event records, if they exist then build the email with the details in the logging tables.

    Thanks,

    Dave

  • July 23, 2012 at 2:03 pm

    #1516460

    Thanks Dave

    If I understand you correctly, I'd create variables at the package level and then update them in the data flow event handler (or the foreach loop?). (I need one error count per file). Is there a trick to getting the most meaningful error message instead of all the garbage it spouts out?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply