Solved

How to execute a cursor from integration services

Posted on 2010-11-24
11
813 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
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

831 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