March 20, 2008 at 4:23 am
Hello guys,
i've read this forum and also other SQL Server 2005 Integration Services forums and the problem with getting the column name of the rows that goes in error for a component (Derived Column, Lookup, OLDB Destination etc ) it's very difficult and almost impossibile .
My problem is the same as for other users : i have a SSIS witch take data from an ORACLE db and inserting them into a SQL db, with transformation , lookups, etc and i would like that all rows that going into errors UnionAll them and inserting them into an Error Table. The problem with error description i've resolved it into a Script Component with the ComponentMetaData.GetErrorDescription but remain the problem with the Column Name who generate the error. I'm on the edge to give up for the the column who generate the error, but can you help me, instead of getting the name of the column, the name of the component who generate the error ? That will help me very much, because if i have 10 Lookup component ,i know which one generate the error. Can I do that inside the Script ? VisualBasic programmer please help me because at programming i s**k 🙁
Thank you very much !
March 20, 2008 at 11:23 pm
Hi ,
I am giving the code of a function that will retrieve the column name giving the error. The idea behind achieving this is to track down the lineage id of the column code backto the input column, what i did was to first change the .dtsx file to .xml and then parse the file. By using the tags and lineage id of the columns i retrieved the name of the error causing colum.
Here is the code :
'Retrieve column name which contains bad data
'Summary : Steps to get bad data column name
' It gets lineage id for with id matching with Row.ErrorColun from package code file(.dtsx[XML]) converted into .xml file,
' Search and get value for property with value = lineageid + 1 and name as 'SourceInputColumnLineageID',
' Search and get vaue of name attribute of with id=above property value, In case if bad data is in derived column;
' it matches id=lineageid
Public Function RetrieveErrorColumnName(ByVal errorcolumn As String, ByVal codefilepath As String) As String
Try
Dim resultingColumnName As String = String.Empty
Dim outputColumnId As String = String.Empty
Dim lineageId As String = String.Empty
Dim propertyId As String = String.Empty
Dim columnName As String = String.Empty
Dim nodeName As String = String.Empty
Dim subNodes As XPath.XPathNodeIterator
Dim idAttribute As String = String.Empty
Dim isExitWhile As Boolean = False
Dim xPathDocument As XPath.XPathDocument = New XPath.XPathDocument(codefilepath)
Dim navigator As XPath.XPathNavigator = xPathdocument.CreateNavigator()
Dim nodes As XPath.XPathNodeIterator = navigator.SelectDescendants(XPath.XPathNodeType.Element, True)
'Get input column lineageid where columnid =errorcolumn
While nodes.MoveNext()
If (nodes.Current.Name = ELEMENT_INPUT) Then 'input
nodeName = nodes.Current.GetAttribute(ATTRIBUTE_NAME, String.Empty)
If (nodeName = ATTRIBUTE_INPUT_NAME) Then ' attribute name
subNodes = nodes.Current.SelectDescendants(XPath.XPathNodeType.Element, True)
While subNodes.MoveNext()
If (subNodes.Current.Name = ELEMENT_INPUTCOLUMN) Then
idAttribute = subNodes.Current.GetAttribute(ATTRIBUTE_ID, String.Empty)
If (idAttribute = errorcolumn) Then
lineageId = subNodes.Current.GetAttribute(ATTRIBUTE_LINEAGEID, String.Empty)
isExitWhile = True
Exit While
End If
End If
End While
End If
End If
If (isExitWhile) Then
Exit While
End If
End While
'Get property value with id=above lineageid + 1
nodes = navigator.SelectDescendants(XPath.XPathNodeType.Element, True)
While nodes.MoveNext()
If (nodes.Current.Name = ELEMENT_PROPERTY) Then
nodeName = nodes.Current.GetAttribute(ATTRIBUTE_NAME, String.Empty)
propertyId = nodes.Current.GetAttribute(ATTRIBUTE_ID, String.Empty)
If nodeName = ATTRIBUTE_SRCLINEAGEID And propertyId = CStr(CInt(lineageId) + 1) Then
outputColumnId = nodes.Current.Value
Exit While
End If
End If
End While
'Get output column name with id= above property value
isExitWhile = False
nodes = navigator.SelectDescendants(XPath.XPathNodeType.Element, True)
While nodes.MoveNext()
If (nodes.Current.Name = ELEMENT_OUTPUT) Then '
nodeName = nodes.Current.GetAttribute(ATTRIBUTE_NAME, String.Empty)
If (nodeName = ELEMENT_OUTPUT_DATAREADER) Then ' attribute name is DataReader Output
subNodes = nodes.Current.SelectDescendants(XPath.XPathNodeType.Element, True)
While subNodes.MoveNext()
If (subNodes.Current.Name = ELEMENT_OUTPUTCOLUMN) Then
idAttribute = subNodes.Current.GetAttribute(ATTRIBUTE_ID, String.Empty)
If (outputColumnId <> String.Empty) Then
If (idAttribute = outputColumnId) Then
columnName = subNodes.Current.GetAttribute(ATTRIBUTE_NAME, String.Empty)
resultingColumnName = columnName
isExitWhile = True
Exit While
End If
ElseIf (idAttribute = lineageId) Then
columnName = subNodes.Current.GetAttribute(ATTRIBUTE_NAME, String.Empty)
resultingColumnName = columnName
isExitWhile = True
Exit While
End If
End If
End While
ElseIf (nodeName = ELEMENT_OUTPUT_DATADERIVED) Then ' attribute name is Derived Column Output
subNodes = nodes.Current.SelectDescendants(XPath.XPathNodeType.Element, True)
While subNodes.MoveNext()
If (subNodes.Current.Name = ELEMENT_OUTPUTCOLUMN) Then
idAttribute = subNodes.Current.GetAttribute(ATTRIBUTE_ID, String.Empty)
If (idAttribute = lineageId) Then
columnName = subNodes.Current.GetAttribute(ATTRIBUTE_NAME, String.Empty)
resultingColumnName = Me.Variables.DerivedColErrMsg & columnName
isExitWhile = True
Exit While
End If
End If
End While
End If
If (isExitWhile) Then
Exit While
End If
End If
End While
Return resultingColumnName
Catch ex As Exception
metaData.FireError(Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext, CBool(1))
End Try
End Function
November 6, 2008 at 2:38 pm
How do you implement this within SSIS
May 18, 2009 at 2:51 am
There has got to be a simpler way to get the column name from the lineageID?
January 25, 2011 at 10:09 pm
I found this interesting http://blogs.msdn.com/b/helloworld/archive/2008/08/01/how-to-find-out-which-column-caused-ssis-to-fail.aspx and simple
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply