Error on getting return value from Oracle procedure in SSIS package
In my case, I want to execute a stored procedure in Oracle database using SSIS package and if there has error in Oracle, I want to get the error message from Oracle and pass it to SSIS to log the error. I can execute the stored procedure through SSIS successfully, but the problem is I cannot get the error message using OUT parameter of procedure in Oracle, the following part is what I have done to test whether SSIS can fulfill my requirement: 1. Oracle client installed, use Microsoft OLE DB Provider for Oracle, Test Connection to Oracle succeeded. 2. The stored procedure created in Oracle database: create or replace p1(in1 varchar2, out1 OUT varchar2) as begin out1 := 'nice'; end; 3. In the SSIS package, I add a "Execute SQL Task" component: Variable defined = User::output (String datatype) ResultSet = Single Row SQLSourceType = Direct Input SQLStatement = declare in1 varchar2(10); out1 varchar2(10); begin in1 := 'wow'; out1 := '1a2'; sys.p1(in1, out1); end; In Result Set tab: Result name = out1, Variable name = User::output Then, when I run the package, the error is: Error: No result rowset is associated with executio of this query. [Execute SQL Task] Error: An error occurred while assigning a value to variable "output" So, is there anything I can do to solve the problem? Seems I need to return a result set from Oracle... Any help would be great. Thanks. Info: use SQL Server 2005 SP3, Oracle Client 9i, Oracle Database 10g
October 28th, 2010 10:48pm
Try using EXEC ? = P1 ? OUTPUT in the Execute SQL Task. The first ? is the return value. Is this helpful too: http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/90841f4f-b840-423a-b409-0a8d1be02fc8 ?Arthur My Blog
October 28th, 2010 11:06pm
Thank you for your reply. I tried your first suggestion, but seems the word "OUTPUT" is not supported in Oracle. For the second suggestion, I tried to convert the code from C# to VB, but it has type error "OracleConnection" (Type "OracleConnection" si not defined) even I tried to import Oracle class.
November 1st, 2010 5:22am
Hi kingkong0924, Coudl you please set the "Resultset Name" to be 1 (number 1), and check what happens? Thanks, Jin ChenJin Chen - MSFT
November 1st, 2010 6:22am
Hi Jin, I tried to set it (Resultset = Single row, Result Name = 1, Variable Name = User::output). It returns the same error in SSIS: Error: No result rowset is associated with execution of this query. [Execute SQL Task] Error: An error occurred while assigning a value to variable "output" Thanks.
November 1st, 2010 10:03pm
Just found that there has a technical article stating that Under Oracle Connectivity Although the OraOLEDB.Oracle.1 provider supports the new Oracle data types, neither this provider nor MSDAORA supports executing stored procedures with parameters that provide result sets. SSIS is unable to use the metadata APIs against the Oracle database. http://msdn.microsoft.com/en-us/library/bb332055(SQL.90).aspx Poor SSIS...
November 26th, 2010 3:28am