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.
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
Now, the script should be ready to use.
Here is the most basic script:
Public Class ScriptMain
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim Dest As DBDestination = New DBDestination()
Public Class DBDestination
Public Sub Save(ByVal command As String, ByVal connection As String)
Using cn As System.Data.OleDb.OleDbConnection = _
Dim cmd As System.Data.OleDb.OleDbCommand = cn.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = command
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.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.