Output Buffer Remove Row?

I am using a script component to create the output buffer dynamically. I use the Outputbuffer.AddRow() call. I then set all the fields I want, and its added to the output and later inserted into the database. If a field value fails it causes an error, but the record is partially inserted upto the point where the set field command caused the error. So if I set 10 fields, and it fails on field 5 it inserts data for the 5 fields that worked and nulls into the others.

As a result I have a try catch clause, and if it fails I want to cancell the addition of the new row. Is there a command like RemoveRow(), rollback, etc that can be used to not insert the record in error?

Sample code..

Try

PaymentOutputBuffer.AddRow()

PaymentOutputBuffer.Sequence = pi + 1

PaymentOutputBuffer.RecordID = Row.RecordID

PaymentOutputBuffer.PaymentMethod = PaymentArray(pi)

Catch e As Exception

PaymentErrorOutputBuffer.removecurrentrow(?)

End Try

Can you provide more of your script? Maybe even all of it?|||

The script is very long. I do the same basic task in three scripts for three different feeds, this is the shortest one. The only difference (basically) between this and the others is the number of field in the outputbuffer. Ok.. Here is the code:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.Text

Imports Company.SSIS.Functions

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim PaymentRecord As String = System.Text.Encoding.Unicode.GetString(Row.PaymentMethods.GetBlobData(0, CInt(Row.PaymentMethods.Length)))

Dim PaymentArray() As String

Dim pi As Integer

PaymentArray = Parse.StringParse(PaymentRecord, "\|")

For pi = 0 To PaymentArray.Length - 1

If PaymentArray(pi) <> "" Then

Try

PaymentOutputBuffer.AddRow()

PaymentOutputBuffer.Sequence = pi + 1

PaymentOutputBuffer.ServiceRecordID = Row.ServiceRecordID

PaymentOutputBuffer.PaymentMethod = PaymentArray(pi)

Catch e As Exception

PaymentErrorOutputBuffer.AddRow()

PaymentErrorOutputBuffer.ErrorRecordID = Row.ServiceRecordID

PaymentErrorOutputBuffer.ErrorCode = e.Message

PaymentErrorOutputBuffer.ErrorData.AddBlobData(Encoding.UTF8.GetBytes(PaymentArray(pi)))

End Try

End If

Next

End Sub

End Class

The Company.SSIS.Functions import is a dll I wrote. It has the Parse.StringParse(PaymentRecord, "\|") call. It is simply a regex function. I listed it below.. but basically splits the delimited field into multiple rows. So the goal is that it is called per row, splits the data in the row into an array and stores it as records. If the paymentmethod store fails, the record still gets inserted into the datase with the sequence and service record id. The catch directs creates a second output, that i then store to my error table. I essentially want the catch to still send to my error table, but for the first output not to be created if it fails and enters the catch.

Public Class Parse

Public Shared Function StringParse(ByVal StringValue As String, ByVal ParseValue As String) As String()

If Replace(StringValue, "", Nothing) Is Nothing Then

Return Split(StringValue)

Else

Dim pattern As String = ParseValue & "(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"

Dim r As RegularExpressions.Regex = New RegularExpressions.Regex(pattern)

Return r.Split(StringValue)

End If

End Function

End Class

|||What are some of the reasons why the assignment to PaymentMethod could fail?|||

Payment method is varchar(20) field. The output is defined as String 20. Primary reason of failure is text truncation. On other code it may be bad date/time stamp, non integer value, etc. Typically it is a data error.

I can do a specific check on payment method to make sure its not in error before calling the addrow() so that the output is not called unless it will succeed. But for scenarios I don't think of, or conditions where I need to check 20+ fields this is not as practical. So I was hoping to use a try/catch process to insert the row if its good, otherwise write to the error table.

|||Yep, I hear ya... I just wanted you to think about performing data validation up front, and you have. Good.

Some of the other code experts may have to chime in now. |||Remove the current row with the call to the PipelineBuffer.RemoveRow() method. The script component does not expose the PipelineBuffer.RemoveRow() method directly, but its does expose the PipelineBuffer(s) in PrimeOutput, on which RemoveRow can be called.

Taking your scenario as example, save the underlying pipeline buffers, and call any PipelineBuffer method on them, including RemoveRow.

Imports System

Imports

Microsoft.SqlServer.Dts.Pipeline

Public Class ScriptMain

Inherits

UserComponent

Private

normalBuffer As PipelineBuffer

Private

errorBuffer As PipelineBuffer

Public Overrides Sub

Input0_ProcessInputRow _

(ByVal

Row As Input0Buffer)

Try

With

PaymentOutputBuffer

.AddRow()

.FirstString =

Row.GeneratedStr1

End

With

Catch

ex As Exception

With

PaymentErrorOutputBuffer

.AddRow()

.ErrorMessage =

ex.Message.Substring(0, _

Math.Min(ex.Message.Length,

250))

End

With

normalBuffer.RemoveRow()

' Remove

the row from normal, as normalBuffer

' is the

underlying PipelineBuffer

' for

PaymentOutputBuffer

End Try

End Sub

Public Overrides Sub

PrimeOutput(ByVal Outputs As Integer, _

ByVal

OutputIDs() As Integer,

_

ByVal

Buffers() As

Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

' save

underlying PipelineBuffer to allow for calls to

'

PipelineBuffer methods

normalBuffer = Buffers(0)

errorBuffer = Buffers(1)

MyBase.PrimeOutput(Outputs,

OutputIDs, Buffers)

End Sub

End Class

|||Thanks.. .that works and does what I need. I was curious about the Buffers(x) value and if there is another way to set it to the specific outputbuffer. I tried to look at the help on the Pipeline buffer, but was not able to determine it. I want to be able to reference the Buffers(PaymentOutputBuffer) vs. a 0 so that if people add additional buffers, etc it will not break. Is there a way to make this go off the actual buffer name?|||Yes, the buffers can be referenced by name.

The OutputIDs array parameter to PrimeOutput has the integer buffer ids (not the offset) in coordinated order with the Buffers array.

The buffer's name is available via a call to ComponentMetaData.OutputCollection.FindObjectById(<id from OutputIds here>).Name.

See the following BOL reference for the OutputCollection methods: ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/T_Microsoft_SqlServer_Dts_Pipeline_Wrapper_IDTSOutputCollection90_Members.htm