Script Source: Error on truncation etc

I have a script source to deal with a source that has different "record types" (first 3 columns are the same then the remaining 2 to 30 columns are different based on the record type).

Script source was working fine... then one of the columns that I had set to String with length of 2 came in with a length of 3 (which is not per spec)... instead of failing - all the columns after the one that had the bad value were null and the script just stopped as soon as it hit that.. AND said it was success. Which means it imported the data incorrectly and since the script says it was a sucess you'd never know anything went wrong and it only imported 30 rows instead of 10k+

Any ideas on how to capture this error?

Code (shortened with .... but should be enough - sorry the forum butchers the code formatting - if someone has a tip for pasting code from VS let me know):

Imports System

Imports System.Data

Imports System.Math

Imports System.IO

Imports System.Convert

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub CreateNewOutputRows()

Dim oCurrentFile As File

Dim oStreamReader As StreamReader

Dim sCurrentLine As String

Dim aCurrentLine() As String

Try

oStreamReader = oCurrentFile.OpenText(Me.Variables.SourceName)

sCurrentLine = oStreamReader.ReadLine()

Do While sCurrentLine IsNot Nothing

aCurrentLine = sCurrentLine.Split(Chr(44))

Select Case aCurrentLine(2) ' This is the 3rd column.. "Record Type" which tells us what type of record it is and how many columns etc (Chr(44) is a comma)

Case "BF"

BFRecordsBuffer.AddRow()

BFRecordsBuffer.TimeStamp = aCurrentLine(0)

BFRecordsBuffer.EyeCatcher = aCurrentLine(1)

BFRecordsBuffer.RecordType = aCurrentLine(2)

BFRecordsBuffer.Sym = aCurrentLine(3)

....

BFRecordsBuffer.RecordCount = Convert.ToInt32(aCurrentLine(24))

...

If aCurrentLine.GetLength(0) >= 30 Then

BFRecordsBuffer.SeqNo = aCurrentLine(29)

End If

Case "QF"

QFRecordsBuffer.AddRow()

QFRecordsBuffer.Timestamp = aCurrentLine(0)

QFRecordsBuffer.EyeCatcher = aCurrentLine(1)

QFRecordsBuffer.RecordType = aCurrentLine(2)

...

....

End Select

sCurrentLine = oStreamReader.ReadLine()

Loop

BFRecordsBuffer.SetEndOfRowset()

QFRecordsBuffer.SetEndOfRowset()

....

Catch ex As Exception

Me.ComponentMetaData.FireError(1, "Script Component", ex.Message, "", 0, True)

Finally

oStreamReader.Close()

End Try

End Sub

End Class

Chris Honcoop wrote:

Code (shortened with .... but should be enough - sorry the forum butchers the code formatting - if someone has a tip for pasting code from VS let me know):

I can help with that. Go here: http://www.jtleigh.com/people/colin/software/CopySourceAsHtml/

Yuo can see an example of the result here:

http://blogs.conchango.com/jamiethomson/archive/2006/12/19/SSIS-Nugget_3A00_-Extract-last-word-from-a-sentance-using-regular-expressions.aspx

-Jamie

|||

Thanks Jamie, I installed it and don't see it as an option under add-ons.. nor where it mentions it will be in the menus - am I missing something? In re: to your blog welcome to the wonderful world of regex ;)

Any ideas on the script source truncation/data type mismatch "non error"?

|||

Chris Honcoop wrote:

Thanks Jamie, I installed it and don't see it as an option under add-ons.. nor where it mentions it will be in the menus - am I missing something? In re: to your blog welcome to the wonderful world of regex ;)

Any ideas on the script source truncation/data type mismatch "non error"?

It doesn't work in VSA unfortunately so copy and paste it into a regular VB.Net project and copy as html from there.

Not sure about the error you're getting i'm afraid.

-Jamie

|||Its something with the way I do the error trapping.. I comment out the try / catch etc and the script source will fail when the columns are bad. Of course getting a useful error message about which column etc is experiencing the problem is an entirely different headache.|||To have the standard SSIS options of ignore,redirect, and fail

component in your own custom script component, there are a couple of

changes to consider.

1. Add a try/catch inside your loop. As written, when the first error

happens (e.g. "ABC" rather than "AB"), the exception will be caught

outside of the loop, and no rows beyond that will be processed. This

change will allow the implementation of the ignore and/or redirect

options, and of course, continued processing.

2. In the newly added try/catch, add an catch for the

DoesNotFitBufferException. This will "catch" the error. You can also simply ignore this particular

exception type, and the string will be truncated, or re-throw the

exception, and the component will fail, or lastly, redirect it (

essentially adding a row to an "error" output on your component)

3. To report which column name/index is experiencing the problem will

require a cache of column information (names, types,lengths, etc) by

overriding the PreExecute function, and then grabbing a reference to

the the base buffers by overriding the PrimeOutput function. The

column cache and base buffers can then be referenced in your

CreateNewOutputRows implementation.

Hopefully this will help you along your way. If not, I'll find some script component code and post that as well.