Thursday, 1 November 2012

Split multi value column into multiple records

Case
I have a column with multiple values and I want to split them into multiple records.












Solution
You could solve it with a fancy TSQL query. I saw split functions with common table expressions, but a relatively easy script could to the trick in SSIS as well. (don't hesitate to post your query/solution in the comments)

1) Source
Add your source to the Data Flow Task












2) Script Component - input columns
This solution uses an asynchronous Script Component, so add a Script Component (type transformation) to your Data Flow Task. Edit it, go to the Input Columns pane and select all the columns you need downstream the data flow as readonly. In this case we need the columns Teacher and Students.
Input columns (readonly)




















3) Script Component - output port
Go to the Inputs and Outputs pane and click on Output 0. Set the SynchronousInputID property to none to make this Script Component asynchronous.
asynchronous




















4) Script Component - output columns
Add output columns for each input column that you need downstream the data flow. In this case we need Teacher (same datatype and size) and a new column named Student which will contain one value from the input column Students (same datatype, but size could probably be smaller).
Output columns



















5) The script
Copy the Inputs0 _ProcessInputRow method to your script and remove all the other methods (PreExecute, PostExecute and CreateNewOutputRows) because we don't need them.
// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    // Method that will execute for each row passing
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // First we are converting the comma seperated list into a string array.
        // You can change the comma if you are using an other seperator like | or ;
        string[] Students = Row.Students.ToString().Split(new char[] { ',' }, StringSplitOptions.None);

        // Counter var used the loop through the string array
        int i = 0;

        // Looping through string array with student names
        while (i < Students.Length)
        {
            // Start a new row in the output
            Output0Buffer.AddRow();

            // Pass through all columns that you need downstream the data flow
            Output0Buffer.Teacher = Row.Teacher;

            // This is the splitted column. Take the [n] element from the array
            // and put it in the new column.
            Output0Buffer.Student = Students[i];

            // Increase counter to go the next value
            i++;
        }
    }
}

or VB.Net

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

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    ' Method that will execute for each row passing
    Public Overrides Sub Input0_ProcessInputRow(Row As Input0Buffer)
        ' First we are converting the comma seperated list into a string array.
        ' You can change the comma if you are using an other seperator like | or ;
        Dim Students As String() = Row.Students.ToString().Split(New Char() {","c}, StringSplitOptions.None)

        ' Counter var used the loop through the string array
        Dim i As Integer = 0

        ' Looping through string array with student names
        While i < Students.Length
            ' Start a new row in the output
            Output0Buffer.AddRow()

            ' Pass through all columns that you need downstream the data flow
            Output0Buffer.Teacher = Row.Teacher

            ' This is the splitted column. Take the [n] element from the array
            ' and put it in the new column.
            Output0Buffer.Student = Students(i)

            ' Increase counter to go the next value
            i += 1
        End While
    End Sub
End Class


6) The result
For testing purposes I added a derived column and a couple of data viewer
The result



























Note: if you want to do this backwards, see this post.


4 comments:

  1. Thanks man, it helped me a lot. really a simple and helpful way to solve the problem.
    ~omair

    ReplyDelete
  2. +1 very helpful, thanks a lot!

    ReplyDelete
  3. When we need to make store of Multiple values in one cell of a table ????

    Please give me real example of that .

    Regards ,

    Mahmoud

    ReplyDelete
    Replies
    1. It's not a best practice to use/create multi-value columns. But sometimes you just get them and you have to deal with it.

      Delete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...