I have spent last few hours to figure out whats wrong with my script transformation. I have SSIS that is used to load data from flat files to db. this data is very messy, variable column count, from time to time there is e.g. blank line. So I have created Script transformation to handle problem with variable column count and to detect those blank lines or unexpected line breaks. Here goes code of my Input0_ProcessInputRow:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
rowString = Row.Column0.Split(CType(";", Char()))
If i = 1 Then 'grab tempalte version - first row & first col
ver = rowString.GetValue(0).ToString()
ElseIf i > Me.Variables.DataStart Then 'line where real data starts
With Output0Buffer
Try
.AddRow()
.ico = rowString.GetValue(0).ToString()
.verze = Convert.ToInt16(rowString.GetValue(1))
.schvalovatel = rowString.GetValue(2).ToString()
.sloupec = rowString.GetValue(3).ToString()
.radek = rowString.GetValue(4).ToString()
.sablona = ver
If rowString.GetUpperBound(0) < 6 Then 'last two missing
.tvalue_IsNull = True
.nvalue_IsNull = True
Else
tString = rowString.GetValue(5).ToString()
If Not String.IsNullOrEmpty(tString) Then
.tvalue = tString
Else
.tvalue_IsNull = True
End If
nString = rowString.GetValue(6).ToString()
If Not String.IsNullOrEmpty(nString) Then
nString = Replace(nString, ".", ",")
.nvalue = Decimal.Parse(nString)
Else
.nvalue_IsNull = True
End If
End If
Catch ex As Exception
MsgBox(i.ToString())
Me.ComponentMetaData.FireError(-1, "Script Component", "err: " _
+ Me.Variables.CurFile _
+ vbNewLine + "line: " + i.ToString() _
+ vbNewLine + "detail: " + ex.Message, String.Empty, 0, True)
End Try
End With
End If
i = i + 1
End Sub
It works flawleslly on correct data, but when there is error like blank line, I would like to catch this by catching Exception and show MsgBox with line where error is located. But this not work as expected - during runtime, Script Transformation fails with generic error (runtime error in user script). Strange thing is, that when I put breakpoint on catch block, during runtime, execution hangs on and visual studio editor gets opened and closed, and transformation completes ignoring any errors... Anybody able to help? Thanks.