Post reply

SSIS Package - Exception Handling

  • July 22, 2008 at 1:15 pm

    #381868

    Hi All,

    I'm trying to build an SSIS package. The process flow of the package would be:

    Step 1:

    1.Read the data from table A.

    2.Validate if the data in table A exists in table B. If there are any records that contains invalid data OR data exists in table A but not table B, error and email notify the invalid records to a distribution group.

    3.Upon validation, hold for union with the set of records in Step 2.

    Step 2:

    1.Read the data from a CSV file.

    2.Validate each field in file. If there are any invalid records, publish the error records to the same location and filename with "ERROR_" appended to the begining.

    3.Upon validation of records, hold for union with Step 1 records.

    Step 3:

    Union records from Step 1 and Step 2 and write them into a table.

    My question is, how do I group only the invalid records and publish to an email distribution group in Step 1 and to an error file in Step 2.

    Any response would be appreciated.

    Thanks in advance,

    -Amith Vemuganti

  • July 28, 2008 at 5:00 am

    #849071

    I think in each case you could add a derived column called FLAG, or something like, which holds the information about each row whether it is VALID, INVALID or MISSING, or whatever.

    Then, use a conditional split transformation to send the rows off to wherever they need to go (flat file, database, etc) based on the value of that flag column.

    Bit of logic to see if the file has been created (or rows written to db, etc) to send off error emails.

    Hope this helps,

    Tom

    Life: it twists and turns like a twisty turny thing

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

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