Need help to execute a Oracle stored procedure from execute sql task

Question

Need help to execute a Oracle stored procedure from execute sql task

prabhu.st

SSCrazy

Points: 2552
More actions
March 16, 2016 at 2:41 am

#399868

Hi,
what is happening now with ETL
------------------------------------
I have a SSIS package which interacts to a Oracle server and fetch data in a incremental load.
as a fix for an issue I created a table in Oracle server which will have the last loaded data's date value and on the next load it will be excluded while loading. this is to avoid data redundancy.
to do this I have two Execute SQL Tasks
Execute SQL Task 1 - This will fetch the value which will be passed as input for other transformations and DFTs in the package.
Execute SQL Task 2 - this task is the error prone task, here is where I am trying to delete the records from Oracle table.
about Execute SQL Task1
---------------------------
Query used to fetch the value for input parameters
select TOP 1 convert(varchar(27),date_column_name,121) as RecordTimeChar
from TableName where Fileter_column_name = 'condition'
ResuletSet mapping : Result set name : RecordTimeChar Result set Variable: User::RecordTimeChar
about Execute SQL Task2
---------------------------
Query passed from Expression which will be built on the fly dynamically.
"BEGIN
E3Suite.DelPrediSysInclDate('" + @[User::RecordTimeChar] + "');
END;"
ConnectionType: ADO.NET
Connection: E3Suite
SQLSourceType: Direct input
SQLStatement: passing the above code as expression value here.
IsQueryStroredProcedure: True
My Issue:
-----------
I am getting error message when I try to execute the "Execute SQL Task"
what is the Errors
----------------------
[Execute SQL Task] Error: Executing the query "BEGIN
E3Suite.DelPrediSysInclDate('2016-03-01 00:2..." failed with the following error: "ORA-06550: line 3, column 5:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge
The symbol "exit" was substituted for ";" to continue.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
---------------------------- xxx ----------------------------
if I manually Execute the same code which is getting passed in to the Oracle from SSIS, it is working fine. Please help to fix this issue.
Thanks in Advance.
Prabhu.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Caveat, I don't know Oracle at all, but I found this thread which may have something for you to try, http://www.sqlservercentral.com/Forums/Topic1052699-391-1.aspx

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

SSIS won't accept semi-colons with the Oracle drivers.

I'm not sure if you can make it work without the semi-colons removing BEGIN & END.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

prabhu.st SSCrazy Points: 2552 More actions · Answer 3

Hi Jack,

thanks for the information. But it doesn't help me to fix the issue, because I already tried those ideas which were not working..

But I like your intension to help me..

Thanks a lot

prabhu.st SSCrazy Points: 2552 More actions · Answer 4

Hi Luis,

Yes, your answer is approximately correct, I already tried this idea as well, what happened you know, it is getting successfully executed with raw value I mean hardcoded value.. but if execute the same code (as you said removing BEGIN, END & Schema name as well) with the ssis input parameter again it is getting error.

Kindly suggest me, how can i execute the below command with parameter

Sample code with hardcoded value (this is working)

Someprocname('2016-03-16 00:09:540')

Sample code with ssis parameter value which is not working..

Someprocname(@User::RecordMaxTimeChar)