Click here to Skip to main content
16,019,618 members
Articles / Database Development / SQL Server

SSIS Basic OLEDB / ODBC destination script

Rate me:
Please Sign up or sign in to vote.
3.00/5 (1 vote)
29 May 2008CPOL1 min read 51.6K   145   16   3
Simple destination script to use do updates with a sub query.

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:

VBScript
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Finland Finland
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralDyanamic Excel data dump to Database ,The Excel name changes for every time Pin
raj kumar Arakala26-Jan-09 22:18
raj kumar Arakala26-Jan-09 22:18 
QuestionSSIS Package Demo ? Pin
Paul Chu29-Aug-08 18:59
Paul Chu29-Aug-08 18:59 
AnswerRe: SSIS Package Demo ? Pin
madgardener4-Sep-08 21:55
madgardener4-Sep-08 21:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.