Solved

How to execute a cursor from integration services

Posted on 2010-11-24
11
818 Views
Last Modified: 2012-06-26
Hello Experts,
I have a cursor statement that need to execute from Integration Services. The cursor statement refers to an Oracle DB. When I execute it from Oracle SqlDeveloper everything works. In Integration services I create one sql task, I paste inside it the code from Oracle SqlDeveloper and  when I execute the tast I get error : "possible failure reasons: problems with the query , ResultSet property not set correctly ,parameters not set correctly, or connection not established correctly."

any idea ?

Thanks in advance
0
Comment
Question by:spiral2007
[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
  • 6
  • 5
11 Comments
 
LVL 10

Expert Comment

by:Humpdy
ID: 34203466
can you post your code pls
0
 

Author Comment

by:spiral2007
ID: 34203520

DECLARE Saddresscode  varchar(10);
 Saddress      varchar(255);
 Scode1        varchar(5);
 Scode2        varchar(5);
 Szoniid       number;
 Senoriaid     number;
 QADDRESS     NUMBER :=0;

CURSOR AD IS
SELECT addresscode,address,code1,code2,zoniid,enoriaid
FROM ( SELECT COUNT(CODINSTR.DM_ADDRESSES_TEST.ADDRESSCODE) AS TS ,CODINSTR.DM_ADDRESSES_TEST.ADDRESS,CODINSTR.DM_ADDRESSES_TEST.ADDRESSCODE,CODINSTR.DM_ADDRESSES_TEST.CODE1,CODINSTR.DM_ADDRESSES_TEST.CODE2,CODINSTR.DM_ADDRESSES_TEST.ZONIID,CODINSTR.DM_ADDRESSES_TEST.ENORIAID
        FROM CODINSTR.DM_ADDRESSES_TEST
        INNER JOIN CODINSTR.DM_ADDRESSES_TEST dm ON CODINSTR.DM_ADDRESSES_TEST.ADDRESSCODE  = dm.ADDRESSCODE  
        GROUP BY CODINSTR.DM_ADDRESSES_TEST.ADDRESS, CODINSTR.DM_ADDRESSES_TEST.ADDRESSCODE, CODINSTR.DM_ADDRESSES_TEST.CODE1, CODINSTR.DM_ADDRESSES_TEST.CODE2, CODINSTR.DM_ADDRESSES_TEST.ZONIID, CODINSTR.DM_ADDRESSES_TEST.ENORIAID
    )
WHERE ((TS>1 AND ENORIAID>-1) OR(TS=1)) ORDER BY ADDRESS;

BEGIN
OPEN ad;
LOOP
  FETCH ad INTO Saddresscode,Saddress,Scode1,Scode2,Szoniid,Senoriaid;
  EXIT WHEN ad%NOTFOUND; 
    SELECT COUNT(aDDRESS) into QADDRESS  FROM CODINSTR.DM_ADDRESSES WHERE ADDRESS = sADDRESS;
    IF qADDRESS =0 THEN
       INSERT INTO CODINSTR.DM_ADDRESSES (ADDRESSCODE,ADDRESS,CODE1,CODE2,ZONiID,ENORIAID) VALUES (sADDRESSCODE , sADDRESS,sCODE1,sCODE2,sZONiID,sENORIAID);
    END IF;
    qADDRESS := 0;
   	END LOOP; 

close ad;


UPDATE CODINSTR.DM_ADDRESSES SET ENORIAID = NULL WHERE ENORIAID=-1;

Open in new window

0
 
LVL 10

Accepted Solution

by:
Humpdy earned 500 total points
ID: 34203844
ok, so this is oracle sql code which is slightly different to the sql code which your execute task is expecting.

What you will have to do is call the oracle procedure from your t-sql task, not execute your oracle code through the task.
Maybe through linked server or ado.net is the best way
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:Humpdy
ID: 34203862
0
 

Author Comment

by:spiral2007
ID: 34203902
[Execute SQL Task] Error: Executing the query "EXECUTE ( 'begin init_enoriaid(); end;')" failed with the following error: "ORA-00900: invalid SQL statement
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34203961
do you have your oracle code wrapped in a procedure ?
You will need to call the procedure like execute servername.owner.storedprocedure.
0
 

Author Comment

by:spiral2007
ID: 34204117
EXECUTE ( 'begin EXECUTE CODINSTR.INIT_ENORIAID(); end;')


still getting

[Execute SQL Task] Error: Executing the query "EXECUTE ( 'begin EXECUTE CODINSTR.INIT_ENORIAID();..." failed with the following error: "ORA-00900: invalid SQL statement
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34204125
from your SQL management sudio, are you currently able to query a table in your Oracle database.
Is that communication link set up ?
0
 

Author Comment

by:spiral2007
ID: 34204148
I am using integration services.. and in the other flows the sql statements that gets data from oracles tables works fine..
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34204195
can you put a declare in at the beginning, before your BEGIN statement,

also,
can you turn on sql profiler and execute the task and post what the profiler is trying to execute.
0
 

Author Comment

by:spiral2007
ID: 34204681
nothing happens to profiler...
I am using integration services to pass data from access db to oracle db...
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

751 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