Split rows in ssis based on a field value

Question

In SSIS - How can I split data from 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

You have to use a script component to achieve this. Use an unsynchronous output buffer to generate multiple rows from on row based on your own logic.

Solution Details

1. Add a DataFlow Task
2. In the DataFlow Task add a Flat File Source, Script Component, and a Destination
3. In the Script Component, select ID, Data columns as Input
4. Go to the Input and Outputs page, click on the Output and change the Synchronous Input property to none
5. Add two Output Columns ID and Data into the Output
6. Change the Script language to Visual Basic

7. Inside the Script editor write the following code

   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
   

Additional Information

For more details follow these links:

• SSIS - Script Component, Split single row to multiple rows
• Output multiple rows from script component per single row input

---

Using T-SQL

Based on your comments, this is a link that contains a n example of how this can be done using a SQL command

• Turning a Comma Separated string into individual rows