title: SSIS - Script Component, Split single row to multiple rows (Parent Child Variation)
body: <p>Thanks in advance for your help. I'm in need of help on writing SSIS script component to delimit single row to multiple rows. There were many helpful blog and post I looked at below:</p>
  
  <p><a href="http://beyondrelational.com/ask/public/questions/1324/ssis-script-component-split-single-row-to-multiple-rows-parent-child-variation.aspx" rel="noreferrer">http://beyondrelational.com/ask/public/questions/1324/ssis-script-component-split-single-row-to-multiple-rows-parent-child-variation.aspx</a></p>
  
  <p><a href="http://bi-polar23.blogspot.com/2008/06/splitting-delimited-column-in-ssis.html" rel="noreferrer">http://bi-polar23.blogspot.com/2008/06/splitting-delimited-column-in-ssis.html</a></p>
  
  <p>However, I need a little extra help on coding to complete the project. Basically here's what I want to do.</p>
  
  <p>Input data</p>
  
  <pre>
  
      ID               Item Name
      1                Apple01,02,Banana01,02,03
      2                Spoon1,2,Fork1,2,3,4
  
  </pre>
  
  <p>Output data</p>
  
  <pre>
  
      ParentID      ChildID          Item Name
      1             1                Apple01
      1             2                Apple02
      1             3                Banana01
      1             4                Banana02
      1             5                Banana03
      2             1                Spoon1
      2             2                Spoon2
      2             3                Fork1
      2             4                Fork2
      2             5                Fork3
      2             6                Fork4
  </pre>
  
  <p>Below is my attempt to code, but feel free to revise whole if it's illogic. SSIS Asynchronous output is set.</p>
  
  <pre><code>Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
      Dim posID As Integer, childID As Integer
      Dim delimiter As String = ","
      Dim txtHolder As String, suffixHolder As String
      Dim itemName As String = Row.ItemName
      Dim keyField As Integer = Row.ID
  
      If Not (String.IsNullOrEmpty(itemList)) Then
  
          Dim inputListArray() As String = _
          itemList.Split(New String() {delimiter}, _
          StringSplitOptions.RemoveEmptyEntries)
  
          For Each item As String In inputListArray
              Output0Buffer.AddRow()
              Output0Buffer.ParentID = keyField
  
              If item.Length &gt;= 3 Then
                  txtHolder = Trim(item)
                  Output0Buffer.ItemName = txtHolder
  
                  'when item length is less than 3, it's suffix
              Else
                  suffixHolder = Trim(item)
                  txtHolder = Left(txtHolder.ToString(), Len(txtHolder) _
                      - Len(suffixHolder)) &amp; suffixHolder.ToString()
                  Output0Buffer.ItemName = txtHolder
              End If
          Next
      End If
  End Sub
  </code></pre>
  
  <h1>The current code produces the following output</h1>
  
  <pre>
  ID           Item Name
  1            Apple01
  1            02
  1            Banana01
  1            02
  1            03
  2            Spoon1
  2            2
  2            Fork1
  2            2
  2            3
  2            4
  
  </pre>