Database operation with SSIS Script component

Step 1 : Create DataFlowTask

Step 2: Place OLEDB  DataSource component

Step 3: Place a Script component and connect the Input.

Step 4: Right Click the Script Component, go to Connection Managers Tab.

Step 5: Click Add and Select your database connection from the dropdownlist .

Step 6 : Go to Script tab and click the Design Script option. It will open the Visual Studio for Applications.

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 connMgr As IDTSConnectionManager90

    Private Conn As SqlConnection

    Private Cmd As SqlCommand

    Private sqlReader As SqlDataReader

    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

        connMgr = Me.Connections.MyDatabaseConnection        'This is the connection to your connection manager.

        Conn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

    End Sub

    Public Overrides Sub PreExecute()

        Dim cmd As New SqlCommand("select getdate()", Conn)

        sqlReader = cmd.ExecuteReader

    End Sub

    Public Overrides Sub PostExecute()

        sqlReader.Close()

    End Sub

    Public Overrides Sub ReleaseConnections()

        connMgr.ReleaseConnection(Conn)

    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        If sqlReader.HasRows Then

            System.Windows.Forms.MessageBox.Show(Convert.ToString(sqlReader(0)))

        End If

    End Sub

End Class

 

AcquireConnections :

                This method is used to connect the data source

PreExecute

                Override PreExecute method to execute the database query

ProcessInputRow

                In this method, process the output of the SQLCommand. In our case, we need to process the SQLDataReader.

PostExecute

                This method is used to do cleanup database connection, if anything.

ReleaseConnection

                This method is used to release the database connection.