SSIS OLE DB data source query is choking Oracle 11g
-
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
-
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 -
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
-
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 -
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
-
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 -
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
-
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?
-
Did you try to use the "TOP" clause? 😉
-
The query is generated by SSIS OLE DB data source provider - I do not know how to change it.
-
Hi Alan,
If you cannot change the query, then you would have to reduce the number of objects accessible to the Oracle user account you are using to connect.
It sounds as if the Oracle user has been granted SELECT ANY TABLE or something equally lazy on your test 11g instance. Ask your Oracle DBA to restrict the privileges granted so that the user account only has access to the tables it needs for the application. HTH
-
I don't think you will be able to resolve this issue from the SSIS side. 200,000 objects is crazy to start with and probably the SSIS team never expected to have such situation. You have to search for solution from the Oracle side. Like define a different schema and link to use only limited set of tables and views.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply