Split multi value column into multiple records

Question

I am trying to split multi value column into multiple records in script component of ssis. I have one column with the name of the actors and the other - with the films they starred (separated by comma). Now I want to split the information so that each raw contains the name of the actor and the title of only one film. I`m using the code below but something is wrong with it. Can anybody help me?

I use the code I found and only changed Input and Output into Cyrillic to adapt to my program`s language.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper



Public Class ScriptMain
    Inherits UserComponent



    Public Overrides Sub Вход0_ProcessInputRow(ByVal Row As Вход0Buffer)

        ' This array would hold the single numeric values after you tokenize the input column '
        Dim inputNumericValuesArray As Array
        ' This varibale would hold one value from the array during the loop '
        Dim currentNumericValue As String



        ' Tokenize the NumericValues column by splitting it based on a semicolon separator '
        inputNumericValuesArray = Row.ПреобразованиеданныхПреобразованиевЮникодknownForTitles.Split(","c)



        ' Loop on the retrieved tokens adding each in a new row '
        For Each currentNumericValue In inputNumericValuesArray
            ' Create a new row '
            Выход0Buffer.AddRow()



            ' Get the TaskName from the source row and assign it to the output row without any changes '
            Выход0Buffer.nconst = Row.ПреобразованиеданныхПреобразованиевЮникодnconst



            ' Parse the current numeric value as decimal '
            ' and assign it to the Numeric value of the output row '
            Выход0Buffer.knownForTitle = Decimal.Parse(currentNumericValue)
        Next
    End Sub

End Class

The error is 0xC0047062. The full description of the error is below:

Ошибка: 0xC0047062 в Заполнение name_title 1 1, Компонент скриптов [23]: System.NullReferenceException: Ссылка на объект не указывает на экземпляр объекта.

Possible duplicate of [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it). If my use of Google Translate is correct, I suggest checking that `Row.ПреобразованиеданныхПреобразованиевЮникодknownForTitles` has a value. — Andrew Morton, Jul 12 '19 at 14:55

score 0 · Accepted Answer · answered Jul 13 '19 at 20:41

Simply check if input columns are Null before using them:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper



Public Class ScriptMain
    Inherits UserComponent



    Public Overrides Sub Вход0_ProcessInputRow(ByVal Row As Вход0Buffer)

        ' This array would hold the single numeric values after you tokenize the input column '
        Dim inputNumericValuesArray As Array
        ' This varibale would hold one value from the array during the loop '
        Dim currentNumericValue As String


        If Not Row.ПреобразованиеданныхПреобразованиевЮникодknownForTitles_IsNull Then

            ' Tokenize the NumericValues column by splitting it based on a semicolon separator '
            inputNumericValuesArray = Row.ПреобразованиеданныхПреобразованиевЮникодknownForTitles.Split(","c)



            ' Loop on the retrieved tokens adding each in a new row '
            For Each currentNumericValue In inputNumericValuesArray
                ' Create a new row '
                Выход0Buffer.AddRow()



                ' Get the TaskName from the source row and assign it to the output row without any changes '
                If Not Row.ПреобразованиеданныхПреобразованиевЮникодnconst_IsNull Then
                    Выход0Buffer.nconst = Row.ПреобразованиеданныхПреобразованиевЮникодnconst
                Else
                    Выход0Buffer.nconst)IsNull = True
                End If 


                ' Parse the current numeric value as decimal '
                ' and assign it to the Numeric value of the output row '
                Выход0Buffer.knownForTitle = Decimal.Parse(currentNumericValue)
            Next

        End If
    End Sub

End Class

Thank you! The problem was that there was NULL in one of the columns, as you suggested. — Olga, Sep 10 '19 at 18:34

Split multi value column into multiple records

1 Answers1