VB 2010 script task code

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

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

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    '
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.
    Dim db2Con As OleDbConnection = Nothing
    Dim strError As String = Nothing
    Dim db2Cmd As OleDbCommand = Nothing
    Dim db2Rdr As OleDbDataReader = Nothing
    Dim EODC_Audit_DataTable As New DataTable

    Public Sub Main()
        '
        ' Add your code here
        '



        Dts.Variables("Audit_Row_Ct").Value = CheckForEODC()
        Dts.TaskResult = ScriptResults.Success


    End Sub

    Public Function CheckForEODC() As Integer
        CheckForEODC = 0
        Try
            If Not DB2RS.SetDBCon(db2Con, strError) Then Throw New Exception(strError)
            If Not DB2RS.SetDBCmd(db2Con, db2Cmd, "SELECT * FROM " + Dts.Variables("Region").Value.ToString + ".****", strError) Then Throw New Exception(strError)
            db2Rdr = db2Cmd.ExecuteReader
            EODC_Audit_DataTable.Load(db2Rdr)
        Catch ex As Exception
            Dts.VariableDispenser.LockForWrite("stdError")
            Dts.Variables("strError").Value = "Load Error:  " & ex.Message & "<br />&nbsp;"
            Dts.Variables.Unlock()
        Finally
            DB2RS.DisposeOfObjects(db2Con, db2Cmd, db2Rdr)
        End Try

        If (**** < 1) Then
            CheckForEODC = 0
        Else
            CheckForEODC = ****
        End If
    End Function
End Class


Public Class DB2RS


    Public Shared Function SetDBCon( _
       ByRef dbCon As OleDbConnection, _
       ByRef strError As String _
   ) As Boolean

        Try
            dbCon = New OleDbConnection(Dts.Variables("DB2ConnectionString").Value.ToString)
            dbCon.Open()
            SetDBCon = True
        Catch ex As Exception
            strError = ex.Message
            SetDBCon = False
        End Try
    End Function

    Public Shared Function SetDBCmd( _
        ByRef dbCon As OleDbConnection, _
        ByRef dbCmd As OleDbCommand, _
        ByVal strCmd As String, _
        ByRef strError As String _
    ) As Boolean

        Try
            dbCmd = New OleDbCommand(strCmd, dbCon)
            dbCmd.CommandType = Data.CommandType.Text
            SetDBCmd = True

        Catch ex As Exception
            strError = ex.Message
            SetDBCmd = False

        End Try
    End Function



    Public Shared Sub DisposeOfObjects( _
        Optional ByRef dbCon As OleDbConnection = Nothing, _
        Optional ByRef dbCmd As OleDbCommand = Nothing, _
        Optional ByRef dbRdr As OleDbDataReader = Nothing, _
        Optional ByRef dbAda As OleDbDataAdapter = Nothing, _
        Optional ByRef dbDSet As DataSet = Nothing, _
        Optional ByRef dTable As DataTable = Nothing, _
        Optional ByRef dView As DataView = Nothing _
    )
        Try
            If Not IsNothing(dbDSet) Then
                dbDSet.Dispose()
                dbDSet = Nothing
            End If
        Catch ex As Exception
            dbDSet = Nothing
        End Try

        Try
            If Not IsNothing(dbAda) Then
                dbAda.Dispose()
                dbAda = Nothing
            End If
        Catch ex As Exception
            dbAda = Nothing
        End Try

        Try
            If Not IsNothing(dbRdr) Then
                If Not dbRdr.IsClosed Then dbRdr.Close()
                dbRdr = Nothing
            End If
        Catch ex As Exception
            dbRdr = Nothing
        End Try
        Try
            If Not IsNothing(dbCmd) Then
                dbCmd.Dispose()
            End If
        Catch ex As Exception
            dbCmd = Nothing
        End Try

        Try
            If Not IsNothing(dbCon) Then
                If dbCon.State = ConnectionState.Open Then
                    dbCon.Close()
                End If
                dbCon.Dispose()
            End If
        Catch ex As Exception
            dbCon = Nothing
        End Try

        Try
            If Not IsNothing(dTable) Then
                dTable.Dispose()
                dTable = Nothing
            End If
        Catch ex As Exception
            dTable = Nothing
        End Try

        Try
            If Not IsNothing(dView) Then
                dView.Dispose()
                dView = Nothing
            End If
        Catch ex As Exception
            dView = Nothing
        End Try
    End Sub

    Public Shared Function Null2Space( _
        ByVal strValue As Object, _
        Optional ByVal blnDash As Boolean = False _
    ) As String

        Try
            If IsNothing(strValue) Then
                If blnDash Then
                    Null2Space = "&nbsp;-&nbsp;"
                Else
                    Null2Space = "&nbsp;"
                End If
            ElseIf strValue Is System.DBNull.Value Then
                If blnDash Then
                    Null2Space = "&nbsp;-&nbsp;"
                Else
                    Null2Space = "&nbsp;"
                End If
            ElseIf strValue.ToString.Trim.Length() < 1 Then
                If blnDash Then
                    Null2Space = "&nbsp;-&nbsp;"
                Else
                    Null2Space = "&nbsp;"
                End If
            Else
                Null2Space = strValue.ToString.Trim()
            End If

        Catch ex As Exception
            Null2Space = strValue.ToString

        End Try
    End Function



