CODEWITHSUNDEEP
sql-serverssisetl
Ben
Ben

Reputation: 81

Split rows in ssis based on a field value

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

Upvotes: 4

Views: 1397

Answers (1)

Hadi
Hadi

Reputation: 37368

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:

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

Upvotes: 1

Related Questions