Split strings in ssis based on field value

Question

In SSIS - how can I split the data from a row into 2 rows for example:

FROM:

ID  Data
1   On/Off
2   On/Off

TO:

ID  Data
1   On
1   Off
2   On
2   Off

Answer 1

Solution Overview

For this you need to use the script component. Use a non-synchronous output buffer to generate multiple lines from a line based on your own logic.

Solution Details

• Add DataFlow Task
• In DataFlow Taskadd Flat File Source, Script Componentand destination
• The component of the script, select the columns ID, Dataas input
• Go to the page Input and Outputs, click "Exit" and change the property Synchronous Inputtonone
• Add two output columns IDand DatainOutput
• script Visual Basic

• script

  Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
  
      Dim strValues() as String = Row.Data.Split(CChar("/")
  
      For each str as String in strValues
  
          Output0Buffer.AddRow()
          Output0Buffer.ID = Row.ID
          Output0Buffer.Data = str
  
      Next
  
  
  End Sub
  

:

• SSIS - script ,
• script

---

T-SQL

, , n , SQL

• , ,