SQL Server Performance

SSIS Script Executes w/out Destination Updates

Discussion in 'SQL Server 2005 Integration Services' started by dcsoke, Apr 25, 2007.

  1. dcsoke New Member

    I am using the script below to update a DB2 table and it successfully executes. However, the database doesn't update. Can anyone help me? When I use a hardcoded update sql command then the destination record does update just that one record, but the parameters don't work. Thank You,



    Public Class ScriptMain
    Inherits UserComponent

    Dim connMgr As IDTSConnectionManager90
    Dim conn As OdbcConnection
    Dim cmdSelect As OdbcCommand
    Dim cmdUpdate As OdbcCommand
    Dim cmdInsert As OdbcCommand
    Dim paramInsert As OdbcParameter
    Dim paramSelect As OdbcParameter
    Dim paramUpdate As OdbcParameter
    Dim ds As DataSet

    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
    connMgr = Me.Connections.Connection
    conn = CType(connMgr.AcquireConnection(Nothing), OdbcConnection)
    End Sub

    Public Overrides Sub PreExecute()
    cmdUpdate = New OdbcCommand("UPDATE WEBLIB.A2JBMSKHF SET A2DESC = @FullName WHERE Rtrim(A2JOBN) = @FileNameNum", conn)
    paramUpdate = New OdbcParameter("@FullName", OdbcType.VarChar)
    cmdUpdate.Parameters.Add(paramUpdate)
    paramUpdate = New OdbcParameter("@FileNameNum", OdbcType.VarChar)
    cmdUpdate.Parameters.Add(paramUpdate)
    End Sub

    Public Overrides Sub BorrowerRecords_ProcessInputRow(ByVal Row As BorrowerRecordsBuffer)
    Try
    With cmdUpdate
    .Parameters("@FileNameNum").Value = Row.FileNameNum.Trim.ToString()
    .Parameters("@FullName").Value = Row.FullName.Trim.ToString()
    .ExecuteNonQuery()
    End With
    Catch ex As Exception
    ex.ToString()
    End Try
    End Sub

    Public Overrides Sub ReleaseConnections()
    connMgr.ReleaseConnection(conn)
    End Sub
    End Class
  2. satya Moderator

    Have you checked the reference at DB2 end?
    If you are estabilishing the successful connection to DB2 from SQL then the result must be investigated from that side too.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page