End Class











Public Class DBSQLRS

    Public Shared Function SetDBCon( _
        ByRef dbCon As SqlConnection, _
        ByRef strError As String, _
        Optional ByVal dbName As String = "*****", _
        Optional ByVal dbUser As String = "*****" _
    ) As Boolean

        Try
            If dbUser = "ELI_Web" Then
                dbCon = New SqlConnection("*****")
            ElseIf dbUser = "ELI_URP" Then
                dbCon = New SqlConnection("*****")
            End If

            dbCon.Open()
            SetDBCon = True

        Catch ex As Exception
            strError = ex.Message
            SetDBCon = False

        End Try
    End Function

    Public Shared Function SetDBCmd( _
        ByRef dbCon As SqlConnection, _
        ByRef dbCmd As SqlCommand, _
        ByVal strCmd As String, _
        ByRef strError As String _
    ) As Boolean

        Try
            dbCmd = New SqlCommand(strCmd, dbCon)
            dbCmd.CommandType = Data.CommandType.StoredProcedure
            SetDBCmd = True

        Catch ex As Exception
            strError = ex.Message
            SetDBCmd = False

        End Try
    End Function

    Public Shared Function SetDBPar( _
        ByRef dbCmd As SqlCommand, _
        ByRef dbPar As SqlParameter, _
        ByVal strParName As String, _
        ByVal objParValue As Object, _
        ByVal dbType As Data.SqlDbType, _
        ByRef strError As String _
    ) As Boolean

        Try
            dbPar = New SqlParameter(strParName, dbType)
            dbPar.IsNullable = True
            dbPar.Value = objParValue
            dbCmd.Parameters.Add(dbPar)
            SetDBPar = True

        Catch ex As Exception
            strError = ex.Message
            SetDBPar = False

        End Try
    End Function

    Public Shared Sub DisposeOfObjects( _
        Optional ByRef dbCon As SqlConnection = Nothing, _
        Optional ByRef dbCmd As SqlCommand = Nothing, _
        Optional ByRef dbPar As SqlParameter = Nothing, _
        Optional ByRef dbRdr As SqlDataReader = Nothing, _
        Optional ByRef dbAda As SqlDataAdapter = Nothing, _
        Optional ByRef dbDSet As DataSet = Nothing, _
        Optional ByRef dTable As DataTable = Nothing, _
        Optional ByRef dView As DataView = Nothing _
    )
        Try
            If Not IsNothing(dbDSet) Then
                dbDSet.Dispose()
                dbDSet = Nothing
            End If
        Catch ex As Exception
            dbDSet = Nothing
        End Try

        Try
            If Not IsNothing(dbAda) Then
                dbAda.Dispose()
                dbAda = Nothing
            End If
        Catch ex As Exception
            dbAda = Nothing
        End Try

        Try
            If Not IsNothing(dbRdr) Then
                If Not dbRdr.IsClosed Then dbRdr.Close()
                dbRdr = Nothing
            End If
        Catch ex As Exception
            dbRdr = Nothing
        End Try

        Try
            If Not IsNothing(dbPar) Then
                dbPar = Nothing
            End If
        Catch ex As Exception
            dbPar = Nothing
        End Try

        Try
            If Not IsNothing(dbCmd) Then
                dbCmd.Dispose()
            End If
        Catch ex As Exception
            dbCmd = Nothing
        End Try

        Try
            If Not IsNothing(dbCon) Then
                If dbCon.State = ConnectionState.Open Then
                    dbCon.Close()
                End If
                dbCon.Dispose()
            End If
        Catch ex As Exception
            dbCon = Nothing
        End Try

        Try
            If Not IsNothing(dTable) Then
                dTable.Dispose()
                dTable = Nothing
            End If
        Catch ex As Exception
            dTable = Nothing
        End Try

        Try
            If Not IsNothing(dView) Then
                dView.Dispose()
                dView = Nothing
            End If
        Catch ex As Exception
            dView = Nothing
        End Try
    End Sub

    Public Shared Function Null2Space( _
        ByVal strValue As Object, _
        Optional ByVal blnDash As Boolean = False _
    ) As String

        Try
            If IsNothing(strValue) Then
                If blnDash Then
                    Null2Space = "&nbsp;-&nbsp;"
                Else
                    Null2Space = "&nbsp;"
                End If
            ElseIf strValue Is System.DBNull.Value Then
                If blnDash Then
                    Null2Space = "&nbsp;-&nbsp;"
                Else
                    Null2Space = "&nbsp;"
                End If
            ElseIf strValue.ToString.Trim.Length() < 1 Then
                If blnDash Then
                    Null2Space = "&nbsp;-&nbsp;"
                Else
                    Null2Space = "&nbsp;"
                End If
            Else
                Null2Space = strValue.ToString.Trim()
            End If

        Catch ex As Exception
            Null2Space = strValue.ToString

        End Try
    End Function

End Class