Link to home
Create AccountLog in
Avatar of SQLSearcher
SQLSearcher

asked on

SSIS Query Oracle and is getting the wrong figures

Hello Experts Exchange
I have written a SQL script to query oracle and insert data into a table.  When I run this script in SQL Developer I get the correct figures, however when I configure SSIS to run the script the figures are wrong.

I have attached a spreadsheet with the table I am building, one sheet with the figures from SQL Developer and the other sheet with the figures from SSIS.

I am running the SQL Script in a Execute SQL Task in SSIS.

This is the script I am running;

declare
    i number;
    l number;
    k number := 3;
    m number;
    sqlcmd varchar(30000);
    week_start number;
    week_end number;
    PN_INCOME Number := 0;
    table_does_not_exist exception;
    pragma exception_init(table_does_not_exist, -942);
    
Cursor C1 (PN_Week number) is
Select Sum(Z.TTL_INCOME)
From (select SI_LOC_ID, SI_YEAR, SI_WEEK,
sum((si_income1+si_income2+si_income3+si_income4+si_income5+si_income6+si_income7+si_income8+si_income9+si_income10+si_income11+si_income12+si_income13+si_income14+si_income15)-nvl(SI_VAT_TOTAL,0)) as ttl_income
, sum(si_no_transactions) as tran_cnt, sum(nvl(SI_VAT_TOTAL,0)) as vat_tot 
from SITE_INCOME 
where SI_YEAR = to_char(sysdate, 'YYYY') 
group by SI_LOC_ID, SI_YEAR, SI_WEEK, si_no_transactions) Z
Where Z.SI_Week = PN_Week
and SI_Loc_ID in (Select LG_LOC_ID from Location_Groups lg inner join locations l on lg.LG_LOC_ID = l.LOC_ID 
                  inner join Location_Group_Types lgt on lgt.LGT_ID = lg.LG_LGT_ID where lgt.LGT_DESCRIPTION = 'SCOTLAND' 
                  and store_status(l.LOC_DATE_OPENED, l.LOC_DATE_CLOSED) = 'L' and lgt.LGT_TYPE = 'R' and l.loc_loc_ty_id = 4);

Cursor C2 (PN_Month number) is
Select Sum(Z.TTL_INCOME)
From (select SI_LOC_ID, SI_YEAR, SI_WEEK,
sum((si_income1+si_income2+si_income3+si_income4+si_income5+si_income6+si_income7+si_income8+si_income9+si_income10+si_income11+si_income12+si_income13+si_income14+si_income15)-nvl(SI_VAT_TOTAL,0)) as ttl_income
, sum(si_no_transactions) as tran_cnt, sum(nvl(SI_VAT_TOTAL,0)) as vat_tot 
from SITE_INCOME 
where SI_YEAR = to_char(sysdate, 'YYYY') 
group by SI_LOC_ID, SI_YEAR, SI_WEEK, si_no_transactions) Z
Where Z.SI_Week Between (Select min(rw_week_number) from retail_weeks where rw_year = to_char(sysdate, 'YYYY') and RW_MONTH = PN_Month group by rw_month)
and (Select max(rw_week_number)  from retail_weeks where rw_year = to_char(sysdate, 'YYYY') and RW_MONTH = PN_Month group by rw_month)
and SI_Loc_ID in (Select LG_LOC_ID from Location_Groups lg inner join locations l on lg.LG_LOC_ID = l.LOC_ID 
                  inner join Location_Group_Types lgt on lgt.LGT_ID = lg.LG_LGT_ID where lgt.LGT_DESCRIPTION = 'SCOTLAND' 
                  and store_status(l.LOC_DATE_OPENED, l.LOC_DATE_CLOSED) = 'L' and lgt.LGT_TYPE = 'R' and l.loc_loc_ty_id = 4);

Cursor C3 (PN_Month1 number,PN_Month2 number) is
Select Sum(Z.TTL_INCOME)
From (select SI_LOC_ID, SI_YEAR, SI_WEEK,
sum((si_income1+si_income2+si_income3+si_income4+si_income5+si_income6+si_income7+si_income8+si_income9+si_income10+si_income11+si_income12+si_income13+si_income14+si_income15)-nvl(SI_VAT_TOTAL,0)) as ttl_income
, sum(si_no_transactions) as tran_cnt, sum(nvl(SI_VAT_TOTAL,0)) as vat_tot 
from SITE_INCOME 
where SI_YEAR = to_char(sysdate, 'YYYY') 
group by SI_LOC_ID, SI_YEAR, SI_WEEK, si_no_transactions) Z
Where Z.SI_Week Between (Select min(rw_week_number) from retail_weeks where rw_year = to_char(sysdate, 'YYYY') and RW_MONTH = PN_Month1 group by rw_month)
and (Select max(rw_week_number)  from retail_weeks where rw_year = to_char(sysdate, 'YYYY') and RW_MONTH = PN_Month2 group by rw_month)
and SI_Loc_ID in (Select LG_LOC_ID from Location_Groups lg inner join locations l on lg.LG_LOC_ID = l.LOC_ID 
                  inner join Location_Group_Types lgt on lgt.LGT_ID = lg.LG_LGT_ID where lgt.LGT_DESCRIPTION = 'SCOTLAND' 
                  and store_status(l.LOC_DATE_OPENED, l.LOC_DATE_CLOSED) = 'L' and lgt.LGT_TYPE = 'R' and l.loc_loc_ty_id = 4);

