?
Solved

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

Posted on 2010-01-12
23
Medium Priority
?
789 Views
Last Modified: 2013-11-30
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
Comment
Question by:AngieWill
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 7
  • 2
  • +1
23 Comments
 
LVL 11

Expert Comment

by:rajvja
ID: 26292205
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
 

Author Comment

by:AngieWill
ID: 26292223
I already tried MsgBox(errNumber) but doesn't appear.
When I try MsgBox("toto"),the Message Box appears.
0
 

Author Comment

by:AngieWill
ID: 26292232
What must I define in Script Task Editor? PrecompileScriptIntoBinaryCode, ReadOnlyVariables...?
0
Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

 
LVL 30

Accepted Solution

by:
Reza Rad earned 668 total points
ID: 26292242
what happen if you use MsgBox(ex.Message) before Throw(ex)
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 26292245
"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
 
LVL 11

Expert Comment

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

Author Comment

by:AngieWill
ID: 26292347
PedroCGD,

what is the syntax? execute or call?
0
 

Author Comment

by:AngieWill
ID: 26292353
With 'Script Task', I have no error but ErrorCode is empty.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 26292368
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
 

Author Comment

by:AngieWill
ID: 26292439
PedroCGD,

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

Author Comment

by:AngieWill
ID: 26292454
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 26292469
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
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26292490
so this error means that you must add your variable  to ReadOnlyVariables or ReadWriteVariables
0
 

Author Comment

by:AngieWill
ID: 26292531
I added ErrorCode in the ReadOnlyVariables but didn't work.
0
 
LVL 22

Assisted Solution

by:PedroCGD
PedroCGD earned 1332 total points
ID: 26292534
add to ReadWriteVariables because you are trying to update it, no?!
0
 

Author Comment

by:AngieWill
ID: 26292761
I add in WriteReadVariables : System::ErrorCode,System::ErrorDescription,System::SourceName

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

Expert Comment

by:PedroCGD
ID: 26292926
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
 

Author Comment

by:AngieWill
ID: 26293907
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
 

Author Comment

by:AngieWill
ID: 26293932
Sorry the OnError script is called but without exception
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 26293954
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
 

Author Comment

by:AngieWill
ID: 26294059
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
 
LVL 22

Assisted Solution

by:PedroCGD
PedroCGD earned 1332 total points
ID: 26294179
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
 

Author Closing Comment

by:AngieWill
ID: 31676039
Finally, I catch all errors in the application which calls the dtsx package.

Thanks for your help.
0

Featured Post

Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question