The problem is due to not assigning Alias to your query output

In executesql task in General Tab change SqlStatement property as below and in resultset property set as SingleRow and in Result Set tab set
ResultName = RetResult
VariableName = User::RowPresent1

IF EXISTS (SELECT 1 FROM myDestination WHERE RecordID = x)
THEN
   SELECT 1 as RetResult;
ELSE
   SELECT 0 as RetResult;

Hi Mitesh Modi , here i am using oracle database,

so, i changed code to

declare c integer; X integer;
begin select count(*) into c from ba_account where NAME='UNKNOWN';
if c > 0 then X :=1; else X :=0; end if;
end

in Result Set tab i set ResultName = X VariableName = User::RowPresent1

it is giving error:

Error: No result rowset is associated with the execution of this query. [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowPresent1": "Exception from HRESULT: 0xC0015005".

then i set Result Name to number 0 it is giving same error ,

then next i set to 1 same error,

then X same error

please Suggest me for this what i want to set or any change in the oracle code ?

Hi Karteek,

I think in oracle column aliasing is working like this. in your example you have declare x variable. there is no need to declare variable.

In oracel aliasing working like that below

SELECT table1.Col1 AS "This is an alias" FROM table1

OR

SELECT table1.Col1 "This is an alias" FROM table1

So you have to execute code like that

declare c integer;
begin 
select count(*) into c from ba_account where NAME='UNKNOWN';
if c > 0 then 
   select 1 "RetResult"; 
else 
   select 0 "RetResult";  
end if;
end

and then

ResultName = RetResult
VariableName = User::RowPresent1

http://www.iforerunner.com/SQL/elearningcolumnalias.html

HI Mitesh Modi ,Thanks for your Fast reply

whenever i am executing above mentioned code

declare c integer; begin select count(*) as c from ba_account where NAME='UNKNOWN'; if c > 0 then select 1 "RetResult" ; else select 0 "RetResult" ; end if; end;

it is giving error message

[Execute SQL Task] Error: Executing the query "declare c integer; begin select count(*) into c ..." failed with the following error: "ORA-06550: line 7, column 27: PL/SQL: ORA-00923: FROM keyword not found where expected ORA-06550: line 7, column 4: PL/SQL: SQL Statement ignored ORA-06550: line 9, column 26: PL/SQL: ORA-00923: FROM keyword not found where expected ORA-06550: line 9, column 4: PL/SQL: SQL Statement ignored ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

so, i changed it to

declare c integer; begin select count(*) as c from ba_account where NAME='UNKNOWN'; if c > 0 then select 1 as "RetResult" from dual ; else select 0 as "RetResult" from dual ; end if; end;

then error message:

[Execute SQL Task] Error: Executing the query "declare c integer; begin select count(*) as c fr..." failed with the following error: "ORA-06550: line 4, column 1: PLS-00428: an INTO clause is expected in this SELECT statement ORA-06550: line 7, column 4: PLS-00428: an INTO clause is expected in this SELECT statement ORA-06550: line 9, column 6: PLS-00428: an INTO clause is expected in this SELECT statement ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

*please NeedHelpful.*

you have written

select count(*) as c from ba_account

instead of

select count(*) into c from ba_account

like that below

declare c integer; 
begin 
select count(*) into c from ba_account where NAME='UNKNOWN'; 
if c > 0 then 
select 1 as "RetResult" from dual ; 
else 
select 0 as "RetResult" from dual ; 
end if; 
end;

i run the package with above code

error message is:

[Execute SQL Task] Error: Executing the query "declare c integer; begin select count(*) into c ..." failed with the following error: "ORA-06550: line 5, column 1: PLS-00428: an INTO clause is expected in this SELECT statement ORA-06550: line 7, column 1: PLS-00428: an INTO clause is expected in this SELECT statement ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

so i changed to

declare c integer; begin select count(*) into c from ba_account where NAME='UNKNOWN'; if c > 0 then select 1 into "RetResult" from dual ; else select 0 into "RetResult" from dual ; end if; end;

error message:

[Execute SQL Task] Error: Executing the query "declare c integer; begin select count(*) into c f..." failed with the following error: "ORA-06550: line 5, column 16: PLS-00201: identifier 'RetResult' must be declared ORA-06550: line 5, column 28: PL/SQL: ORA-00904: : invalid identifier ORA-06550: line 5, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 7, column 15: PLS-00201: identifier 'RetResult' must be declared ORA-06550: line 7, column 27: PL/SQL: ORA-00904: : invalid identifier ORA-06550: line 7, column 1: PL/SQL: SQL Statement ignored ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

so i changed code to :

declare c integer; declare RetResult integer; begin select count(*) into c from ba_account where NAME='UNKNOWN'; if c > 0 then select 1 into "RetResult" from dual ; else select 0 into "RetResult" from dual ; end if; end;

error message:

[Execute SQL Task] Error: Executing the query "declare c integer; declare RetResult integer; begi..." failed with the following error: "ORA-06550: line 2, column 1: PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function package pragma procedure subtype type use form current cursor The symbol "begin" was substituted for "DECLARE" to continue. ORA-06550: line 10, column 4: PLS-00103: Encountered the symbol "end-of-file" 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

Hi karteek,
I haven't installed oracle so not able to help you.

I think the easiest way is to write that whole stuff in server side by creating procedure.

call procedure like below from executesql task

begin procTest; end;

http://social.msdn.microsoft.com/Forums/eu/sqlintegrationservices/thread/8a5998f5-2dad-4634-9be1-24cdbb74fb39

or try this way

http://www.mssqltips.com/sqlservertip/1448/execute-plsql-code-in-an-oracle-package-from-ssis/

Thanks Mitesh Modi for Quick response, i created a stored procedure

CREATE OR REPLACE PROCEDURE Test143 AS
c number(20);
X number(20);
BEGIN SELECT COUNT(*) into c FROM ba_account where NAME='UNKNOWN'; if c > 0
then
X := 1;
else
X := 0;
end if; END;

case 1: in the ResultSet tab i set

        ResultName : 0,

it is giving error message

[Execute SQL Task] Error: Executing the query "exec Test143" 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.

case 2:in the ResultSet tab i set

        ResultName : X,

error message:

[Execute SQL Task] Error: Executing the query "exec Test143" 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.

case 3:in the ResultSet tab i set

        ResultName : 1,

error message:

[Execute SQL Task] Error: Executing the query "exec Test143" 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.

please suggest me what value we have to set for ResultName ? or any other changes in stored procedure ?

please NeedHelpful.

Here your procedure doesn't return any thing. execute procedure in oracle and its output should be something like that

X
-
1

before end of procedure you have to write select query which return x as column with o or 1 as value

Hi Mitesh Modi thanks for your quick responce,

you mentioned before end of procedure you have to write select query which return x as column with o or 1 as value

i changed procedure:

CREATE OR REPLACE PROCEDURE Test143 (RECCOUNT OUT NUMBER) AS
c number;
X number;
BEGIN SELECT COUNT(*) into c FROM baaccount where NAME='UNKNOWN'; if c > 0
then
X := 1;
else
X := 0;
end if; select X into REC_COUNT from dual;

-- DBMSOUTPUT.PUTLINE(REC_COUNT); END;

here REC_COUNT is out parameter i executed this code in sqldeveloper it is working fine.

in execute sql task:

sql statements :

1) how to call this stored procedure ,

