February 28, 2014 at 11:11 am
Hi All,
I hope someone can help me or at least has had a similar experience that I can learn from.
I have been supplied some details to get access to a Oracle 11 database view. I initially tried to use the import wizard but it failed on the MS OleDB provider stating that it was not available, so I downloaded the Oracle 11 client ODAC software and installed it but I still had the same issue with both the MS oledb driver and the Oracle OLEDb driver. I tried using SSDT in Visual studio 2012 to see if the different controls would work but still the same.
Strangely, I could connect when setting up a system DSN connection but that was as far as I could get.
Has anyone successfully connected to an Oracle Scheme view from within SQL?
My requirements are:
1/ Connect to view via either Linked Server or SSIS
2/ Download a dataset on a daily basis into a SQL staging DB
Thanks in advance
E.
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
February 28, 2014 at 11:28 am
Yes, you can use either way. Use Linked Server (LS) for short and less complex queries. SSIS is recommended as you can do much more using it than a LS.
--
SQLBuddy
March 1, 2014 at 3:36 am
Many thanks for your response, but I know that it should be possible; My issue is that they are not working and I wanted to know from someone who has got it working and could advise.
If you have any experience in this please let me know.
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
March 1, 2014 at 4:45 pm
Yeah, we have implemented them. Use option 1.
What's the error or issue that you are having ..
--
SQLBuddy
March 2, 2014 at 5:14 am
I cannot get the oledb providers to work, neither the MS one or the oracle one, from within the import data function. Could you let me know the steps you performed and which specific software from Oracle, client software, just drivers, or if any you downloaded to get it to work?
I have managed to test a connection successfully via system DSN, but I can't get a connection to work from the SQL import data (Tasks>Import Data>Choose Oracle provider,....)
Any help will be appreciated
Thanks
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
March 2, 2014 at 2:04 pm
Ness (3/2/2014)
I cannot get the oledb providers to work, neither the MS one or the oracle one, from within the import data function. Could you let me know the steps you performed and which specific software from Oracle, client software, just drivers, or if any you downloaded to get it to work?I have managed to test a connection successfully via system DSN, but I can't get a connection to work from the SQL import data (Tasks>Import Data>Choose Oracle provider,....)
Any help will be appreciated
Thanks
First you need to install Oracle Client and then setup Linked server\use SSIS. Please check the following links below to install Oracle Client. System DSN doesn't work with this approach. Try to use the driver "Oracle provider for OLEDB". It works better than MS OLEDB driver.
http://www.mssqltips.com/sqlservertip/2011/export-sql-server-data-to-oracle-using-ssis/
Also Please post the exact error you are getting when you try to use the OLEDB . Also please let us know what you are trying to do with "Import Export Wizard.
--
SQLBuddy
March 3, 2014 at 4:58 am
This is very useful information shared here. I am really thankful for this.
March 4, 2014 at 2:38 am
Many thanks SQL Buddy - I will test and get abck to you to let you know if it worked
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
March 4, 2014 at 8:05 am
Ness (3/4/2014)
Many thanks SQL Buddy - I will test and get abck to you to let you know if it worked
You are welcome Ness. Sure, pls keep us posted.
--
SQLBuddy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply