asked on

SSIS : call oracle procedure with Error Code and Error Description as parameters

Hi,

I created a dtsx package with on Data Flow Task. I defined a variable vValue in this package.

On the events onError and onTaskFailed, I want to call an oracle procedure with as parameters the system::ErrorCode, the system::ErrorDescription and the variable vValue.

I tried with a 'Execute SQL Task' and with a 'Script Task' but I didn't succeed.

1. First solution with Script Task:

Public Sub Main()
'
' Add your code here
'
Dim oracleConn As OracleClient.OracleConnection
Dim oracleCmd As OracleClient.OracleCommand
Dim sqlCmd As String
Dim errNumber As Integer
Dim errDescription As String
Dim errSource As String
GetErrorValues(errNumber, errDescription, errSource)
oracleConn = CType(Dts.Connections("Conn1").AcquireConnection(Nothing), OracleConnection)
oracleConn.Open()
oracleCmd = New OracleClient.OracleCommand()
oracleCmd.Connection = oracleConn
oracleCmd.CommandText = "Procedure1"
oracleCmd.CommandType = CommandType.StoredProcedure
oracleCmd.Parameters.AddWithValue("asPar1", OracleClient.OracleType.VarChar).Value = errSource
oracleCmd.Parameters.AddWithValue("asPar2", OracleClient.OracleType.VarChar).Value = Dts.Variables("User::vValue").Value
oracleCmd.Parameters.AddWithValue("alPar3", OracleClient.OracleType.Number).Value = errNumber
oracleCmd.Parameters.AddWithValue("asPar4", OracleClient.OracleType.VarChar).Value = errDescription
oracleCmd.ExecuteNonQuery()

Dts.TaskResult = Dts.Results.Failure

End Sub

Private Sub GetErrorValues(ByRef errNumber As Integer, ByRef errDescription As String, ByRef errSource As String)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead("ErrorCode")
Dts.VariableDispenser.LockForRead("ErrorDescription")
Dts.VariableDispenser.LockForRead("SourceName")
Dts.VariableDispenser.GetVariables(vars)

Try

errNumber = CType(vars("ErrorCode").Value, Integer)
errDescription = vars("ErrorDescription").Value.ToString()
errSource = vars("SourceName").Value.ToString()
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As SystemException
Throw ex
End Try
End Sub

2. Second solution:
I don't find the syntax on the SQL statement...

The two solutions should work?

Thanks for your help

rajvja

try just displaying the errornumber, variable value in a script task. Check whether the values are captured or not.
Then try to call the SPROC by passing parameters(via these variables) using Execute SQL task

AngieWill

ASKER

I already tried MsgBox(errNumber) but doesn't appear.
When I try MsgBox("toto"),the Message Box appears.

AngieWill

ASKER

What must I define in Script Task Editor? PrecompileScriptIntoBinaryCode, ReadOnlyVariables...?

ASKER CERTIFIED SOLUTION

Reza Rad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PedroCGD

"I tried with a 'Execute SQL Task' and with a 'Script Task' but I didn't succeed."
Which was the error? In SQL task you should change the connectiontype to ADO.NET
Regards,
Pedro

rajvja

When you try with Exec SQL task, have you got any error? WHat do you mean by didn't succeed?

AngieWill

ASKER

PedroCGD,

what is the syntax? execute or call?

AngieWill

ASKER

With 'Script Task', I have no error but ErrorCode is empty.

PedroCGD

Add a SQL task and open/edit
In the connection type property, set ADO.NET
In the Connection property set the ADO connection.. if not exist, create one to oracle
In the SQL Statment add the SQL statment SELECT... FROM...

helped?

AngieWill

ASKER

PedroCGD,

I want to call an oracle procedure with as parameters the System::ErrorCode and the System::ErrorDescription.

AngieWill

ASKER

reza_rad,

I become this error:
Failed to lock varaiable "ErrorCode" for read access with error 0xC0010001 "The variable cannot found. This occurs when an attempt is made to retrive a variable from the variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created."

I will try to include the Data Flow Task in one container.

PedroCGD

For this error:
Failed to lock varaiable "ErrorCode" for read access with error 0xC0010001 "The variable cannot found
You need to add the name of variable you are using in the ReadOnlyVariables property or readWriteVariables, depending if you wil read or write on it.

helped?

Reza Rad

so this error means that you must add your variable to ReadOnlyVariables or ReadWriteVariables

AngieWill

ASKER

I added ErrorCode in the ReadOnlyVariables but didn't work.

SOLUTION

PedroCGD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AngieWill

ASKER

I add in WriteReadVariables : System::ErrorCode,System::ErrorDescription,System::SourceName

In script :
Dts.VariableDispenser.GetVariables(vars)
vars.Count return 0????

PedroCGD

Sorry... you are using system variables... the logic it's different.
You want to get the error from the connection that you are trying to make, correct? the error message is stored inside EX exception...

see the attached code and image
Helped?

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

Public Class ScriptMain
    Public Sub Main()
        Try
            '
            ' Add your code here
            '

            Dts.TaskResult = Dts.Results.Success
        Catch ex As Exception
            'use EX exception
            MsgBox(ex.InnerException)
            MsgBox(ex.Message)

            'Use SSIS log error events
            Dts.TaskResult = Dts.Results.Failure
            Dts.Events.FireError(0, "myError", "MyDesc", "", 0)
        End Try
    End Sub

End Class

Open in new window

ScriptTask.JPG

AngieWill

ASKER

Thanks

But I don't understand.

I want only in my package to catch all errors and save these errors in the database. For this, I want to call an oracle procedure.

I try your script for the OnError event of the DataFlowTask.
In another script task, I do manually an error but this event is not called.

I think I try to find another solution.

AngieWill

ASKER

Sorry the OnError script is called but without exception

PedroCGD

Sorry, but you didnt clarify your issues.
You want to save in Oracle each time occur an error?! Did you tried SSIS Logging?!

If you'll do it manually I sugeest using SSIS EventHandlers

AngieWill

ASKER

Sorry I am new in SSIS.

I want to save in oracle each time an error occurs but the SSIS package should return 'Failure'.
I hope you understand what I want.

Thanks.

SOLUTION

PedroCGD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AngieWill

ASKER

Finally, I catch all errors in the application which calls the dtsx package.

Thanks for your help.