Kode für das ODBC Datenziel Script Komponente

 

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.Odbc

Public Class ScriptMain
    Inherits UserComponent

    Private mCon As OdbcConnection
    Private mCmd As OdbcCommand
    Private mZähler As Integer
    Private mStartzeit As Date

    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
        Dim ConnectionString As String

        mStartzeit = Now()
        Try
            ConnectionString = Me.Connections.Verbindung.ConnectionString
            mCon = New OdbcConnection(ConnectionString)
            mCon.Open()
            mCon.ChangeDatabase("TempDB")
        Catch ex As Exception
            Me.ComponentMetaData.FireError(255, "AcquireConnections", ex.ToString, "", 0, False)
        End Try
    End Sub

    Public Overrides Sub PreExecute()

        MyBase.PreExecute()

        Try
            mCmd = New OdbcCommand("INSERT INTO dbo.Contact (FirstName, MiddleName, LastName) VALUES(?, ?, ?)", mCon)
            With mCmd
                .Parameters.Add("@FirstName", OdbcType.NVarChar, 50)
                .Parameters.Add("@MiddleName", OdbcType.NVarChar, 50)
                .Parameters.Add("@LastName", OdbcType.NVarChar, 50)
            End With
        Catch ex As Exception
            Me.ComponentMetaData.FireError(255, "PreExecute", ex.ToString, "", 0, False)
        End Try
    End Sub

    Public Overrides Sub Eingabe0_ProcessInputRow(ByVal Row As Eingabe0Buffer)
        Try
            mZähler += 1
            With mCmd
                If Not Row.FirstName_IsNull Then
                    .Parameters("@FirstName").Value = Row.FirstName
                Else
                    .Parameters("@FirstName").Value = DBNull.Value
                End If
                If Row.MiddleName_IsNull Then
                    .Parameters("@MiddleName").Value = DBNull.Value
                Else
                    .Parameters("@MiddleName").Value = Row.MiddleName
                End If
                If Not Row.LastName_IsNull Then
                    .Parameters("@LastName").Value = Row.LastName
                Else
                    .Parameters("@LastName").Value = DBNull.Value
                End If

                .ExecuteNonQuery()
            End With
        Catch ex As Exception
            Me.ComponentMetaData.FireError(255, "Eingabe0_ProcessInputRow", ex.ToString, "", 0, False)
        End Try
    End Sub

    Public Overrides Sub ReleaseConnections()
        Try
            mCon.Close()
        Catch ex As Exception
            Me.ComponentMetaData.FireError(255, "ReleaseConnections", ex.ToString, "", 0, False)
        End Try
    End Sub

    Public Overrides Sub PostExecute()
        Dim Zeitdifferenz As TimeSpan

        MyBase.PostExecute()

        Zeitdifferenz = Now.Subtract(mStartzeit)

        Me.ComponentMetaData.FireInformation(0, Me.ComponentMetaData.Name, "Anzahl Datensätze: " & _
                    mZähler.ToString & " - Inserts pro Sekunde: " & mZähler / Zeitdifferenz.TotalSeconds, "", 0, False)
    End Sub
End Class
 

Das Beispielpaket finden Sie hier