July 16, 2010 at 7:43 am
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).
Date 16/07/2010 14:33:26
Log Job History (Import Statements)
Step ID 1
Server TestSvr
Job Name Import Statements
Step Name Import Statements
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
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
July 19, 2010 at 7:52 pm
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?
July 20, 2010 at 10:02 am
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://agilebi.com/cs/blogs/jwelch/archive/2007/05/05/handling-multiple-errors-in-ssis.aspx
Barry
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply