"jobs" <jobs@webdos.com> wrote in message
news:1168374231.030057.156450@i56g2000hsf.googlegroups.com:

> I have some vb.net code in an SSIS script task that uses ado to call an
> oracle function that schedules a job and returns the job number . I've
> tested the code seperately under an asp.net website and it works fine.
> However under the ssis script task, an exception kicks out an exception
> at the line
>
> cmd.ExecuteNonQuery() ' **** ERROR RIGHT HERE ****
>
> How do I troubleshoot this so I can see what the error is under SSIS?
>
>
>
> Public Sub OraSP(ByVal processname As String, ByVal database As String,
> ByVal user As String, ByVal password As String)
> Dim cnn As OracleConnection
> Dim sConnString As String = "Data Source=" + database + ";user
> id=" + user + ";password=" + password + ";"
> cnn = New OracleConnection(sConnString)
> Dts.Events.FireInformation(0, "OraSP Connection=", sConnString,
> "", 0, False)
>
> Dim cmd As New OracleCommand
> With cmd
> .CommandType = CommandType.StoredProcedure
> .CommandText = "X_FN"
> .CommandTimeout = 0
> .Connection = cnn
> End With
>
> Dim procname, jobout As OracleParameter
> procname = cmd.Parameters.Add("iprocname", OracleType.VarChar)
> procname.Value = "WHAT"
> procname.Direction = ParameterDirection.Input
> jobout = cmd.Parameters.Add("job", OracleType.Number)
> jobout.Direction = ParameterDirection.ReturnValue
>
> Try
> cnn.Open()
> If cnn.State = ConnectionState.Open Then
> Dts.Events.FireInformation(0, "Ora connected", "", "",
> 0, False)
> End If
> Dim job As Integer
> cmd.ExecuteNonQuery() ' **** ERROR RIGHT HERE ****
> job = CInt(jobout.Value)
> cnn.Close()
> Catch ex As Exception
> Throw New Exception("Could not submit oracle procedure")
> Dts.Events.FireInformation(0, "ERROR=", "could not submit
> oracle procedure", "", 0, False)
> Finally
> cmd.Dispose()
> cnn.Close()
> End Try
> End Sub