Microsoft Excel as a Source and Target as Oracle in ODI 188.8.131.52:
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 184.108.40.206, 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.
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.
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.
All looks good. Now execute the interface and check the status in operator.
Hmm. “Invalid fetch size” error seems to be a known issue to me. Actually this is a bug in ODI 220.127.116.11. Oracle has provided a patch to resolve this ( patch number 13528165 ). The same error also appears in ODI 18.104.22.168 as well, not sure why. As per Oracle support team this error should have been resolved in 22.214.171.124 but as per my testing its the same problem. When I talked with them, they suggested to go with below approach.
That’s all for today. See you on my next post.