SSIS OLE DB data source query is choking Oracle 11g

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Working with Oracle » SSIS OLE DB data source query is choking...

12 posts, Page 1 of 2

1 2 »»

SSIS OLE DB data source query is choking Oracle 11g

Rate Topic

Display Mode

Topic Options

Author

Message

Alan Spillert

Posted Friday, January 22, 2010 12:23 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 17, 2013 2:17 PM
Points: 163, Visits: 384

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

Gift Peddie

Posted Friday, January 22, 2010 12:58 PM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, June 13, 2013 9:43 AM
Points: 3,432, Visits: 14,334

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

Alan Spillert

Posted Friday, January 22, 2010 2:11 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 17, 2013 2:17 PM
Points: 163, Visits: 384

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

Gift Peddie

Posted Friday, January 22, 2010 2:48 PM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, June 13, 2013 9:43 AM
Points: 3,432, Visits: 14,334

Alan Spillert (1/22/2010)

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

Alan Spillert

Posted Monday, January 25, 2010 10:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 17, 2013 2:17 PM
Points: 163, Visits: 384

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

Gift Peddie

Posted Monday, January 25, 2010 11:04 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, June 13, 2013 9:43 AM
Points: 3,432, Visits: 14,334

Alan Spillert (1/25/2010)

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

Nice Marmot

Posted Tuesday, January 26, 2010 9:20 AM

Old Hand

Group: General Forum Members
Last Login: Friday, May 24, 2013 8:58 AM
Points: 394, Visits: 630

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. Wink

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

Alan Spillert

Posted Tuesday, January 26, 2010 10:29 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 17, 2013 2:17 PM
Points: 163, Visits: 384

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