Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««7891011

Error Handling in SSIS Expand / Collapse
Author
Message
Posted Tuesday, May 4, 2010 11:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:46 PM
Points: 54, Visits: 239
Jack, I was able to come up with a way to crosstab the xml values. You could also use this to run a report I guess.

Make sure to change the ErrorTask and RunDate variables to values in your own load_errors table.

You might want to add this to your original post.

Thanks again

BEGIN
-- Define Variables
DECLARE @ListCol VARCHAR (1000)
, @Query VARCHAR (2000)
, @ErrorTask VARCHAR (100)
, @RunDate VARCHAR (100)

SET @ListCol = ''
SET @Query = ''
SET @ErrorTask = 'DFT Load Credit Card'
SET @RunDate = '2010-04-29 14:08:00'

-- Create dynamic column list given Error Task name
SELECT @ListCol = (SELECT DISTINCT a.name + ','
FROM load_errors b
cross apply error_details.nodes('//field') AS xmlrows(data)
cross apply (SELECT xmlrows.data.value('./@name[1]','varchar(120)') AS field_name
) AS a(name)
WHERE b.error_task = @ErrorTask
FOR XML PATH(''))
SET @ListCol = SUBSTRING(@ListCol, 1, LEN(@ListCol)-1)


-- Create dynamic query
SET @Query = '
SELECT *
FROM (SELECT t.load_error_id
, t.package_run_date
, t.error_task
, t.error_step
, t.error_code
, t.error_desc
, f.name
, f.value
FROM load_errors t
--= blow out the rows from the xml column
cross apply error_details.nodes(''//field'') as xmlrows(data)
--= convert the nodes to two columns, field: name and value
cross apply (select xmlrows.data.value(''./@name[1]'',''varchar(120)'') as field_name
, xmlrows.data.value(''./@value[1]'',''varchar(120)'') as field_value ) as f(name,value)
WHERE t.error_task = ''' + @ErrorTask + '''
AND t.package_run_date = ''' + @RunDate + '''
) inner_table
pivot (max(inner_table.value)
for inner_table.name in (' + @ListCol + ')
) pvtfname
'

-- Execute dynamic query
EXEC(@QUERY)
END

Post #915524
Posted Thursday, December 16, 2010 4:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 10:44 AM
Points: 39, Visits: 392
Its a wonderful article.
I learned a lot.

Tinku
Post #1036208
Posted Friday, September 28, 2012 12:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 1:59 PM
Points: 17, Visits: 416
This article fails to mention this technique will only log error messages generated within SSIS. Errors reported by the database engine (including SQL Server) will receive the generic "no status is available" error. In those cases, you cannot get the detailed error in the redirect that you would normally see if a package was set to fail on error. This is just another example of functionality included in most ETL tools, but lacking in SSIS.
Post #1366072
Posted Wednesday, October 3, 2012 7:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 10,342, Visits: 13,352
Shon,

In my experience, I have gotten the error message generated by SQL Server back when using this. It's why I wrote the code. The errors I was experiencing were from SQL Server, like Data Type overflow errors, which are coming from SQL Server.

What types of errors are you seeing that aren't getting descriptions returned?




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1367693
Posted Wednesday, October 3, 2012 1:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 1:59 PM
Points: 17, Visits: 416
It seems like the issue is column level vs row level errors. The method call is showing predefined errors, but does not return the same message you see from the db engine. Here are two examples.

1. I have a unique index setup on a table. When we try to insert a dup, the error raised by the db engine is "Cannot insert duplicate key row in object 'TableName' with unique index 'Index_Name'. The duplicate key value is ...." The error we get in SSIS is "The data value violates integrity constraints. "

2. I have a trigger on a table that checks for overlapping dates, and raises a 16 level error with rollback. Instead of getting the error message generated in the trigger, we get "No status is available".

In searching for a method to return the error generated by the db, I find that it is not possible in SSIS. What makes it even more disappointing is that we know SSIS does have these errors at the package level because we get them in a package level OnError event handler if the package is setup to fail on error, but there is apparently no way to have this information at the row level during a redirect. I hope I am wrong about this, but at this point I have not found a way to do it in SSIS even though it would be a simple tsql script to accomplish the same thing.
Post #1368009
Posted Wednesday, July 9, 2014 10:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 10:10 AM
Points: 19, Visits: 80
Hello, Great article.

Actually the initial article is missing an "&".

I am having some issues with it though.

I tried debugging it and it fails on:

Dim objColumn As Object = columnValue.GetValue(Row, Nothing)

It never gets to 13 message. This is on a Date column that is NULL.

I see there is a check like "If IsNothing(objColumn) " but it does not even get to that point.

This is the piece of code that is failing at.
------------------------------------------------------------------

If Not (strAttributeName.Contains("ErrorCode") Or strAttributeName.Contains("ErrorColumn")) Then
' get the value for the column
MsgBox("11")

columnValue = rowType.GetProperty(strAttributeName)
MsgBox("12")

Dim objColumn As Object = columnValue.GetValue(Row, Nothing)
MsgBox("13")

' if the value is null set to empty string
If IsNothing(objColumn) Then
MsgBox("21")
strValue = String.Empty
MsgBox("22")
Else
MsgBox("31")
strValue = objColumn.ToString()
MsgBox("32")
End If
' append the node to the xml string
Post #1590839
Posted Wednesday, July 9, 2014 10:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 10,342, Visits: 13,352
Matteo,

What version of SSIS are you using? This article was written a long time ago and the code even longer using SSIS 2005. There may be changes somewhere that need to be addressed. I haven't looked at this code in at least 5 years. If I have some time tonight after work I can possibly look into what you are seeing.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1590851
Posted Wednesday, July 9, 2014 10:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 10:10 AM
Points: 19, Visits: 80
Thanks for the quick reply. The version I am using is 2008 R2.

I can get it to work if I can wrap that statement in a Try Catch but I was wondering what makes it fail.
Post #1590859
« Prev Topic | Next Topic »

Add to briefcase «««7891011

Permissions Expand / Collapse