none
connectiong to SQLServer in script component (SSIS) RRS feed

  • Question

  • Hi

    I'm very new to SSIS but can't find the answer to this anywhere.

    I'm simply trying to connect to a SQLServer database from within a script component.

    I am doing this using a [Native OLE DB\SQL Native Client] connection manager which i've added to the script component and called it SQLconnection.

    this is the code:

    Dim SQLconnMgr As IDTSConnectionManager90 = Me.Connections.SQLconnection

    Dim SQLConn As sqlClient.SQLConnection = CType(SQLconnMgr .AcquireConnection(Nothing), sqlClient.sqlConnection)

    The first line is fine...but I get this error in the second line:

    [ Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.sqlClient.sqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface. ]

    Can anyone help? I'm totally stuck

    thanks!

    andy
    Sunday, July 22, 2007 12:06 PM

Answers

  • Dear Friend,

    Look at this example... and tell me if you get it...

    If not post here the problem and I will try to help you!

     

    Code Snippet

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


    Public Class ScriptMain
        Public countErrorUsers As Integer

        Public Sub Main()

     

           Dim oCnn As New Data.SqlClient.SqlConnection
            oCnn.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=True;"
            Dim oCmd As SqlCommand = New SqlCommand
            oCmd.CommandType = Data.CommandType.StoredProcedure
            oCmd.CommandText = "STOREDPROCEDURENAME"
            oCmd.Connection = oCnn


            oCnn.Open()
            Dim oReader As SqlDataReader = oCmd.ExecuteReader()
            Dim strLogin As String

            While oReader.Read
                strLogin = oReader.Item("FIELDNAME").ToString
                If userValid(strLogin) = True Then
                   ...

               End If
            End While

           oReader.Close()
            oCnn.Close()

           Dts.TaskResult = Dts.Results.Success

        End Sub

     

    Regards!

     

    Sunday, July 22, 2007 3:12 PM
  • Use the connection manager for connection acquisition; this buys you container transaction enlistment, configuration, validation, and retained connections / connection pool use.

     

    The following script tranform scaffold will "get" the above freatures just by calling AcquireConnection and ReleaseConnection on the connection manager, in this case provided an ADO.NET/SqlClient connection manager is configured in the script transform with a name of "DataMart". 

     

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
    Imports System.Data.SqlClient
    
    Public Class ScriptMain
        Inherits UserComponent
    
        Private sqlConn As SqlConnection
        Private sqlCmd As SqlCommand = New SqlCommand("select @@trancount")
    
        Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
            sqlConn = _
            DirectCast(Connections.DataMart.AcquireConnection(Transaction), SqlConnection)
        End Sub
    
        Public Overrides Sub PreExecute()
            With sqlCmd
                .Connection = sqlConn
                .CommandTimeout = 30
                .CommandType = CommandType.Text
            End With
        End Sub
    
        Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
            Dim tranCount As Int32 = DirectCast(sqlCmd.ExecuteScalar(), Int32)
            Log("TranCount : " + tranCount.ToString(), 0, Nothing)
        End Sub
    
        Public Overrides Sub ReleaseConnections()
            If sqlConn IsNot Nothing Then
                Connections.DataMart.ReleaseConnection(sqlConn)
            End If
        End Sub
    
    End Class
    Sunday, July 22, 2007 4:57 PM

