Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 800
  • Last Modified:

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
0
AngieWill
Asked:
AngieWill
  • 12
  • 7
  • 2
  • +1
3 Solutions
 
rajvjaCommented:
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
0
 
AngieWillAuthor Commented:
I already tried MsgBox(errNumber) but doesn't appear.
When I try MsgBox("toto"),the Message Box appears.
0
 
AngieWillAuthor Commented:
What must I define in Script Task Editor? PrecompileScriptIntoBinaryCode, ReadOnlyVariables...?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Reza RadCommented:
what happen if you use MsgBox(ex.Message) before Throw(ex)
0
 
PedroCGDCommented:
"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
0
 
rajvjaCommented:
When you try with Exec SQL task, have you got any error? WHat do you mean by didn't succeed?
0
 
AngieWillAuthor Commented:
PedroCGD,

what is the syntax? execute or call?
0
 
AngieWillAuthor Commented:
With 'Script Task', I have no error but ErrorCode is empty.
0
 
PedroCGDCommented:
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?

0
 
AngieWillAuthor Commented:
PedroCGD,

I want to call an oracle procedure with as parameters the System::ErrorCode and the System::ErrorDescription.
0
 
AngieWillAuthor Commented:
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.
0
 
PedroCGDCommented:
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?
0
 
Reza RadCommented:
so this error means that you must add your variable  to ReadOnlyVariables or ReadWriteVariables
0
 
AngieWillAuthor Commented:
I added ErrorCode in the ReadOnlyVariables but didn't work.
0
 
PedroCGDCommented:
add to ReadWriteVariables because you are trying to update it, no?!
0
 
AngieWillAuthor Commented:
I add in WriteReadVariables : System::ErrorCode,System::ErrorDescription,System::SourceName

In script :
Dts.VariableDispenser.GetVariables(vars)
vars.Count return 0????
0
 
PedroCGDCommented:
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
0
 
AngieWillAuthor Commented:
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.



0
 
AngieWillAuthor Commented:
Sorry the OnError script is called but without exception
0
 
PedroCGDCommented:
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
0
 
AngieWillAuthor Commented:
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.
0
 
PedroCGDCommented:
In the Even Handler tab, click on
"Click here to create an 'OnError' event handler.... blabla.." in order to add some task you want to send information to oracle about an error that SSIS package fired.

Also you have the SSIS logging (Menu>SSIS>Logging) that enables you to automatically write the events you chosse to a XML, TXT, SQL...

Helped?
0
 
AngieWillAuthor Commented:
Finally, I catch all errors in the application which calls the dtsx package.

Thanks for your help.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 12
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now