Welcome to All Things Data Integration: Announcements, Insights, Best Practices, Tips & Tricks, and Trend Related...

Connecting to Microsoft Excel using Oracle Data Integrator

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Topology, Data Server, Physical and Logical Architecture are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for more details.

In this post I will describe how a Microsoft Excel spreadsheet can be used in Oracle Data Integrator.
Microsoft Excel is one of the many different technologies you can leverage in ODI as a source or as a target.

Prepare your Excel spreadsheet

Prior to using a Microsoft Excel spreadsheet in ODI we need to specify a name for the different cell tables we want to use. You can have multiple names in the same spreadsheet.

First open up a Microsoft Excel spreadsheet, we will need to define a named range.


To do so, start by selecting the data you are interested in.


Then go to the Formulas tab and click on Define Name.


Specify a name in Name field, I used CITY_DATA in this example.


Click OK.

Note: This is slightly different in older versions of Microsoft Excel, please refer to its documentation to find out more information about how to name cells.

Define an ODBC Data Source

ODI will use an ODBC connection to natively access Microsoft Excel.


Open up the Data Sources menu in Microsoft Windows: click on the Start menu, go to Administrative Tools then click on Data Sources (ODBC).

The ODBC Data Source Administrator window will open up.


Click on Add...


In the Create New Data Source window, pick Microsoft Excel Driver and click on Finish.


In the ODBC Microsoft Excel Setup, enter any name in the Data Source Name field.
I used XLS_SRC_CITY in this example. This name will be used in Topology Manager to refer to this ODBC Data Source.

Click on Select Workbook...


Using the File Explorer select your Excel file and click on OK.
Make sure that the Read Only checkbox is unchecked otherwise you will get an error message while trying to insert data into the Excel spreadsheet (Error: '[Microsoft][ODBC Excel Driver] Operation must use an updateable query.').


You should now see your new ODBC Data Source listed in the ODBC Data Source Administrator.

Create a Data Server in Topology Manager

Open up Topology Manager and go to Physical Architecture.

Right on the Microsoft Excel technology and select Insert Data Server.


In the Data Server window enter a name in the Name field, I picked XLS_SRC_CITY.


Go to the JDBC tab.


Select the Sun JDBC-ODBC bridge in the JDBC Driver List.

In the JDBC URL template replace <odbc_dsn_alias> with the name of the ODBC Data Source you specified earlier. I used XLS_SRC_CITY in this example.


Here are the JDBC settings I used in this example:

· JDBC Driver: sun.jdbc.odbc.JdbcOdbcDriver

· JDBC URL: jdbc:odbc:XLS_SRC_CITY

Click on Test and make sure you get a successful connection.

Click OK.

In the Physical Schema window go to the Context tab to define a Logical Schema.


Click on clip_image030to insert a new row, select your context and enter a Logical Schema name in the Logical Schema column. I used XLS_SRC_CITY in this example.

Click OK to save and close the Physical Schema window.

We now have successfully created the infrastructure we need to use Microsoft Excel in our ODI processes.

In another post I will describe how to use Microsoft Excel as a source and a target in ODI.

Join the discussion

