none
SSIS OLE DB data flow source hangs connecting to Oracle 11G

    Question

  • I have a set of SSIS packages which pull data from Oracle 10 into SQL Server 2005, working fine. The Oracle data is used by another application on this SS05 database.  Oracle is being upgraded to 11g soon, and the packages fail on our test system.  When I edit my SSIS OLE DB Data Flow Source to display a list of tables, SSIS appears to hang.  In one test the list of tables returned in more then a half hour.  Looking at the query sent from SSIS to Oracle, it is trying to pull back 200,000 objects, and both ends are choking. 



    The OLE DB connection tests successfully. The TNSNAMES.ORA is set correctly, and I can access Oracle from SQL*Plus.  I've tried OLE DB providers from Microsoft, Oracle (11g), and ATTUNITY with similar results.  I would prefer to avoid using ADO.NET because the field mapping will have to be redone

    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 extracted from Oracle while executing

    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

    Thursday, January 28, 2010 2:30 PM

Answers

  • You can't constrain the drop-down box in the OLE DB connection manager object.  Instead, I would suggest that you simply use the SQL mode and type in the SQL you need for your source. 
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Marked as answer by Zongqing Li Wednesday, February 03, 2010 8:06 AM
    Thursday, January 28, 2010 2:51 PM

All replies

  • You can't constrain the drop-down box in the OLE DB connection manager object.  Instead, I would suggest that you simply use the SQL mode and type in the SQL you need for your source. 
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Marked as answer by Zongqing Li Wednesday, February 03, 2010 8:06 AM
    Thursday, January 28, 2010 2:51 PM
  • Even I have notice the delay in populating the DB objects in OLEDB source. As Phil said its better to put the SQL query instead. thats what I do.
    Hope this helps !!
    Sudeep   |    My Blog
    Thursday, January 28, 2010 3:26 PM