SQL Server Performance Forum – Threads Archive
SSIS Script Executes w/out Destination Updates
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 ScriptMainInherits 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
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.
]]>