so its return value which can be assigned to ResultName in ResultSet tab ,??

in general oracle procedure with parameters can be execute using

declare id1 number; begin Test143(id1); end;

it return some value either 0 or 1

2) how above statements should be written into execute sql task--sql statements--??

or other better appraoch for Overall to this Thread.

my aim is if the record is not loaded into table then next dataflow task should insert this value

in next dataflow task i can make use of text file contains all the required records. this text file is used as source lookup with this table

then unmatched records will get.

this unmatched records will be loaded into this table

but this approach is not correct . so, for this i am Forcefully using this execute sql task with precedence constraint

Unfortunately this is not working.....

try this link

http://technet.microsoft.com/en-us/library/cc280502.aspx

http://social.msdn.microsoft.com/Forums/en-GB/sqlintegrationservices/thread/4b46880b-709d-4787-bd35-b975c69f0334

/*OLEDB Connection Type*/
EXEC Test143 ? OUTPUT  


/*ODBC Connection Type*/  
CALL Test143 ? OUTPUT

what value we have to set for ResultName in ResultSet tab ,?

try this link

http://blogs.msdn.com/b/mattm/archive/2006/11/08/stored-procedures-with-output-parameters.aspx

http://www.bidn.com/blogs/BillBrannen/bidn-blog/612/stored-procedure-return-values-and-output-parameters-in-ssis

Try this below code

/*OLEDB Connection Type*/
EXEC Test143 ? OUTPUT

set Resultset = None

and in parameter mappings

variablename = @x
Direction = Output
DataType = Long
Parameter Name = 0
Parameter Size = -1

see the first link of my previous post

Hi Mitesh Modi thanks for your quick responce,

as per your Suggestion , i created: user variable :x, datatype : int32 ,Value: 0, and in the Parameter Mapping as per your suggestion i added and i executed the package but

error message:

**Error: ORA-00900: invalid SQL statement

[Execute SQL Task] Error: Executing the query "EXEC Test143 ? OUTPUT" failed with the following error: "One or more errors occurred during processing of command.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.**

In resultset property set Resultset = None instead of SingleRow.

if it not work then check this link

http://blogs.msdn.com/b/mattm/archive/2006/11/08/stored-procedures-with-output-parameters.aspx

Another is to use script task like below

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/90841f4f-b840-423a-b409-0a8d1be02fc8

Are you using OLDDB Connection?

Hi Mitesh Modi ,

in Execute Sql Task --General tab- i set Resultset = None

i am using Connection Type:OLE DB

i gone through

http://blogs.msdn.com/b/mattm/archive/2006/11/08/stored-procedures-with-output-parameters.aspx

but it is not working for oracle stored procedure.,

Finally I realized that in my Scenario, Oracle Statements does not work with Execute Sql task.

Thanks for Valuable time spent on this thread