Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS OLE DB data source query is choking Oracle 11g Expand / Collapse
Author
Message
Posted Friday, January 22, 2010 12:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 2, 2014 8:21 AM
Points: 168, Visits: 423
I have a set of SSIS packages which pull data from Oracle 10 into SQL Server 2005, working fine. Oracle is being upgraded to 11 soon, and the packages fail on our test system. The OLE DB connection tests successful. I've tried OLE DB providers from Microsoft, Oracle (11g), and ATTUNITY with similar results. When I edit my SSIS OLE DB Data Flow Source to display a list of tables the query sent from SSIS to Oracle is trying to pull 200,000 objects, and both ends are choking.
How can I edit or control the query produced by SSIS to limit the Oracle metadata selected?
Thank you for any assistance.
Alan

Here is the Oracle query generated by SSIS (formatted by Red Gate):
select *
from ( select null table_catalog
,decode(o1.owner, 'PUBLIC', NULL,
o1.owner) table_schema
,o1.object_name table_name
,decode(o1.owner, 'SYS',
decode(o1.object_type, 'TABLE',
'SYSTEM TABLE', 'VIEW',
'SYSTEM VIEW',
o1.object_type), 'SYSTEM',
decode(o1.object_type, 'TABLE',
'SYSTEM TABLE', 'VIEW',
'SYSTEM VIEW',
o1.object_type),
o1.object_type) table_type
,null table_guid
,null description
,null table_propid
,null date_created
,null date_modified
from all_objects o1
where ( ( o1.object_type = 'TABLE'
and o1.generated != 'Y' )
or o1.object_type = 'VIEW' )
union
select null table_catalog
,decode(o2.owner, 'PUBLIC', NULL,
o2.owner) table_schema
,o2.object_name table_name
,o2.object_type table_type
,null table_guid
,null description
,null table_propid
,null date_created
,null date_modified
from all_objects o2
,all_objects o3
,all_synonyms s
where o2.object_type = 'SYNONYM'
and ( ( o3.object_type = 'TABLE'
and o3.generated != 'Y' )
or o3.object_type = 'VIEW' )
and o2.owner = s.owner
and o2.object_name = s.synonym_name
and s.table_owner = o3.owner
and s.table_name = o3.object_name
) tables
order by 4
,2
,3




Post #852243
Posted Friday, January 22, 2010 12:58 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:22 AM
Points: 3,428, Visits: 14,430
Oracle client is required for all development with Oracle and SSIS is development so you need to download and install the 11g client about 1.7gig file. Then you also need to put your TNSNAMES.ORA file in your Oracle home folder. The TNSNAMES.ORA file contains among other things the relevant Oracle permissions.



Kind regards,
Gift Peddie
Post #852278
Posted Friday, January 22, 2010 2:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 2, 2014 8:21 AM
Points: 168, Visits: 423
Oracle client has been installed, and I can access the data from the server using SQL*Plus. The TNSNAMES is correct. In SSIS I can "Test Connection" successfully. When I try to view the table list the systems choke.
Alan



Post #852341
Posted Friday, January 22, 2010 2:48 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:22 AM
Points: 3,428, Visits: 14,430
Alan Spillert (1/22/2010)
Oracle client has been installed, and I can access the data from the server using SQL*Plus. The TNSNAMES is correct. In SSIS I can "Test Connection" successfully. When I try to view the table list the systems choke.
Alan


SQL Plus is not relevant to Microsoft development so you have to try connecting to Oracle in SSRS because you can do that in BID if the result is the same you need to change your code.



Kind regards,
Gift Peddie
Post #852362
Posted Monday, January 25, 2010 10:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 2, 2014 8:21 AM
Points: 168, Visits: 423
Why would SSRS using an OLE DB provider give me a different result the SSIS using the same provider?

The goal here is to extract some data from Oracle for use in a SQL Server database.

All of the code was generated by the SSIS OLE DB data source to display a list of the Oracle tables needed. Is there any way to control this code?

Thanks,
Alan



Post #853176
Posted Monday, January 25, 2010 11:04 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:22 AM
Points: 3,428, Visits: 14,430
Alan Spillert (1/25/2010)
Why would SSRS using an OLE DB provider give me a different result the SSIS using the same provider?

The goal here is to extract some data from Oracle for use in a SQL Server database.

All of the code was generated by the SSIS OLE DB data source to display a list of the Oracle tables needed. Is there any way to control this code?

Thanks,
Alan


I see your point I did not see you were using Oracle system tables to pull the data and that may just be the reason for the problem. The alternative is to use Oracle SQL Developer to look at all the Oracle objects and split the transfer to smaller tasks.



Kind regards,
Gift Peddie
Post #853181
Posted Tuesday, January 26, 2010 9:20 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 4, 2014 9:58 AM
Points: 472, Visits: 711
Hi Alan,

This query is bringing back a list of all tables, views, and synonyms to which the Oracle user account has access. (In your case, that's over 200,000.) This suggests that the Oracle account is way too privileged.

If you know the target schema(s) of interest, add

and /*o1, o2*/.owner in ('SCHEMA1','SCHEMA2')

to the WHERE clauses to cut it down. The query is not efficiently written, but it does run 'as is' on my Oracle system. HTH
Post #853743
Posted Tuesday, January 26, 2010 10:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 2, 2014 8:21 AM
Points: 168, Visits: 423
The ORACLE query is generated by the SSIS OLE DB Data Source when I try to get the list of tables. Does anyone know how to control, to edit, this query?


Post #853803
Posted Wednesday, January 27, 2010 12:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 20, 2010 7:30 PM
Points: 4, Visits: 11
Did you try to use the "TOP" clause?
Post #854652
Posted Thursday, January 28, 2010 6:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 2, 2014 8:21 AM
Points: 168, Visits: 423
The query is generated by SSIS OLE DB data source provider - I do not know how to change it.


Post #855137
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse