Page 1 of 1
access to oracle
Posted: Wed Dec 10, 2008 9:03 am
by knowledge
Hi ,
My source is MS access but I can not load using ODBC as datastage is installed on unix and access is windows ..
We do not have drivers for ODBC ..
What is the other way I can load MS access to Oracle.
Can we do it on Database (MS access - Oracle) level without using Datastage ?
I found one tool which converts Access to oracle(online) but it is one time load and converts from local copy of database ( on my desktop)
I want to conect to remote Ms access database ,extract data and load into staging area in oracle tables .
Please guide ....
Thanks.
Posted: Wed Dec 10, 2008 9:06 am
by chulett
You can do separate steps - one to dump the Access data to files and transfer them to UNIX, then a DataStage job to load to Oracle. An Enterprise Scheduler can easily tie them together. Or purchase ODBC drivers and do everything from DataStage.
Posted: Wed Dec 10, 2008 9:24 am
by knowledge
Thanks for quick replay,
How can i dump the Ms access into files ...i have read priveleges to MS access database ( on remote server) ..there is no funding to buy ODBC drive ...have to find some round about way to load data on regular basis ...
Posted: Wed Dec 10, 2008 9:33 am
by chulett
There must be some "Access People" there you can work with to accomplish that. Me, I have no clue and don't really want one.
Posted: Wed Dec 10, 2008 9:39 am
by knowledge
Thanks ,
I will look into it ...
where can i find more info on odbc and its requirement...?
Posted: Wed Dec 10, 2008 9:57 am
by chulett
For DataStage? The pdfs in your PC's "Docs" directory. Otherwise, the vendor:
http://www.datadirect.com/index.ssp would be a good place, I would think.
Posted: Wed Dec 10, 2008 10:07 am
by knowledge
Thanks .
Posted: Wed Dec 10, 2008 3:41 pm
by vinnz
You could check out Oracle Heterogeneous Services which is supposed to enable access to a non-oracle database using oracle - this would require some DBA work. Hope that helps.
Posted: Fri Dec 12, 2008 1:07 pm
by knowledge
Not sure what it is ...we r still looking for other options, i will try to get more information on it ,
Thanks .
Posted: Fri Dec 12, 2008 1:40 pm
by dslisa
Knowledge, don't know if this will help you. Can you convert your Access Database into a .csv file and then read it in Datastage as a sequential file? Once you are able to read it you should be able to do what ever you need to do with it. Once the .csv is created FTP it from windows to your UNIX source directory before doing anything else. Don't know how big your Access database is and how frequently it is updated? You would manually need to do the .csv conversion process or write a Macro to do it.
Posted: Fri Dec 12, 2008 3:32 pm
by knowledge
hi lisa ,
Thanks , That is one of the option we r planning to implement ,
But again many factors r involved ...
Posted: Fri Dec 12, 2008 7:17 pm
by ray.wurlod
The present tense plural form of the verb "to be" is spelled "are", not "r".
DSXchange is not a mobile telephone, and we expect users to employ professional standards of written English, not least because it is a difficult enough language for those whose first language is not English without resorting to SMS-style abbreviations and other pretensions.
Posted: Fri Dec 12, 2008 9:42 pm
by knowledge
ok .