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.htmsql
