SSIS Script Not Working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SSIS Script Not Working

Author

Topic

dcsoke
Starting Member

2 Posts

Posted - 2007-04-23 : 23:45:33

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?

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

dcsoke
Starting Member

2 Posts

Posted - 2007-04-24 : 14:30:04

If I hardcode the cmdUpdate then it works fine: cmdUpdate = New OdbcCommand("UPDATE WEBLIB.A2JBMSKHF SET A2DESC = 'Tom' WHERE Rtrim(A2JOBN) = 300100", conn)

When I try to pass in variables nothing happens, can anyone tell me what I'm doin wrong?
cmdUpdate = New OdbcCommand("UPDATE WEBLIB.A2JBMSKHF SET A2DESC = @FullName WHERE Rtrim(A2JOBN) = @FileNameNum", conn)

Thanks,
Dan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources