SSIS: Try/Catch not working in Script Transformation

1.2k views Asked by At

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.

0

There are 0 answers