Microsoft Excel as a Source and Target as Oracle in ODI 11.1.1.6

Microsoft Excel as a Source and Target as Oracle in ODI 11.1.1.6:

Today we are going to load data from excel file to oracle table. I have got so many mails from ODI developers requesting to write on this as they are facing couple of issues.

Note here, in case of Windows server 2008, 64 bit,  the 64 bit excel/access odbc driver will not be available for you. You can only see the default odbc driver location as “C:\WINDOWS\SysWOW64\odbcad32.exe” . But the problem is, it is not compatible with 64 bit ODI.

In this case you are probably going to see below error.
java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
To resolve above  issue, you have to use 64 bit excel/access driver. Download  link.

Since I am using windows 7, 32 bit and with ODI 11.1.1.6, there should not be any problem.

First open the excel file and put some data. Then select all rows and columns and go to formula tab. Click on Define name.
EXCELTAB1
EXCELTAB1_1

Now click ok and go to Control Panel\System and Security\Administrative Tools. Double click on Data Source(ODBC) and proceed as  per below screenshots.
EXCELTAB2

We have now created the data source successfully. Next open ODI Studio and go to topology. Create a new dataserver under excel technology and subsequently create physical schema and logical schema for it.
EXCELTAB4
EXCELTAB5

Create a new project.
EXCELTAB6

Go to Model and reverse the the excel file.
EXCELTAB7

Now create an interface ( Right click and new interface). Drag the source data store and target datastore. Select appropriate KMs and execute the interface. If you have not imported then right click on Knowledge Modules and click on import. Here you can select CKM Oracle, LKM Sql to Sql and IKM Sql Control Append.
EXCELTAB8
EXCELTAB9
EXCELTAB10
EXCELTAB11
EXCELTAB12

All looks good. Now execute the interface and check the status in operator.

EXCELTAB13

 

Hmm. “Invalid fetch size” error seems to be a known issue to me. Actually this is a bug in ODI 11.1.1.5.  Oracle has provided a patch to resolve this ( patch number 13528165 ). The same error also appears in ODI 11.1.1.6 as well, not sure why. As per Oracle support team this error should have been resolved in 11.1.1.6 but as per my testing its the same problem. When I talked with them, they suggested to go with below approach.

 

Go to the excel data server and change the Array fetch Size and Batch update Size to 0 (zero). The default value was 30. Save it and execute the interface again.
EXCELTAB14
EXCELTAB15

As you can see its running fine.
EXCELTAB16

Lets verify the data on target table. Yes, Its there 🙂
EXCELTAB17

That’s all for today. See you on my next post.

About Bhabani 86 Articles
Bhabani has 10 plus years of experience in Data warehousing and Analytics projects that has span across multiple domains like Travel, Banking and Financial, Betting and Gaming Industries. Solution areas he focuses on designing the data warehouse and integrating it with cloud platforms like AWS or GCP. He is also a Elite level contributor at OTN forum more than 9 years. He loves to do experiment and POC on different integration tools and services. Some of his favorite skills are Redshift, Big Query, Python, Apache Airflow, Kafka, HDFS, Map Reduce ,HIVE, Habse, Sqoop, Drill, Impala.

