0

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.

bazinac
  • 668
  • 5
  • 22
  • 1
    This `rowString = Row.Column0.Split(CType(";", Char()))` is not in the try..catch block. Is your error there? What version of SSIS are you using? < 2012 does not support breakpoints in script components. Also, for a blank line this `rowString.GetValue(0).ToString()` would be referring to a null array and it is also not in the try..catch block – Mark Wojciechowicz Dec 16 '14 at 16:25
  • Thanks Mark, but the error is not related to the line you mention. I am getting index out of array bounds error, which cannot happen as first line is always full. And morevoer I have tried to put Try statement before line you mention and situation was the same. I am using SSIS 2012. – bazinac Dec 16 '14 at 21:46
  • Does a blank row contain the row delimiter? Perhaps the error is in the call of the method – Mark Wojciechowicz Dec 16 '14 at 21:58
  • Yes, it containst row delimiter. What I do not understand is, why Try Catch is not firing exception... Even when I surrounded whole content of this Sub, it still fails on generic error "index was outside the bounds of an array." Still no luck. – bazinac Dec 17 '14 at 11:25
  • Could you post the whole error? – Mark Wojciechowicz Dec 17 '14 at 11:49
  • It says "Script Component Error: index was outside the bounds of an array" `in ScriptComponent_487a049be0d64db3a8b2d44cccec9d10.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row) in ScriptComponent_487a049be0d64db3a8b2d44cccec9d10.UserComponent.Input0_ProcessInput(Input0Buffer Buffer) in ScriptComponent_487a049be0d64db3a8b2d44cccec9d10.UserComponent.ProcessInput(Int32 InputID, String InputName, PipelineBuffer Buffer, OutputNameMap OutputMap) in Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.ProcessInput(Int32 InputID, PipelineBuffer buffer)` – bazinac Dec 17 '14 at 13:05

0 Answers0