Handling errors when importing

  • Hi,

    I have a package that imports csv files into a SQL Server table, and then moves the files to another folder. However, I can't work out (I'm quite new to SSIS!) how to handle an error. What I'd like to do is, if a particular csv throws up an error on importing, that the file gets moved to an alternate location, an email sent to a user, and then the package move on to import the next csv file.

    I have a ForEach Loop that contains a Data Flow task and a File System task. Should I:

    a) add an additional File System task and link the Data Flow task to it with a Failure precedence constraint, or:

    b) create an Event Handler (OnError or OnTaskFailed?) for the Data Flow task, with the Event Handler containing the File System task.

    Are there any settings I need to change so that the Package will carry on processing subsequent files?

    How should I send an email - should I use the Send Mail task (is this completely independant of Database Mail?)? Would it also be possible (this isn't vital!) to include in the email:

    1) the name of the csv file (the name is held in a variable);

    2) the error message (e.g. the duplicate key row in the example below from the Job History).

    Date16/07/2010 14:33:26

    LogJob History (Import Statements)

    Step ID1

    ServerTestSvr

    Job NameImport Statements

    Step NameImport Statements

    Duration00:00:01

    Sql Severity0

    Sql Message ID0

    Operator Emailed

    Operator Net sent

    Operator Paged

    Retries Attempted0

    Message

    Microsoft (R) SQL Server Execute Package Utility

    Version 10.0.1600.22 for 64-bit

    Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

    Started: 2:33:26 PM

    Error: 2010-07-16 14:33:27.12

    Code: 0xC0202009

    Source: Import File OLE DB Destination [42]

    Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "The statement has been terminated.".

    An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Cannot insert duplicate key row in object 'dbo.tblStatementData' with unique index 'IX_tblStatementData_1'.".

    End Error

    Error: 2010-07-16 14:33:27.12

    Code: 0xC0047022

    Source: Import File SSIS.Pipeline

    Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (42) failed with error code 0xC0202009 while processing input "OLE DB Destination Input" (55). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

    End Error

    DTExec: The package execution returned DTSER_FAILURE (1).

    Started: 2:33:26 PM

    Finished: 2:33:27 PM

    Elapsed: 0.203 seconds

    Thanks for any guidance,

    Barry

  • I would use a failure contraint and add the actions on that 'branch'.

    Are you wanting to redirect the row that caused the error or the entire file?

  • Hi Grasshopper,

    I did look at that first, but I couldn't access the System variable "ErrorDescription" (which I wanted to include in an email). So in the end I used the OnError event handler containing a Send Mail task, and the OnTaskFail event handler with a File System task to move the file to a different location (if there is an error in the file the whole file is not processed).

    After much Googling on how to get the package to continue if one file failed, I found these very useful articles:

    http://social.msdn.microsoft.com/Forums/pt-BR/sqlintegrationservices/thread/a8c841bf-8899-415f-a91f-1a4b17db6917

    http://agilebi.com/cs/blogs/jwelch/archive/2007/05/05/handling-multiple-errors-in-ssis.aspx

    http://agilebi.com/cs/blogs/jwelch/archive/2008/01/15/handling-multiple-errors-in-ssis-revisited.aspx

    Barry

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

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