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.OleDb.OleDbConnection = _
New System.Data.OleDb.OleDbConnection(connection)
cn.Open()
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.
