locked
Does Fuzzy Lookup work with Oracle ? RRS feed

  • Question

  •  

    Sorry, this might be an obvious question, but I can not find anything in the documentation/forum.

     

    I want to use a Fuzzy Lookup between 2 Oracle tables.

    I select the Reference Table.

    I then switch to the Columns tab, but the "Available Input Columns" and "Available Lookup Columns" lists are always empty.

     

    I have experimented quite a bit, but to no avail. I noticed this on the Reference Table tabpage : "The table maintenance feature requires the installation of a trigger on the reference table". My guess would be that SSIS does not support Oracle for this, but I am not able to find anything in the documentation that it doesn't.

     

    Any answer/pointer greatly appreciated.

     

    Thanks

     

    Jan Vandepitte

     

     

     

    Friday, April 13, 2007 7:35 AM

Answers

  • Hi Jan,

     

    The Fuzzy Lookup and Fuzzy Grouping components are SQL Server only.

     

    I'm sure the answer is also in books online somewhere, but the SSIS Connectivity Wiki has a simple table that shows the data provider options for each of our components.

     

    Hope that helps!

     

    ~Matt

     

    Friday, April 13, 2007 11:54 PM

All replies

  • Haven't tried it against Oracle, but my first question would be whether you have permission to create tables and triggers in the db.
    Friday, April 13, 2007 1:37 PM
  • Hi Jan,

     

    The Fuzzy Lookup and Fuzzy Grouping components are SQL Server only.

     

    I'm sure the answer is also in books online somewhere, but the SSIS Connectivity Wiki has a simple table that shows the data provider options for each of our components.

     

    Hope that helps!

     

    ~Matt

     

    Friday, April 13, 2007 11:54 PM
  • Hi Mat,

    I have a SSIS script which uses 'Fuzzy Lookup Transformation'component.
    Currently this script uses SQL Server as backend. I have to migrate this script to Oracle as backend.

    If 'Fuzzy Lookup Transformation' does not support for Oracle datbase then which component i should use
    to migrate the script.

    Thanks in advance.

    Regards,
    Sumit Kapoor
    Monday, September 22, 2008 2:49 PM
  • Great question...its always a challenge to get good "meta data" from the SQL Oracle Provider.  I tried to perform a fuzzy lookup against our Oracle database via the OLE DB Provider for Oracle without much luck.

     

    Are you using SSIS to manipulate data that must reside in Oracle? 

     

    My suggestion would be to import the Oracle data via the OLE DB Provider and pull the table in SQL Server "land" and then do your lookups from there.  It adds a few more steps but they are simple steps and your guaranteed success with you Fuzzy Lookups and Groupings. 

     

    Now this is SQL 2005...who knows if SQL 2008 is "smarter".

     

    The other option at you hands would be to see if using a "LINKED SERVER" connection in SQL Server to put a layer of abstraction between you and Oracle.  Then SSIS would think its doing everything in SQL "land" when its really performing on a linked server instance with is really Oracle.

     

    Good Luck

    Quinn 

     

    Monday, September 22, 2008 4:22 PM