Comments ( 57 )
  • guest Thursday, October 21, 2010
    Is it just me or there simply no flexibility if you need to define data area (named range) before hand? Most use cases you'd want to automate ETL, thus data source must support varying number of entries (rows) at the very least.
    Is there any work around for this?
  • Anshuman Jain Thursday, February 17, 2011
    Is it possibel to have work on multiple sheets under one excel workbook ?
  • julien.testut Thursday, February 17, 2011
    Hi Anshuman,
    Yes if you have multiple spreadsheets they will appear as different tables when you reverse engineer the XLS file.
  • shivesh Wednesday, May 11, 2011
    Is there any way in Oracle Data Integrator to fetch the path of the MS-Excel file or file name which we use as a parameter to run in the interface without hard coding the file name or its path?
  • Julien Thursday, May 12, 2011
    Hi Shivesh,
    You can use an ODI variable as a startup parameter for your scenario and populate it with the path or the filename. You can find more information about this in the following blog post: http://blogs.oracle.com/dataintegration/entry/using_parameters_in_odi_the_dy_1.
    You could store the file names and their paths in a DB table and refresh your variable using it to dynamically pass that information to the ODI scenario.
  • Marco Monday, July 18, 2011

    Hi Julien,

    Same question here as the first one: Is there a way to process an excel file without having to define a range before hand? Because for example, we'll be working with a lot of auto-generated excel files that have no range definitions in them. Would we need to setup a 'define ranges' step (either done manually or if automatically if possible) first in the entire process before we could proceed with the data integration? Hope you could enlighten me.

    Thanks and Regards,


  • Julien Tuesday, July 19, 2011

    Hi Marco,

    Yes this is possible, please have a look at Doc ID 424442.1 on My Oracle Support. This article walks you through how to set this up.



  • guest Wednesday, October 5, 2011

    is it possible to import/export data from oracle 10g database server onto excel file. and how?

  • Julien Friday, October 7, 2011


    Yes, it is possible to load data from Oracle 10g into Excel using ODI. The Excel chapter in the ODI Doc will help: http://download.oracle.com/docs/cd/E21764_01/integrate.1111/e12644/excel.htm#insertedID0. Look at section 8.6.2.



  • Alex Monday, October 17, 2011

    Hi Julien,

    i was learning ODI while follwing your guide, and i got the follwing error while importing the data source into Topology Manager:

    "microsoft obdc driver manager data source name not found and no default driver specified"

    Any idea why it happnened? I triple checked the names, and i defined the data source within "User DNS" from odbcad32.exe

    Thanks for help

  • David Tuesday, October 18, 2011

    Hi Alex

    You can check the following posts for clues;


    Sounds like some misconfiguration of the DSN for the context in which you are running ODI.



  • Spanish_Guest Tuesday, January 10, 2012

    Hey I found the information in this post really interesting. I would like to confirm something.

    I am trying to upload some info from an excel file to ODI, and after that to make ODI check if there is any update on that file and upload it.

    Can I do this with ODI?

    I do rally appreciate your help and guidance as I am new to this tech.

    Best regards.


  • Julien Tuesday, January 10, 2012

    Hi Jaison,

    There is no mechanism available to check if changes have been made to an Excel spreadsheet. You could check at the OS level (in an ODI procedure using some Groovy or Jython code) if the file has been modified and then upload it again but there is no way to detect which changes have been made in the file itself.



  • Spanish_Guest Thursday, January 12, 2012

    Thanx so much for your answer, I have already manged to upload from an excel file!

    Now, I have gotten another question, to upload info now from an Oracle_DB_A to another Oracle_DB_B should i do the same procedure but now with the Oracle technology, no?

    Once again thanx so much for your help, guidance and time.

    Best regards.


  • Julien Thursday, January 12, 2012

    Hi Jaison,

    Yes, you will now need to define connectivity to your Oracle source and target and then create the models and interfaces needed. Use the Oracle specific Knowledge Modules: Doc -> http://docs.oracle.com/cd/E21764_01/integrate.1111/e12644/oracle_db.htm#Toc153688078.

    For Oracle you have various data movement options: JDBC, DBLINK, datapump.



  • Spanish_Guest Friday, January 13, 2012

    Hi Julien,

    Fisrt of all thank you so much Julien!

    Have done it and working now, by any chance have you gotteng any info or can redirect me to a doc where I can find ways to control with ODI:

    + How to check that in two tables the PKs are the same? For example: Column A and B in both table have gotten the same values. Can I make ODI to check this?

    Best regards.


  • Julien Friday, January 13, 2012


    The Incremental Update KMs will do that for you: they will match records based on the PK (Update Key to be more precise) and then do an update when necessary. The KM doc I sent out previously lists the KMs available for each technology.

    If you're more looking into checking referential integrity while moving the data then refer to the following Doc chapters:

    - Models -> http://docs.oracle.com/cd/E21764_01/integrate.1111/e12643/create_rev_model.htm#CHDCDCHD

    - Interfaces -> http://docs.oracle.com/cd/E21764_01/integrate.1111/e12643/interfaces.htm#CHDEHIDE (section '11.3.7 Set up Flow Control and Post-Integration Control')



  • L Monday, January 23, 2012

    Hi Julien,

    I have multiple excel files with multiple spreadsheets in them. The format of all the workbooks is same only the data differ. I have defined named ranges in each and every spreadsheet. When I first reverse engineered the models in ODI for one of the workbooks all the named ranges appeared as system tables and I could load the data into a target table. But when I wanted to load data of the second workbook (by renaming it as the first file), the ODI job is failing because ODI is unable to see the named ranges in the second file even though they are there. My job is programmed to do the following:

    1) Take an excel file from folder1 and rename it to the model filename (with which I reverse engineered the model).

    2) read data from it.

    3) Rename the file back to the original name,archive in a different folder (folder2) and delete it from the folder1.

    4) Repeat the steps until there is no file left to process in folder1.

    But my problem is ODI is not recognizing the named ranges in the second file even though they are exactly same as the first file.

    Kindly help me with this.

    Thanks in advance.


  • Julien Tuesday, January 24, 2012

    Hi L,

    I'd check first if ODI can see the named ranges for those files without renaming them to see if this works fine. I suspect there might be an issue there. I'd also double-check that the structure for your datastores does look the same for all the files, if there is a difference it could explain the issue.

    If everything works fine but it keeps failing when automating your process, I'd suggest to open up an SR and work with our Support team to fix your issue.



  • Jaison Monday, February 13, 2012

    Hi Julien,

    First of all, really thank you so much, for your help and guidance as they had been really useful to me.

    Once again I will have to bring some data from some excel file, which I have already done and "know" how to do and I would like to confirm with you something.

    What I shall do is the following:

    From one excel workbook file which has a lot of info just in one spread sheet feed some tables with the data coming from it.

    So what I have though is create mane name ranges (Define names on excel) as I need and work with them, but I will be facing with headers on the rows and columns. There is where I doubt if it will be possible. So far, I will not be able to modify the Excel’s file structure.

    Something more or less like this:

    Country1 Country 2

    Sales1 9999999 9999999

    Sales2 9999999 9999999

    Sales3 9999999 9999999

    Total123 9999999 9999999

    Sales4 9999999 9999999

    Sales5 9999999 9999999

    Sales6 9999999 9999999

    Total456 9999999 9999999

    Best regards.


  • Julien Monday, February 13, 2012

    Hi Jaison,

    You could define a name range which includes the Country1, Country2 headers as well as the empty header for the Sales1, Sales2, etc column. I didn't test this but ODI should give an arbitrary name to that column header and then when you integrate the data you can use only the column data you care about. I am assuming you don't care about the row header (Sales1, Sales2 etc.).

    I hope this helps.



  • Jaison Thursday, March 8, 2012

    Hi Julien

    I really thank you so much for all your help as it has been really useful to me.

    Right now I am facing an issue when uploading data from an excel file which I have already uploaded and modified the data type of the cells.

    By default when it comes from excel it comes in another format varchar and number, and I had manually changed it to varchar 250, as in some columns there will be numbers and letters.

    That is why I have changed the type and length of them to varchar 250.

    Is there a way to tell/order ODI to be always Varchar 250 when the reverse engineering is being done for the excel tables? Any recommendations or suggestions?

    Please do not hesitate to contact me if any further info is required.

    Best regards.


  • Julien Monday, March 12, 2012

    Hi Jaison,

    Unfortunately you cannot force ODI to always use varchar 250 for the standard reverse engineering, this will need to be done manually.



  • Jaison Friday, March 23, 2012

    Hi Julien,

    Thanx so much, for all your help, as it has been really usefull to me!

    I have been uploading data from Excel files successfully ‘till now.

    The files that I used to upload, were having the same “FileName.xls”. Now, what I do have is an excel file with different name, so I assume that as it was not created like that on the ODB it will not be uploaded, part of the name of the file is the same. I will let myself explain better.

    MYBEAUTIFULXLS.xls ---Before


    Could I use something like MYBEAUTIFULXLS% somewhere on ODI, to make him upload data from files that start with those characters?

    I do really appreciate your ideas!

    Best regards.


  • Julien Monday, March 26, 2012

    Hi Jaison,

    You could use an ODI variable for this, you will find more information about this in this post: https://blogs.oracle.com/dataintegration/entry/using_parameters_in_odi_the_dy_1.



  • Jaison Tuesday, April 10, 2012

    Hi Julien,

    Thank you so much for your help,

    In fact I had already reviewed those forums, but even though is not working for me. :-(

    So far what I have doen is the following: -by the way, I have posted this on the forums https://forums.oracle.com/forums/thread.jspa?threadID=2364172&tstart=0 -

    1. I have created a command procedure to list all the files into a *.lst file

    cmd /c dir C:\<...path...>\ <...FileName...> * .xls /b /a:-d > C:\<...path...>\FileNamelist_HR1.lst

    It is working, and it does create a *.lst file with all the files that start with the <...FileName...> * .xls

    2. I have created an interface to put all the name files on a temporaly table. The table is being populated correctly.

    3. I have created an ODI Variable that retrieves the name of the files, as the loop passes by.

    4. HERE is the issue:

    I have created an Interface, which is suppoused to be taking the variable but it aint!

    It is just taking one of the files

    Any ideas, I have checked and everything is suppoused to be ok, but it is not as its not bringing the correct data or better said being able to look on the other excel files.

    So, what I have done is the following: I t have removed the excel file that is passing ok and tried to excute another, it goes well until the interface as i get a window error message:

    +Cannot find data: See com.borland.dx.dataset.DataSetException error code: Base+62+

    View the logs at the bottom of the page

    5. I have created another Command Procedure to delete the CurrentFileName (Variable), and it is doing all right.

  • Julien Thursday, April 12, 2012

    Hi Jaison,

    This must be due to the fact that the Excel connectivity relies on the ODBC entry which refers to one of your xls files. Here is what I would do:

    - set up the ODBC connection to use a specific filename like file.xls

    - then continue using your table and variable but rename the files you get dynamically to file.xls one after the other d

    - run your interface

    - archive that file if you need to keep track of it



  • guest Saturday, June 23, 2012

    Very useful.

  • Jaison Gonzalez Friday, September 7, 2012

    Hi Julien,

    It has been a while since I last written! :-)

    I am trying to upload an Excel file and it is doing quite all right when I am protecting the sheets inside!

    I have faced some issues, as the users are not following our instructions (adding extra sheets, as they cannot modify the ones we have given them). So we have decided to secure the whole workbook. Here goes the BUT, but when I am trying to upload it, it does not work.

    I do get a “Load Data” Error on the ODBC –Description error message below-

    -5005 : S1000 : java.sql.SQLException: [Microsoft][ODBC Excel Driver] Could not decrypt file.

    java.sql.SQLException: [Microsoft][ODBC Excel Driver] Could not decrypt file.

    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)

    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)

    at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3074)

    at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)

    at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)

    at com.sunopsis.sql.SnpsConnection.v(SnpsConnection.java)

    at com.sunopsis.sql.SnpsConnection.c(SnpsConnection.java)

    at com.sunopsis.sql.i.run(i.java)

    Could I upload protected workbook to ODI? I do really appreciate your help and guidance.

    Please do not hesitate to contact me if any further information is required.

    Best regards.


  • Julien Friday, September 7, 2012

    Hi Jaison,

    This seems to be a limitation with the Excel ODBC driver and password protected files. You can find more information about it here: http://support.microsoft.com/kb/211378. I am not sure if the workarounds are acceptable in your case, please review.



  • Jaison Monday, September 10, 2012

    Hi Julien,

    Thank you so much for your help!

    Best regards.


  • guest Monday, October 15, 2012

    Dear all,

    at the moment, we'll try to determine whether we can use ODI to load data into our data warehouse (Oracle 11g) or not.

    Some of our source systems are Microsoft Access databases. ODI would be installed on a Red Hat Linux system. Does ODI bring its own ODBC drivers for MS Access or do we need to get/buy ODBC drivers to connect to Access from a Linux OS?

    Thank you very much.

    Best Regards


  • Julien Monday, October 15, 2012

    Hi Sven,

    ODI does not provide an ODBC driver for Access, we typically rely on the vendor's drivers. For MS Access on Linux, there is a driver available from easysoft (http://www.easysoft.com/products/data_access/odbc-access-driver/index.html).



  • Sven Tuesday, October 16, 2012

    Hi Julien,

    thank you so much. That helped me a lot.

    Best Regards


  • guest Monday, April 8, 2013

    Hi Julien,

    First of all, your posts are tremendously helpful. Thank you so much for your advice!

    We are currently experiencing a problem with Excel, ODBC, and ODI. We are trying to use an Excel data source to update an Oracle EPM application. This is with 32-bit Office 2007 on Windows Server 2008 R2 Datacenter 64-bit. We are able to create the physical and logical schemas and the test connection works fine. However, when trying to do a selective reverse in the model, we receive the error message: java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

    Is there a recommendation for how to get around this?

    Thank you!

  • Julien Tuesday, April 9, 2013


    The error message "The specified DSN contains an architecture mismatch between the Driver and Application" is not related to ODI itself but seems to be linked to an environment issue. I'd Google it, I found this link which might be useful: http://msdn.microsoft.com/en-us/library/windows/desktop/ms712362%28v=vs.85%29.aspx



  • guest Thursday, August 1, 2013

    After setting the server , When I am clicking Test Connection , I am getting this error . Could you please help me on this?

    "java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

    at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:133)

    at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.getConnection(LoginTimeoutDatasourceAdapter.java:62)

    at com.sunopsis.sql.SnpsConnection.testConnection(SnpsConnection.java:1125)

    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.getLocalConnect(SnpsDialogTestConnet.java:163)

    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.access$4(SnpsDialogTestConnet.java:159)

    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$4.doInBackground(SnpsDialogTestConnet.java:519)

    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$4.doInBackground(SnpsDialogTestConnet.java:1)

    at oracle.odi.ui.framework.AbsUIRunnableTask.run(AbsUIRunnableTask.java:258)

    at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:655)

    at java.lang.Thread.run(Thread.java:662)

    Caused by: java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

    at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:133)

    at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.getConnection(LoginTimeoutDatasourceAdapter.java:62)

    at oracle.odi.core.datasource.dwgobject.support.OnConnectOnDisconnectDataSourceAdapter.getConnection(OnConnectOnDisconnectDataSourceAdapter.java:74)

    at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter$ConnectionProcessor.run(LoginTimeoutDatasourceAdapter.java:217)

    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)

    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

    at java.util.concurrent.FutureTask.run(FutureTask.java:138)

    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

    ... 1 more

    Caused by: java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)

    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)

    at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3073)

    at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)

    at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)

    at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:410)

    at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:386)

    at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:353)

    at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:332)

    ... 7 more


  • Julien Thursday, August 1, 2013


    This appears to be an ODBC setup issue, you can find more information on Microsoft's website: http://msdn.microsoft.com/en-us/library/windows/desktop/ms712362%28v=vs.85%29.aspx



  • guest Wednesday, December 4, 2013


    I have done above mentioned steps, after adding data server when I am trying to do 'test connection', it is giving below error :


    at oracle.odi.runtime.agent.invocation.RemoteRuntimeAgentInvoker.invoke(RemoteRuntimeAgentInvoker.java:265)

    at oracle.odi.runtime.agent.invocation.RemoteRuntimeAgentInvoker.invokeTestDataServer(RemoteRuntimeAgentInvoker.java:781)

    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.remoteTestConn(SnpsDialogTestConnet.java:585)

    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.access$10(SnpsDialogTestConnet.java:581)

    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$5.doInBackground(SnpsDialogTestConnet.java:558)

    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$5.doInBackground(SnpsDialogTestConnet.java:1)

    at oracle.odi.ui.framework.AbsUIRunnableTask.run(AbsUIRunnableTask.java:258)

    at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:656)

    at java.lang.Thread.run(Thread.java:662)

    can you please help me?

  • Julien Wednesday, December 4, 2013


    This is a generic error message, I'd recommend to contact Support and provide more information about your architecture so they can help troubleshoot the problem.



  • Dinesh Monday, December 23, 2013


    Above mentioned was good example for loading data from excel to Oracle table in local machine

    But how can we do if the same excel file is in linux server ?

    Can you please explain us the step by step procedure to read the excel file from linux and load it in oracle db through ODI ?

    Many Thanks,


  • Julien Monday, December 23, 2013

    Hi Dinesh,

    On Linux you have a couple of options worth exploring with ODI:

    - Use an ODBC driver for Unix: http://www.unixodbc.org/

    - Use a JDBC driver for Excel: http://jexcelapi.sourceforge.net/ I've used JExcel a while back. It does convert the XLS content into CSV files behind the scenes which can be loaded into any target. There are other Excel JDBC driver options such as http://www.hxtt.com/excel.html.



  • guest Tuesday, January 20, 2015


    We are facing an urgent issue in one of our data loads as given below.

    We are getting below error when we try to connect to MS access databases through ODI 11g (

    oracle.odi.runtime.agent.invocation.InvocationException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    at oracle.odi.runtime.agent.invocation.RemoteRuntimeAgentInvoker.invoke(RemoteRuntimeAgentInvoker.java:265)

    at oracle.odi.runtime.agent.invocation.RemoteRuntimeAgentInvoker.invokeTestDataServer(RemoteRuntimeAgentInvoker.java:780)

    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.remoteTestConn(SnpsDialogTestConnet.java:584)

    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.access$10(SnpsDialogTestConnet.java:581)

    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$5.doInBackground(SnpsDialogTestConnet.java:558)

    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$5.doInBackground(SnpsDialogTestConnet.java:1)

    at oracle.odi.ui.framework.AbsUIRunnableTask.run(AbsUIRunnableTask.java:258)

    at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:655)

    at java.lang.Thread.run(Thread.java:662)

    The error is signalled when we test the connection with the standalone agent through topology. for local ( no agent ) we do not get this error.

    We checked the JAVA version used in ODIParams.bat and changed it to 32-bit java and we have also created the DSNs usong 32Bit ODBC drviers.

    Kindly guide us on the same ASAP.

    Environment Details:

    OS version: Windows 2003 64-bit

    ODI Version: Odi 11g (

    Java version: JDK_1.6.43 (32-bit)

  • David Tuesday, January 20, 2015

    Which user are you running the standalone agent under? Which drivers?

    Worth checking each item in this checklist within your setup to see if it is applicable and potential cause....




  • guest Friday, October 9, 2015

    Hi Author,

    Thanks for the post. Can you please give the process for Linux too.

    I am using ODI studio client on windows connected to Linux Server (ODI Server + Oracle Database ).

    Now I need to get the Excel Data which is residing on linux machine.

    Can you help me in creating a DSN on linux and linking the EXCEL path to it as we do on windows.

    I am having WinSCP, Putty to view the files on linux machine.

    I have succesfully got the data from EXCEL on windows following the ODBC connections. but unable to get from linux machine.

    I am using ODI on client and same on Linux Server.

    Please help me in doing this.

  • Julien Monday, October 12, 2015


    The out of the box connectivity requires Windows, it cannot be used with Linux. You will need to either copy the XLS(X) files to a Windows machine and set up the DSN there or find a ODBD or JDBC driver that can work with Excel files on Linux.



  • guest Monday, October 19, 2015

    Hi Julien,

    Thanks for the comment.

    I have installed unixODBC driver in my linux machine. but I was not at all aware of, how to use it.

    Can you please share any documentation on it.?

    setting up connection with excel and populating data on odi studio.

    Thanks in advance,


  • Julien Tuesday, October 20, 2015

    Hi Santosh,

    I'd look into the manuals for unixODBC on their website, if this doesn't help try to ask on our ODI forum: https://community.oracle.com/community/business_intelligence/system_management_and_integration/data_integrator

    Someone may have used it and may be able to help.



  • guest Tuesday, December 22, 2015


    I updated to the latest version of ODI (12.2.1). This also required that I upgrade the jdk. I am on jdk1.8.0_66. After doing this I learned that the java-odbc bridge is no longer supported. What needs to be installed now to be able to source directly from Excel workbooks on 64-bit Windows?

    I am looking for help with this.

  • Julien Tuesday, January 5, 2016


    Starting with ODI 12.2.1 and since JDK 8 deprecated the Sun JDBC-ODBC bridge you will need to use a 3rd party adapter to connect to Excel. Vendors such as OpenLink Software (openlinksw.com) or Progress DataDirect provide such adapters.



  • guest Tuesday, January 5, 2016

    Here is what I tested:

    Downloaded from Progress:

    SequeLink Server for ODBC Socket

    Sequelink Client for JDBC

    Installed both.

    In Sequelink Manager the following string value needs to include "DSN=":

    Attribute: DataSourceSOCODBCConnStr

    Value: DSN=<data source name in Windows ODBC manager>

    Tested using Sequelink's testforjdbc14.bat. Connection Established.

    In ODI ... on Windows, copied to C:\Users\<username>\AppData\Roaming\odi\oracledi\userlib:

    iaik_jce_full.jar, sljc.jar, slssl14.jar

    Q: Not sure which are required? I copied all.

    In ODI Studio:

    Q: For Excel Technology, under Data Server > JDBC > JDBC Driver, did not find an option for the Sequelink driver? Shouldn't something show up?

    Googled and tested in ODI Studio with:

    JDBC Driver: com.ddtek.jdbc.sequelink.SequeLinkDriver

    JDBC URL: jdbc:sequelink://<myservername>:19996

    Q: How to distinguish different ODBC data sources? (i.e. the distinct data sources setup in Windows ODBC Manager).

    A: Looks that it should be "ServerDataSource=", i.e.: jdbc:sequelink://<myservername>:19996;serverDataSource=<mysequelinkdsnname>

    I created a test model and was able to reverse engineer a workbook source, with individual worksheets going into corresponding tables.

    Related Excel question - does anyone how recommendations for implementing a VBA solution to automatically update ranges in Excel Name Manager for named ranges?

    Hopefully someone else will contribute to this update. Thanks in advance!

  • Julien Tuesday, January 5, 2016

    Thanks for contributing and glad to see you got it working! SequeLink shouldn't show up in the Topology, you need to enter this information manually as you did.

  • guest Thursday, January 21, 2016

    Regarding automatic range extension - this can be automated for records added by adding a dollar sign at the end of the Resource Name (Models -> <datastore> -> open properties -> Definition - > Resource Name). For example "SOMETABLE$".

    Also need to add to my earlier post, that for a collocated agent, on the server where it is running follow one of the 2 options below to add the Data Direct driver jar to the agent's classpath:

    Add the jar to <DOMAIN_HOME>/lib directory. Restart the Agent process.

    Add the location of the jar to the environment variable ODI_ADDITIONAL_CLASSPATH . Restart the agent process.

  • guest Sunday, April 3, 2016

    where can i download this sun.jdbc.odbc.JdbcOdbc Driver. help!

  • Julien Monday, April 4, 2016


    The Sun JDBC-ODBC bridge is included in all JDKs until JDK 7. It was deprecated in JDK 8 (needed for ODI 12.2.1 and higher) and a 3rd party JDBC driver is now required to use ODBC datasources in ODI.



  • Sourabh Kurane Wednesday, November 2, 2016

    Hi Julien,

    You had mentioned Doc ID 424442.1 in one of your post.

    I'm unable to locate this anywhere.

    Could you point me to the exact URL?



  • Julien Wednesday, November 2, 2016

    Hi Sourabh,

    Doc 424442.1 is still available on My Oracle Support, please log into My Oracle Support then do a search with the ID and you will find it.



Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.