begin
        sqlcmd := 'Insert into TR_' || 'SCOTLAND' || '(OrderBy,f1)Values (7,''LFL Sales T/Y (£)'')';
        execute immediate sqlcmd;
        
        PN_INCOME:= 0;
        Open C3(1,12);
        Fetch C3 into PN_INCOME;
        Close C3;
                                    
        If PN_INCOME is Null then 
          PN_INCOME:= 0; 
                  
        END If;
        sqlcmd := 'Update TR_' || 'SCOTLAND' ||' set f2=' ||PN_INCOME|| ' Where f1 = ''LFL Sales T/Y (£)''';
        execute immediate sqlcmd;
        
        for m in 1..12 loop
          Select min(rw_week_number) into week_start from retail_weeks where rw_year = to_char(sysdate, 'YYYY') and rw_month = m group by rw_month; 
          Select max(rw_week_number) into week_end from retail_weeks where rw_year = to_char(sysdate, 'YYYY') and rw_month = m group by rw_month;
              
              for i in week_start..week_end loop
                  PN_INCOME:= 0;
                  Open C1(i);
                  Fetch C1 into PN_INCOME;
                  Close C1;
                                    
                  If PN_INCOME is Null then 
                    PN_INCOME:= 0; 
                  
                  END If;
                  
                  sqlcmd := 'Update TR_' || 'SCOTLAND' ||' set f'|| k ||' =' ||PN_INCOME|| ' Where f1 = ''LFL Sales T/Y (£)''';  
                  
                  execute immediate sqlcmd;
                  k:=k+1;
              end loop;
          
          PN_INCOME:= 0;
          Open C2(m);
          Fetch C2 into PN_INCOME;
          Close C2;
          
              
          If PN_INCOME is Null then 
          PN_INCOME:= 0; 
                        
          END If;
         
          sqlcmd := 'Update TR_' || 'SCOTLAND' ||' set f'|| k ||' =' ||PN_INCOME|| ' Where f1 = ''LFL Sales T/Y (£)''';
          execute immediate sqlcmd;
          k:=k+1;
          
        end loop;  
        
        Commit;
          
End;

Open in new window


In SSIS it is the same apart from where it says 'SCOTLAND' this is a question mark, so I can use a parameter.

Can anyone suggest why I am getting a different figure in SSIS?

Regards

SQLSearcher
SQLDeveloper-SSIS.xlsx
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Which Oracle client are you using in SSIS?
Avatar of SQLSearcher
SQLSearcher

ASKER

Hello Vitor
The client I am using is Oracle 11g 32bit.

Regards

SQLSearcher
SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Hi Vitor
I am using the Oracle OLE DB Driver.

Regards

SQLSearcher
ASKER CERTIFIED SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Hello Vitor
I have run the sql script without parameters using the ODBC drivers and I have got the correct figures.

Why would the Oracle OLE DB Driver give me the incorrect figures?

I installed the OLE DB driver as part of the Oracle client install, is there a updated version I can use?

Regards

SQLSearcher
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Hi Vitor
I am now trying to use Parameters in my query with ODBC but it is not working.

Under the settings Parameter Mapping I have set Data type to SQL_Char and reset parameter names to start from 1,2,3....

but I get this error message.

[Execute SQL Task] Error: Executing the query "declare
    i number;
    l number;
    k number :..." 
failed with the following error: "[Oracle][ODBC][Ora]ORA-00942: table or view does not exist
ORA-06512: at line 56
". 
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, 
or connection not established correctly.

Open in new window


Do you know what I can try?

Regards

SQLSearcher
The error is about a non existence object (table or view). Are you passing the table/view name by parameter?
Yes part of the table name is a parameter.

This is the code it is on;

sqlcmd := 'Insert into TR_' || ? || '(OrderBy,f1)Values (7,''LFL Sales T/Y (£)'')';
        execute immediate sqlcmd;

Open in new window

I'm not sure how Oracle handles that. I know that SQL Server doesn't like it.
You should try to print the command before executing it to see if table name is correctly formed. Another option is to send full table name instead of concatenate it with "TR_".
Thank you for your help.