SSIS Basic OLEDB / ODBC destination script

• Download script - 0.6 KB

Introduction

This is a basic script for an SSIS Data Flow Script component that can be modified to execute SQL commands using an OLEDB or ODBC driver. I wanted to create this because my OLEDB command component did not let me execute an Update clause with a sub selection.

Background

I searched for some time for a simple VBScript example to just execute a query to database in SSIS. I couldn't find one, so I had to modify the MSDN example to fit my purposes.

Using the Code

What you have to do is create your SQL command using the Derived Column component or a variable. Then, add it as the input column (here, named as 'SQL_UPDATE') to the script component, and also add your connection through the Connections Manager Tab (here, named as MyConnection).

Now, the script should be ready to use.

Here is the most basic script:

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        '
        Dim Dest As DBDestination = New DBDestination()
        Dest.Save(Row.SQLUPDATE, Me.Connections.MyConnection.ConnectionString)

    End Sub
End Class

Public Class DBDestination

    Public Sub Save(ByVal command As String, ByVal connection As String)
        'Using cn As System.Data.Odbc.OdbcConnection = _
        '    New System.Data.Odbc.OdbcConnection(_connection)
        Using cn As System.Data.OleDb.OleDbConnection = _
                 New System.Data.OleDb.OleDbConnection(connection)
            cn.Open()
            'Dim cmd As System.Data.Odbc.OdbcCommand = cn.CreateCommand()
            Dim cmd As System.Data.OleDb.OleDbCommand = cn.CreateCommand()

            cmd.CommandType = CommandType.Text
            cmd.CommandText = command
            cmd.ExecuteNonQuery()
        End Using
    End Sub

End Class

Points of Interest

Nothing really special here. Just a basic solution to an annoying problem. But because SSIS is so easy to use, I did not want to do a lot of coding to maintain the easy maintainability that SSIS offers. Use and modify the code as you please.