Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS OLE DB data source query is choking Oracle 11g


SSIS OLE DB data source query is choking Oracle 11g

Author
Message
Alan Spillert
Alan Spillert
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 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



Gift Peddie
Gift Peddie
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3474 Visits: 14456
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
Alan Spillert
Alan Spillert
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 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



Gift Peddie
Gift Peddie
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3474 Visits: 14456
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
Alan Spillert
Alan Spillert
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 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



Gift Peddie
Gift Peddie
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3474 Visits: 14456
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
Nice Marmot
Nice Marmot
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 Visits: 767
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
Alan Spillert
Alan Spillert
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 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?



"Walkair" Steve Rezhener
"Walkair" Steve Rezhener
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 11
Did you try to use the "TOP" clause? ;-)
Alan Spillert
Alan Spillert
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 423
The query is generated by SSIS OLE DB data source provider - I do not know how to change it.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search