CODEWITHSUNDEEP
sql-serveroracle-databasessisresultset
Simix48
Simix48

Reputation: 55

SSIS take oracle variable and use them in a ssis resultset

I have an execute sql task in one of my ssis package. I need to check if some of my tables have values inside them and store the result in a variable.I then need to use those variables later on in my package. My question is how do I assign those oracle variable from my sql task to my user variable in an ssis package.

I have something that look like this:

Variable
s5 NUMBER 
variable s6 number 
variable s7 number 
begin
select count(*) into s5 from GC_somethin where rownum < 100;
select count(*) into s6 from GC_somethingabc where rownum < 100;
select count(*) into s7 from GC_dddcccc where rownum < 100;
END;
select s5 AS 's51' from dual;

I have also fix my resultset to single row and in result set I have avariable name like s51 and the name of the variable I assigned to that. IT gives my the following error:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "TSERVADD_AFTER": "Exception from HRESULT: 0xC0015005".

Upvotes: 1

Views: 1473

Answers (1)

EKOlog
EKOlog

Reputation: 395

Try to use SSIS variables instead of Oracle.

  1. Create 3 variables, ie. s5, s6, s6 with intiger type.
  2. Create Execute Sql Tasks for every query, set result set to single row and assign results sets to previously created variables.
  3. Use filled variables in SSIS.

Of course, reformat your queries to:

select count(*) as s5 from GC_somethin where rownum < 100

Sql statement for single Sql Task:

with cte as(
select count(*) as s5, null as s6, null as s7 from GC_somethin 
union all 
select null as s5, count(*) as s6, null as s7 from GC_somethingabc 
union all
select null as s5, null as s6, count(*) as s7 from GC_dddcccc 
)
select max(s5) as s5, max(s6) as s6, max(s7) as s7 from cte

Upvotes: 1

Related Questions