How to use FlatFileSourceErrorOutputColumn

Hot Topic

General network error in VB Application
DSO in SQL2005
SET RECOVERY SIMPLE generates error
GUID as variable
Adapters in SSIS?
Is it possible to use T-SQL to create a user in Active Directory?
Couple of Questions about RS2005
ASP.NET and SQL Server Express 2005 User Account problem
Attempted to read or write protected memory
Report Builder "Report Execution Error"

SQL Server

My Mobile DB.
Questions about Key performance indicators (KPI)
How to run an exe in the SSIS scripting task
Urgent: MSSQL2005(64-bits) MDAC
SSIS FTP TASK (sending file appends underscore to front of file)
Report Builder Help
Visual Studio 2005 Reports are not deploying at SQL server 2000
VS2005 / NonExecuteQuery / SQLDataSource
SQL Server Express EULA
Custom Security: How do you manage groups

How to use FlatFileSourceErrorOutputColumn

I am testing the functionality of XMLTextWriter in regard to handling errors.  The scenario is that I have a text file with some bad data.  I wrote a script component to take any rows containing bad data and write them out to an XML file.  Here is the entire script:


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"

Answer this question

How to use FlatFileSourceErrorOutputColumn

  • DevGal

    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

  • How to use FlatFileSourceErrorOutputColumn
Answer this question