All replies

  • Dear Friend,

    Look at this example... and tell me if you get it...

    If not post here the problem and I will try to help you!

     

    Code Snippet

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


    Public Class ScriptMain
        Public countErrorUsers As Integer

        Public Sub Main()

     

           Dim oCnn As New Data.SqlClient.SqlConnection
            oCnn.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=True;"
            Dim oCmd As SqlCommand = New SqlCommand
            oCmd.CommandType = Data.CommandType.StoredProcedure
            oCmd.CommandText = "STOREDPROCEDURENAME"
            oCmd.Connection = oCnn


            oCnn.Open()
            Dim oReader As SqlDataReader = oCmd.ExecuteReader()
            Dim strLogin As String

            While oReader.Read
                strLogin = oReader.Item("FIELDNAME").ToString
                If userValid(strLogin) = True Then
                   ...

               End If
            End While

           oReader.Close()
            oCnn.Close()

           Dts.TaskResult = Dts.Results.Success

        End Sub

     

    Regards!

     

    Sunday, July 22, 2007 3:12 PM
  • hi, thanks pedro!

    haven't tried it yet...but, I was wondering. I though in SSIS you had to use the connection managers that are set up outside of the code...does that make sense?

    thanks, I'll let you know if it works

    andy
    Sunday, July 22, 2007 3:58 PM
  • You always must try to use the data Sources/Destinations of SSIS, only when I must do something more complex you use the script component.

    You can use the script component in the ControlFlow, and in the DataFlow. In the dataFlow the script component could be the type of Source, Transform or Destination.

    If you get it, dont foreget to check your post as answered!!

    Regards!!

    Sunday, July 22, 2007 4:05 PM
  • Use the connection manager for connection acquisition; this buys you container transaction enlistment, configuration, validation, and retained connections / connection pool use.

     

    The following script tranform scaffold will "get" the above freatures just by calling AcquireConnection and ReleaseConnection on the connection manager, in this case provided an ADO.NET/SqlClient connection manager is configured in the script transform with a name of "DataMart". 

     

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
    Imports System.Data.SqlClient
    
    Public Class ScriptMain
        Inherits UserComponent
    
        Private sqlConn As SqlConnection
        Private sqlCmd As SqlCommand = New SqlCommand("select @@trancount")
    
        Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
            sqlConn = _
            DirectCast(Connections.DataMart.AcquireConnection(Transaction), SqlConnection)
        End Sub
    
        Public Overrides Sub PreExecute()
            With sqlCmd
                .Connection = sqlConn
                .CommandTimeout = 30
                .CommandType = CommandType.Text
            End With
        End Sub
    
        Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
            Dim tranCount As Int32 = DirectCast(sqlCmd.ExecuteScalar(), Int32)
            Log("TranCount : " + tranCount.ToString(), 0, Nothing)
        End Sub
    
        Public Overrides Sub ReleaseConnections()
            If sqlConn IsNot Nothing Then
                Connections.DataMart.ReleaseConnection(sqlConn)
            End If
        End Sub
    
    End Class
    Sunday, July 22, 2007 4:57 PM
  • hey, thanks for all your help guys. jaegd i didn't actually go with your method cos i don't really understand enought about the SSIS setup.

    Went with pedro's as I'm used to just writing vb.net and AS.NET stand alone apps so that was more familiar to me.

    Thanks guys!
    Monday, July 23, 2007 11:29 AM
  • Your call on which one to use, obviously, but jaegd's script definately takes more advantage of what SSIS provides for you, particularly around transactions. If you are doing anything in your script that you might want to roll back, I'd recommend that you revisit this issue.

     

    By the way, the issue in the original post was because you are using an unmanaged connection object (OLEDB). If you switched the connection manager over to an ADO.NET connection, you wouldn't have the error.

    • Proposed as answer by Rajkumar5055 Tuesday, September 4, 2012 9:03 AM
    • Unproposed as answer by Rajkumar5055 Tuesday, September 4, 2012 9:03 AM
    Tuesday, July 24, 2007 12:15 AM
    Moderator
  • Hi,

     

    I am new in SSIS. The below statement from PedroCGD, would someone please show me how to change "SERVERNAME" dynamically against different server at run time? I kind of understand to replace the static string "SERVERNAME" concantenate with a variable, but have no clue where to start.

     

    oCnn.ConnectionString = "Data Source=SERVERNAME ;Initial Catalog=DATABASENAME ;Integrated Security=True;"

     

    Much appreciate for your help.

     

    Regards,

    Monday, April 12, 2010 10:44 AM
  • Thanks Pedro! This is very useful..!!!
    Thursday, January 23, 2014 2:32 PM