89 Comments

    • Hi Bhabani,

      could you please explain the step by step xls or xlsx file generation from oracle table ASAP…(Table is source and xls file is target). we have an urgent requirement

      Thanks,
      Balakrishna

      • Hi Bhabani,
        could you please explain the step by step xls or xlsx file generation from oracle table in ODI linux server not in windows ASAP…(Table is source and xls file is target). we have an urgent requirement
        Thanks,
        Balakrishna

  1. It was a life saver bhabani. I dont have oracle support account. So there was no chance to download any patch. I decided to install latest version. But i got the same error in ODI 11.6. It made me really sad. But one of my friend redirected me to your blog. Above trick helped a lot.Great work.

  2. Hi,

    I am not able to see my Excel file in the selective reverse engineering tab,
    can you tell me the reason

    Thanks

    • Hi Yogesh,
      The reason might with the datasource. Please delete the data source and recreate again. It will be better if you click on configure instead of adding a new one as given in my initial screenshot.

      Also check the connection status in dataserver for successful or failure.
      I am considering that you have selected all column and have given the name in formula tab.

  3. Hi Bhabani,

    I am facing one problem, at flow tab in the interface when I am trying to select IKM in the IKM selector as “IKM SQL To SQL Control Append”. It is not giving me any option in the list box.

    • May be you have selected staging is different than target. If this is the situation then you need multiconnection IKM. Open the IKM check the multiconnection option. Now close interface and open again. You should see it in the drop down box.

      • java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
        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$3.doInBackground(SnpsDialogTestConnet.java:247)
        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] Data source name not found and no default driver specified
        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] Data source name not found and no default driver specified
        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

  4. It is an excellent article it helped to complete exercise with Excel. I just started learning ODI. Please keep posting as much as you can.

  5. Hi Bhabani,

    I am facing some error while implementing this in my system. Could you please help me to sort out this issue?. the error says

    ODI-1217: Session EMP_EXCEL_LOAD (118025) fails with return code 8000.
    ODI-1226: Step EMP_EXCEL_LOAD fails after 1 attempt(s).
    ODI-1240: Flow EMP_EXCEL_LOAD fails while performing a Loading operation. This flow loads target table EMP_DATA.
    Caused By: java.sql.SQLException: ResultSet is closed
    at sun.jdbc.odbc.JdbcOdbcResultSet.checkOpen(JdbcOdbcResultSet.java:6646)
    at sun.jdbc.odbc.JdbcOdbcResultSet.clearWarnings(JdbcOdbcResultSet.java:1765)
    at sun.jdbc.odbc.JdbcOdbcResultSet.close(JdbcOdbcResultSet.java:1468)
    at oracle.odi.runtime.agent.execution.sql.concurrent.FastJDBCRecordSet.close(FastJDBCRecordSet.java:124)
    at oracle.odi.runtime.agent.execution.DataMovementTaskExecutionHandler.handleTask(DataMovementTaskExecutionHandler.java:113)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2906)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2609)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:540)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:453)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1740)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:338)
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:214)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:272)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:263)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:822)
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:123)
    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:83)
    at java.lang.Thread.run(Thread.java:662)
    Thanks in advance!

    -Rakesh

    • Thats a bug in ODI. If you have oracle support account then take a look in document id : 1366658.1

      What is your odi version ? I guess it is fixed in ODI 11.1.1.6

  6. Hi Bhabani,

    That is really useful.I had followed the same steps as per the screenshots but I face a problem during reverse engineering,
    I have created a model with the microsoft Excel technology and mapped it to the right logical schema.
    The file lists in the Selective Rev Engineering, but throws a warning that “You cannot reverse engineer a file type model.Use the reverse engineering function on the Column tab of the datastore”.
    I tried that too but when i click on the search to add the resource name it throws an error “The Directory specified in your schema does not exist”.
    Would appreciate help.
    Thanks in advance!!

    -Hema

    • You have to select the technology as Microsoft excel in the technology option of your model. I believe you have selected the technology as file. Please let me know if i am correct or not to take the futher course of action.
      Thanks

      • Hi Bhabani,

        I have selected the Microsoft Excel as the technology, and my file also lists correctly in the Selective reverse engineering tab.But i am just not able to reverse engineer as it throws an error as I mentioned in my previous post .

        • Hi Hema, If your excel file is not too confidential then can you please send it to bhabaniranjan@gmail.com and support@dwteam.in ?

          I am considering that you have tested below points you are successful on this.
          1. You have defined the correct link between DSN and your Excel File .
          2. You have test the Connection in Topology .
          3. Excel file has an Extension of XLS .
          4. When reversing I believe you have selected the proper logical schema.

          Also let me know if we can have a teamviewer on this.

  7. Hi Bhabani,

    can you please tell me how to create xml dataserver. I tried but throws an error. I think the problem is in jdbc url can u please guide me

    Thanks&Regards,
    Arjun

    • Plz use below jdbc driver and jdbc url

      com.sunopsis.jdbc.driver.xml.SnpsXmlDriver
      jdbc:snps:xml?f=D:\Oracle\Middleware\Oracle_ODI1\oracledi\demo\GEO_DIM.xml

      • Thank you for replay sir,
        I mention Jdbc driver and jdbc url like this
        com.sunopsis.jdbc.driver.xml.SnpsXmlDriver
        jdbc:snps:xml?f=C:\ODI_PROJECT_FILES\PROMO_SYSTEM\PROMOTIONS.xml
        When i test the connection it is successful.
        when i reverse engineer the model there is no tables in that model.
        can u please guide me………..

        Thanks&Regards,
        Arjun

        • Try doing selective reverse and check if you can see any tables are there or not. If your xml file is not that much confidential you can send it to me. This will be easier for me to track your issue.

  8. Just set the fetch size in the DataServer to ZERO.
    it will work…. same when u want to work on Access Database.

    • Thanks for your point Mohammad. Normally this solution is used whenever people face some kind of error related to array invalid fetch size etc. If it works for other issues as well then thats great 🙂

  9. I want to thank you, but I have one more question!

    How can I load thу second excel files without deleting the first one?

    • Hi Ruslan,

      You might have to create 4 folder to achieve multiple excel file processing in one interface and in one package. In folder1 keep all excel files. Using ODI move the 1st file to folder2 with a generic name ( for example EXCEL_FILE). Based on this generic file you have to create the odbc/datasource. Once the file will be processed by odi move this file from folder2 to done directory by appending timestamp to the end of filename ( ex: EXCEL_FILE_11_03_AM) or to error directory. Next move the second file from 1st folder to second folder with same name (EXCEL_FILE) and repeat the same until you move all files from 1st folder to done or error folder.

  10. Hi Bhabani ,

    I have a requirement to load excel file into ODI in Unix,
    and it should be an automated process.

    How can we achive this

    • I have two possible ways.
      1. You might to purchase some jdbc drivers to process excel files in unix.
      2. Most feasible one, create an agent on the windows box which one normally sending the excel files.Thats all, execute the package using this agent on unix.

  11. Hi Bhabani
    Thanks for the post it is very helpful.
    Unfortunately. Once I applied the workaround Array fetch Size and Batch update Size to 0

    It worked perfectly buy the last few days I am getting the same error Invalid Fetch Size

    Any advice?

    Thanks

  12. Hello I’m under ODI 11.1.1.6 and windows server 2008 R2.
    This solution is working with a local, but with an agent in the same computer I still have the problem. Do you think that configuring opmn.xml file will resolve this according to oracle doc ID 1581026.1

  13. Hi Bhabani,

    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,
    Dinesh

    • Excel is a Microsoft product. So its too difficult to read in Linux env unless you have specific jdbc driver which can read excel files. There are many way around but not sure which one fits to you. You can create an agent in widows server and process files present in Linux. The only thing you need to make sure that the path is available to windows agent. Pretty simple 🙂

      Next approach could be the excel jdbc driver created by Dev. You can get it from odiexperts.com. I think it is now free for everyone but you have to consult his team to know more on the support terms and condition. There also some other vendors who are proving excel jdbc drivers.

      Hope it helps.
      Bhabani

  14. thanks you , i have one question:Invalid string or buffer length in ODI: i dont know whether ODI JDK and ODBC is 64bit cause it?

  15. Iam using ODI version 11.1.1.7. Iam facing an issue with Excel Files in Windows 7 64 Bit OS. Firstly I was not able to create new data source as no data sources were visible on Add Options.So to create new data source ,I used odbcad32.exe from following path C:\Windows\SysWOW64 .Able to create the same but getting following error during reverse engineering .
    java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application.

    The link which you have mentioned in your article is not working. Can you please mention Step by Step how to work with windows 7(64 bit) and ODI 11.1.1.7.

    Will really appreciate that .

    • Not sure why the link is not working for you. You can search in Google and download the driver from Microsoft website. That is the only solution I have right now. Even after Google search if you are not getting then let me know I will send to your email id.

      Thanks
      Bhabani

  16. Hi Bhabani,

    I am able to create ODBC DS for excel in my windows environment, created interface and evetying is working fine.

    Could you please tell me how do I create “ODBC data source for excel on UNIX systems” ??

    Thanks in advance.

    • You have two option.
      1. You can use some 3rd party jdbc driver those providing to process excel files in unix env. Odiexperts.COM has given one free driver for processing excel and ms access files.

      2. If you dont trust those 3rd party drivers then oracle suggest to create one agent (agent only installation) in windows system. That agent will process those MS files.

      Hope it make sense.

  17. Hi Bhabani,

    Thanks for your inputs :).

    Can you please tell me, how can I use group by clause in ODI ?

    In my requirment, input from 2 table and I have to generate the out put in one table
    My sql querie is
    select store, item, sum(quantity) from store group by store, item;
    I have tried giving sum()function on my target column but it doesn’t work.

    How can I implement this group by clause in ODI ?

      • I am using ODI_11.1.1.6.0_GENERIC_111219.1055.
        I tried with sum()but my output is wrong. Its loading all the input records to output.
        For example If I hava a table as mentioned below
        Input Table
        date store item qty
        d1 store1 Item1 1
        d2 store2 Item2 1
        d3 store3 Item3 1
        d4 store4 Item4 1
        d5 store1 Item1 3
        d6 store2 Item2 2
        d7 store3 Item3 5
        d8 store4 Item4 4



        If I used sum() in ODI mapping its loading all input table records in my output table.
        How can I get my output as
        week store item sum(quy)
        w1 store1 item1 4
        w2 store2 item2 3
        w3 store3 item3 6
        w4 store4 item4 5

        how can I implement is logic in ODI ?
        Your inputs will be highly appreciated and thanks in advance

        • Its very simple Khaja. I dont know why ODI is not putting group by clause. I just replicated a map in ODI and its putting group by for rest of the column.
          Are you sure you are not using a customized KM? I checked with IKM Oracle Incremental Update and its working fine. Can you invite me for a team viewer ?

          • Thanks Bhabani,

            I got the expected output. In ODI, sum() function and IKM Oracle Incremental Update resolved my problem.

  18. Hi Bhabani,

    Can you please tell me how to use RFI(Retail financial Integration) for EBs TO RMS integration using ODI.

    If you can provide any documents or any link post also highly appreciated.

    Thanks
    khaja

  19. Hi Bhabani,
    I need one simple and easy answer from you.
    Can we load data into the target database without creating the target tables first.?
    I mean direct copy of data from source to staging and then to target.
    Thanks in Advance.
    charan

    • You can do this but you need atleast a table to be reversed to get the metadata. Then you can drop this table. Inside ODI KM, there is option to create target table on the fly.

      BTW why do you choose this approach?

  20. Hi Bhabani,
    I am getting below error while executing the .xls file to oracle table. Can you please suggest how to resolved this issue.
    [Microsoft][ODBC Driver Manager] Invalid string or buffer length.

  21. Hi Bhabani,
    I’m using ODI 12.1.2.0.0
    I created excel data server and working successfully.
    But I’m getting following error when I executing mapping work-flow.

    ODI-1227: Task LKM SQL to SQL (Built-In) (Load DEFAULT_AP) fails on the source MICROSOFT_EXCEL connection ALM_EXCEL_SRC.
    Caused By: java.sql.SQLException: [Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression ‘HHI_DATA.YEAR YEAR’.
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6964)
    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7121)
    at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3117)
    at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:337)
    at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:252)
    at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:197)
    at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:205)
    at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:100)
    at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:38)
    at oracle.odi.runtime.agent.execution.DataMovementTaskExecutionHandler.handleTask(DataMovementTaskExecutionHandler.java:73)
    at oracle.odi.runtime.agent.execution.SessionTask.processTask(SessionTask.java:214)
    at oracle.odi.runtime.agent.execution.SessionTask.doExecuteTask(SessionTask.java:135)

    Please guide,
    Regards
    Kusal

  22. Hello, I have an ODI interface using Excel as source. It works if I execute with “Local (No Agent)”. But if I execute with a logical agent I get the error “Invalid string or buffer length”. What would cause this? I am using ODI 11.1.1.6.5
    Thanks

  23. Hello, Thanks for the quick response. In the other article you say — ” it shouldn’t be an issue if you use 32bit jdk for the agent. Just do one sanity check whether you are executing by using the agent you configured with 32 bit driver or not. ”
    This may be my error because when I execute with no agent it works, but when I use an agent it fails. How do I check to see if the agent is configured with 32 bit driver?
    Thanks,
    Michael

    • You can verify using below command.
      C:\>java -version
      java version “1.6.0_25”
      Java(TM) SE Runtime Environment (build 1.6.0_25-b06)
      Java HotSpot(TM) 64-Bit Server VM (build 20.0-b11, mixed mode)

      while in case of 32 bit JVM it will print
      C:\> java -version
      java version “1.6.0_26”
      Java(TM) SE Runtime Environment (build 1.6.0_26-b03)
      Java HotSpot(TM) Client VM (build 20.1-b02, mixed mode, sharing)

      Once you figured it out, check the installation location. Copy that path up to JDK home and put this in ODIPARAMS.bat/.sh file.
      Also create the DNS as I have mentioned in the article. Let me know if you face any issues.

  24. Hi Bhabani,

    We are trying to load data from MS Excel file to oracle.We are using standalone linux 11.1.1.7.0 agent.When i am trying test MS excel data store connection agent goes down.
    Can you please help on this.
    Want to konw why this is happening?
    But when i am trying to same task with local(No agent)agent it is working fine.

    • What do you mean by agent goes down? Do you mean though the agent is up, while testing it is saying test connection failed?
      Can you give the error stack of your agent while testing?

  25. Hell Bhabani,

    When i am testing MS Excel data store connection, then agent goes down.Before checking the data store agent is up,but if you testing the connection then it will goes down.
    Below is the error stack:

    oracle.odi.runtime.agent.invocation.InvocationException: http://LDBVDTX001.erpcc.lafarge.com:20940/oraclediagent:org.apache.commons.httpclient.NoHttpResponseException: The server LDBVDTX001.erpcc.lafarge.com failed to respond
    at oracle.odi.runtime.agent.invocation.RemoteRuntimeAgentInvoker.invoke(RemoteRuntimeAgentInvoker.java:295)
    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)
    Caused by: org.apache.commons.httpclient.NoHttpResponseException: The server LDBVDTX001.erpcc.lafarge.com failed to respond
    at org.apache.commons.httpclient.HttpMethodBase.readStatusLine(HttpMethodBase.java:1976)
    at org.apache.commons.httpclient.HttpMethodBase.readResponse(HttpMethodBase.java:1735)
    at org.apache.commons.httpclient.HttpMethodBase.execute(HttpMethodBase.java:1098)
    at org.apache.commons.httpclient.HttpMethodDirector.executeWithRetry(HttpMethodDirector.java:398)
    at org.apache.commons.httpclient.HttpMethodDirector.executeMethod(HttpMethodDirector.java:171)
    at org.apache.commons.httpclient.HttpClient.executeMethod(HttpClient.java:397)
    at org.apache.commons.httpclient.HttpClient.executeMethod(HttpClient.java:323)
    at oracle.odi.runtime.agent.invocation.RemoteRuntimeAgentInvoker.invoke(RemoteRuntimeAgentInvoker.java:234)
    … 8 more

    • Very strange issue. Agent should not get down just by testing datastore. What it seems to me you might have some datastores that is pointing to samba share (points to windows shared folder) to see the excel file. Somehow ODI is not able to connect to agent. But if you are testing excel file then make sure agent is running on windows machine not linux. Ping me to discuss on this.Thanks

      Update as per discussion: Architecture issue. This is now fixed with jdbc excel

      • Hi ,

        Can you please suggest the solution as I am facing the same issue in Excel . Stanadrad agent is gettign down while runninng the Excel jobs ,

        Regards,
        Venu

  26. Hi Bhabani,

    Thanks for the post.

    If it is non-windows environment(linux) – then can you please let us know,how to load excel spread sheet in ODI 12.3 ?

  27. Hi Bhabani,
    Based on your document I did intial load for excel to table.while trying to do for incremental load am facing issue.Please help in this regards

    • Yes it is. Select Staging to a RDBMS/Sunopsis Memory Engine and go with normal mapping.
      Loading:LKM SQL to SQL
      Integration:IKM SQL Control Append
      You need one data server for xml and other one for excel

  28. I’m getting below error and need a solution to it.

    any help is appreciated

    ODI-1227: Task SrcSet0 (Loading) fails on the source MICROSOFT_EXCEL connection MyEAIESBexcel.
    Caused By: java.sql.SQLException: [Microsoft][ODBC Excel Driver] External table is not in the expected format.
    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 org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:113)
    at com.sunopsis.sql.SnpsConnection.createConnection(SnpsConnection.java:390)
    at com.sunopsis.sql.SnpsConnection.connect(SnpsConnection.java:348)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2882)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
    at java.lang.Thread.run(Thread.java:722)
    Caused by: java.sql.SQLException: [Microsoft][ODBC Excel Driver] External table is not in the expected format.
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6956)
    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7113)
    at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3072)
    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)
    at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter$ConnectionProcessor.run(LoginTimeoutDatasourceAdapter.java:217)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
    at java.util.concurrent.FutureTask.run(FutureTask.java:166)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    … 1 more

  29. hi Bhabani,

    I’m trying to load data from Oracle to Excel in ODI 12c. Can you show me the details? I have separated staging area and target with source and staging being the same logical schema. But still I’m getting error..If I use LKM SQL to SQL I’m getting error like C$_0EMP is not a valid name. If I omit LKM and use IKM SQL TO SQL Append, then I’m getting as “General Error” Kindly help.

  30. Hi Bhabani,

    Is there any possibility to load data from multiple tabs in one excel file to different target tables(tables structure also different).
    Please help on this.

    Thanks,
    Vasu.

  31. Hi Bhabani,

    We are getting below error while testing excel data server in topology in odi12c.please help on this.

    java.sql.SQLException: [Microsoft][ODBC Excel Driver] System resource exceeded.
    at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:144)
    at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.getConnection(LoginTimeoutDatasourceAdapter.java:73)
    at com.sunopsis.sql.SnpsConnection.testConnection(SnpsConnection.java:1243)
    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.getLocalConnect(SnpsDialogTestConnet.java:173)
    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.access$400(SnpsDialogTestConnet.java:51)
    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$5.doInBackground(SnpsDialogTestConnet.java:629)
    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$5.doInBackground(SnpsDialogTestConnet.java:625)
    at oracle.odi.ui.framework.AbsUIRunnableTask.run(AbsUIRunnableTask.java:258)
    at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:947)
    at java.lang.Thread.run(Thread.java:724)
    Caused by: java.sql.SQLException: [Microsoft][ODBC Excel Driver] System resource exceeded.
    at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:144)
    at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.getConnection(LoginTimeoutDatasourceAdapter.java:73)
    at oracle.odi.core.datasource.dwgobject.support.OnConnectOnDisconnectDataSourceAdapter.getConnection(OnConnectOnDisconnectDataSourceAdapter.java:87)
    at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter$ConnectionProcessor.run(LoginTimeoutDatasourceAdapter.java:228)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    … 1 more
    Caused by: java.sql.SQLException: [Microsoft][ODBC Excel Driver] System resource exceeded.
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6964)
    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7121)
    at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3080)
    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:412)
    at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:385)
    at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:352)
    at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:331)
    … 6 more

    Thanks,
    Vasu

  32. Hi Bhabani,

    Is it possible to connect MA Access database as a source or target in ODI 12c ? I am unable to connect as JDK 1.8 + does not support ODBC-JDBC bridge.
    Is there anything else which can be done to connect ODI with MS Access

    Many Thanks
    Ashutosh

  33. Hi Bhabani ,

    can you please suggest below in ODI 11g .

    while doing reverse engineering in ODI 11g Model with using Microsoft Excel technology the tool is automatically getting shutdown , but when I use the same file in Production the Excel is successfully getting reverse engineer and in the current environment the Excel dataserver is successful and I have used Array Fetch & Batch Fetch size as “0” .

    Note: In Dev Model , check Model Option showing as disabled as well .

    Regards,
    Venu

Comments are closed.