Binaryworld - Accessing OLEDB Connection Managers in a SSIS Script Component ... [ SQL Server -> Integration Services ]

Sometimes you may need to access OLEDB Connection Manager to execute Queries in Script. Here is the easy way to do that.

Credit : http://blogs.msdn.com/mattm/archive/2008/08/22/accessing-oledb-connection-managers-in-a-script.aspx

Click here to copy the following block

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

'//add a reference to the Microsoft.SqlServer.DTSRuntimeWrap ' Project -> Add Reference
'//Imports Microsoft.SqlServer.DTSRuntimeWrap

Public Class ScriptMain

  Public Sub Main()
    '
    ' Add your code here
    ExecuteSql("OLEDB_NORTHWIND_1", "Update Products SET ProductName=upper(ProductName)")

    Dts.TaskResult = Dts.Results.Success
  End Sub
  Private Function ExecuteSql(ByVal sConnMgr As String, ByVal sSql As String)
    Dim cm As ConnectionManager
    Dim cmParam As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters90
    Dim conn As OleDb.OleDbConnection
    Dim ocmd As IDbCommand
    cm = Dts.Connections(sConnMgr)
    cmParam = CType(cm.InnerObject, Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters90)
    conn = CType(cmParam.GetConnectionForSchema(), OleDb.OleDbConnection)
    ocmd = conn.CreateCommand()
    ocmd.CommandText = sSql
    ocmd.CommandType = CommandType.Text
    ocmd.ExecuteScalar()
  End Function

End Class