Imports System
Imports System.Data
Imports System.Math
Imports System.Xml
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim xWriter As XmlTextWriter
Public Overrides Sub PreExecute()
xWriter = New XmlTextWriter(Me.Connections.PatientErrors.ConnectionString, Nothing)
xWriter.WriteStartDocument()
xWriter.WriteComment("Patient data rows that failed validation")
xWriter.WriteStartElement("x", "bad_data", "http://www.domain.com")
xWriter.WriteAttributeString("FileName", Me.Connections.PatientErrors.ConnectionString)
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
xWriter.WriteStartElement("PATIENT_ERROR")
xWriter.WriteStartElement("ERROR_CODE")
xWriter.WriteValue(Row.ErrorCode)
xWriter.WriteEndElement()
xWriter.WriteStartElement("ERROR_COLUMN")
xWriter.WriteValue(Row.ErrorColumn)
xWriter.WriteEndElement()
xWriter.WriteStartElement("SOURCE_COLUMN")
xWriter.WriteValue(Row.FlatFileSourceErrorOutputColumn)
xWriter.WriteEndElement()
xWriter.WriteEndElement()
End Sub
Public Overrides Sub PostExecute()
xWriter.WriteEndElement()
xWriter.WriteEndDocument()
xWriter.Close()
End Sub
End Class
The script receives the following columns as Input:
1) Flat File Source Editor Output Column
2) ErrorCode
3) ErrorColumn
I can write #2 and #3 to the xml file with no problem, although I don't know what they mean. I don't know what to do with #1.
All I can see is that the value of "Row.FlatFileSourceErrorOutputColumn" is a BLOB, but I don't know how to access the data within it.
My questions are:
1) What information is contained in each one of these input columns?
2) How can I access the information in "Row.FlatFileSourceErrorOutputColumn"?1) is the row that failed, so the whole string of the input file. If you can change your input to be under 8000 (4000 unicode) then you can use normal strings. if not the blob is a byte array and so you need to handle it as you would any other byte array and convert it to a string.
2) is the error code of the error
3) is the lineage Id of the column that failed causing the row to error.
If you wait a few days you can get hold of a component that provides the name of the column that failed. keep your eyes on my blog http://sqljunkies.com/weblog/simons
No comments:
Post a Comment