January 9, 2009 at 8:37 am
Dear all,
I am using SQL Server (2005) Stored Procedures through the SSIS "Execute SQL" Task and we use RAISERROR to communicate some error information to the calling code (either through text or error number).
After my "Execute SQL" Task fails, how/where can I find the detail of the error(s)?
I tried to browse through the DTS object properties but can't find a way to the errors.
I was hoping I might get there through the DTS.Connections but no joy either.
To make matters worse, I read this in the books online (2008)
OLE DB and ADO connection managers ignore the result sets that occur after the first result set. Therefore, these connection managers ignore an error returned by an SQL command or a stored procedure when the error is not part of the first result set.
Should I understand that the error details are lost? :ermm:
Thanks for your help
Eric
PS: I tried using direct sql and it seems to make no difference
January 10, 2009 at 2:57 pm
May be this article can help:
January 12, 2009 at 6:49 am
go to event handlers tab
add a execute sql task
before that create a table[error] with following column names
error name, error code, task failed, time of execution, etc
in the execute sql task add a query
insert into error values(?,?,?,?,?)
go to parameter mapping.
in variable name select appropriate system variable. direction input. parameter name as 0,1,2,3....and goes on
u r having so many usefull system variables.. make use of it
January 12, 2009 at 11:02 am
For those interested (it can happen)
The solution was effectively to
- create a table in the database (that is, if you want to store the info)
- create a Package (or Task) Event Handler for the OnError event
- Within this handler, use a EXECUTE SQL task to store the details of the error
The answer is actually documented in the book "Expert SQL Server 2005 Integration Services", chapter 7 page 226.
I changed the adhoc SQL into a stored procedure because the error description may itself break the dynamic sql.
To answer my initial question, one might want to store the "Last Error details" in a specific User Variable that would be available everywhere (because I "think" the error is only temporarily available through the event handler).
Thanks for your help guys...
🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply