SSIS OLEDB or ODBC

Case
A while ago I read this MSDN Blog post that stated that (short version) OLE DB is outdated and we should all use ODBC again. SQL 2012 will be the last release to support OLE DB. Should we all start using ODBC in SSIS?



To OLE DB or not to OLE DB?

Solution
Today, at the Dutch SQL saturdays, I had the change to ask that question to some of the brightest SQL people from The Netherlands (a Microsoft Certified Master and a couple of Microsoft Premier Field Engineers). Their answer: ODBC will become the standard in the future versions, but at the moment (SQL 2012) OLE DB is still much faster! So use OLE DB where possible.

Besides that... a lot of components (like the lookup) only support OLEDB. See complete list here.

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.