Connecting to Oracle DB using SSIS

Can anybody point me how to connect to an Oracle DB using SSIS? I use the ole DB Connection Manager and pick the Microsoft OLE DB Provider for Oracle but it doesn't seem to work. Any suggestions are greatly apprecaited.

Thanks.

Sam.

Why doesn't it "seem to work"? What errors are you getting? What isn't happening? What IS happening?

Have you got Oracle client installed? Have you configured OLE DB Driver with the name of the TNS entry?

-Jamie

|||

This is the error msg I get.

Test Connection failed because of an error in initializing provider. ORA-12514: TNS listener does not currently know of service requested in connect descriptor.

I have Oracle client installed.

How do I configure OLE DB Driver with the name of the TNS entry? I am new to Oracle...so please bear with me..

Thanks a lot..

|||

First of all you need to verify that you can connect to oracle using the configured driver before you try to do it in SSIS.

There is a file on your client called tnsnames.ora. That file should contain what is called a TNS entry that contains all teh connection details for your Oracle server. Your Oracle DBA will give you the TNS entry.

Verify that you can connect and then connect the name of the TNS entry (it usually ends in ".world") into the OLE DB Driver config UI.

-Jamie

|||

Hi Jamie,

Thanks for a quick reply. I have an entry in the tnsnames.ora file for the connection details. So I checked to see if I can connect with the host string in the tnsnames.ora along with the user name/password. To do that I used Oracle SQL* PLUS. I am able to connect.

I couldn't understand the second step...

"then connect the name of the TNS entry (it usually ends in ".world") into the OLE DB Driver config UI." Can you please explain in more detail?

Thanks a lot...I really appreciate your help.

|||

When you build the SSIS Connection Manager using the microsoft OLE DB Provider for Oracle you will see a box labelled Server Name. Copy the name of the tns entry into that box.

-Jamie

|||

I got it Jamie. I was having two locations for tnsnames.ora and that was the reason for the error...

I appreciate your help.

Thanks.

|||

Hi Jamie,

I have the same problem now in production environment. I have to connect to oracle db from production too and I downloaded the client tools on my 64 bit windows server 2003. I edited the tnsnames to have an entry. When I try to connect to oracle, it gives me an error saying client and networking tools not installed, but I did install them. Can you please help me?

Thanks.

Sam.

Connecting to Oracle DB using SSIS

Can anybody point me how to connect to an Oracle DB using SSIS? I use the ole DB Connection Manager and pick the Microsoft OLE DB Provider for Oracle but it doesn't seem to work. Any suggestions are greatly apprecaited.

Thanks.

Sam.

Why doesn't it "seem to work"? What errors are you getting? What isn't happening? What IS happening?

Have you got Oracle client installed? Have you configured OLE DB Driver with the name of the TNS entry?

-Jamie

|||

This is the error msg I get.

Test Connection failed because of an error in initializing provider. ORA-12514: TNS listener does not currently know of service requested in connect descriptor.

I have Oracle client installed.

How do I configure OLE DB Driver with the name of the TNS entry? I am new to Oracle...so please bear with me..

Thanks a lot..

|||

First of all you need to verify that you can connect to oracle using the configured driver before you try to do it in SSIS.

There is a file on your client called tnsnames.ora. That file should contain what is called a TNS entry that contains all teh connection details for your Oracle server. Your Oracle DBA will give you the TNS entry.

Verify that you can connect and then connect the name of the TNS entry (it usually ends in ".world") into the OLE DB Driver config UI.

-Jamie

|||

Hi Jamie,

Thanks for a quick reply. I have an entry in the tnsnames.ora file for the connection details. So I checked to see if I can connect with the host string in the tnsnames.ora along with the user name/password. To do that I used Oracle SQL* PLUS. I am able to connect.

I couldn't understand the second step...

"then connect the name of the TNS entry (it usually ends in ".world") into the OLE DB Driver config UI." Can you please explain in more detail?

Thanks a lot...I really appreciate your help.

|||

When you build the SSIS Connection Manager using the microsoft OLE DB Provider for Oracle you will see a box labelled Server Name. Copy the name of the tns entry into that box.

-Jamie

|||

I got it Jamie. I was having two locations for tnsnames.ora and that was the reason for the error...

I appreciate your help.

Thanks.

|||

Hi Jamie,

I have the same problem now in production environment. I have to connect to oracle db from production too and I downloaded the client tools on my 64 bit windows server 2003. I edited the tnsnames to have an entry. When I try to connect to oracle, it gives me an error saying client and networking tools not installed, but I did install them. Can you please help me?

Thanks.

Sam.

connecting to MSDE on Windows 2003 SBS Server

I have an application (Point of Sale System) that utilizes a MSDE
database. The server came loaded with MSDE instances already and I
installed one for the POS System. The client PCs can see the other 3
MSDE instances, but cannot see the one I installed. Any ideas on how
the new instance can appear on the network. The new instance was not
named. The other three were named instances of MSDE.
hi,
auschnet@.gmail.com wrote:
> I have an application (Point of Sale System) that utilizes a MSDE
> database. The server came loaded with MSDE instances already and I
> installed one for the POS System. The client PCs can see the other 3
> MSDE instances, but cannot see the one I installed. Any ideas on how
> the new instance can appear on the network. The new instance was not
> named. The other three were named instances of MSDE.
please verify the required/desired network protocols are enabled... MSDE
installs by default disabling them and you can override this behavior
providing the
DISABLENETWORKPROTOCOLS=0
parameter to the setup.exe boostrap installer, or later, at run time, using
the Server Network Utility (svrnetcn.exe) and enabling the preferred network
protocol..
additionally, WinXP sp2 ships with a built-in Firewall that's enabled by
default, preventing network connections to... if this is a case, you have to
specify an exception for the service or for the MSDE used port, usually
specifying a subnet or individual remot IP addreses to restrict
connections...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

connecting to local server failing

Hi,

I am trying to connect to my local db server using the machine name. This at one point worked but not it is failing with windows auth.

How can i change the SA password as I cannot remember this and it might get me logged in?

Thanks
Jordna

Also, normally there is a service for SQL server under Services and I do not see this anymore for 2005. Can somebody point to where this would be?

Thanks

|||

Jordan,

You should see a service SQL Server. If you don't then something is wrong with the installation on your machine. Do you see anything in Add/Remove Programs?

Dan

CONNECTING TO ACCESS DATA via SQL Server Managemnt Studio

HI All, forgive me but I am new to SQL Server.

My question is in 2 parts. THe first - Could anyone point me to a tutorial or give me a quick pointer? I want to View my Access 2003 datatables through Server MAngement Studio. I cannot however work this out or find articles on it. Is it possible? Are there any wlakthroughs?

The second Part - Eventually I want to create an SQL database, which is comprised of tables which use (point to) MS ACCESS as there datasource. An unchangeable existing piece of software uses Access as the UI, so I want to leave my data in access, and just set up some pointers from the SQL database tables to read in data from these access tables as when required.

Is that a clear question? Am I making any sense? PLease let me know as I am scratching my head a lot at the moment!!

Cheers guys,

Anthony

You can;t access "Access Database" Tables from SQL Management Studio. but SQL Tables can be from "Access DB"

I can suggest you import data from Access into SQL Server then create link tables in your Access DB.

Access Forms will run with Link Tables which will fetch data from SQL Server.

- Jignesh

|||

you can configure a link server from sql server

to view data from access

http://msdn2.microsoft.com/en-us/library/ms175866.aspx

|||

HI Jignesh,

You can by configuring it as a link server. I did this about an hour after posting my query! Lots of people ahve said you cant but you can, it should be made a bit more obvious I think.

CHeers anywya,

Anthony

|||

Hi Joey,

yes I did this shortly after posting the question.

Thanks anywya, loads of people dont know about this, strangely.

Cheers for your help.

Anthoyn

Connecting SQL 2k to SQL7

While connecting a SQL 2k server to a SQL 7 server to
replicate data, the connection is slow to the point where
the connection times out. The connection is be established
over a full T1 line. Any ideas on how to resolve this
issue?What replication model are you using? One of the reason for replication
time-out is blocking on the subscriber. Make sure you don't have blocking
proccesss on the destinatin database.
Richard
"Paul S. Atkinson" <PAtkinson@.AppliedPsych.com> wrote in message
news:047c01c34a42$b8ff6c70$a101280a@.phx.gbl...
> While connecting a SQL 2k server to a SQL 7 server to
> replicate data, the connection is slow to the point where
> the connection times out. The connection is be established
> over a full T1 line. Any ideas on how to resolve this
> issue?

Connecting sql 2000 sp3 with asp

I created a new SQL database using SQL 2000 SP3 on a Windows 2003
server. Now when my wed publisher changes his codes in ASP to point to
my new server and SQL database we are getting the message this page can
not be displayed when clicking on the web link. The old SQL database
was created with SQL 2000 on a Windows 2000 server. Below you will find
the ASP code that we are using. Please Help me!

<!--Open Connection to SQL Database-->
<%'CREATE/OPEN ADO CONNECTION OBJECT
Dim objConnect, oSurvey
Set objConnect = Server.CreateObject("ADODB.Connection")
oSurvey = "driver={SQL Server};server=SPD-HEAT-01;database=Heat8;" &
_
"uid=johndoe;pwd=johndoe"
objConnect.Open oSurvey,"",""%Try enabling server side debugging on the iis server, and then goto the
site again, this might give you a better idea of exactly what the
connection error is.

Its also possible the new db server has a different driver string for
the sql server.

Connect with Access

Can someone point me in the right direction.. I want to connect to and call
either a procedure or function from accessYou can add a linked server to Access from SQL Server. For examples, see
sp_addlinkedserver, and sp_addlinkedsrvlogin in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Brian" <nixtootec@.hotmail.com> wrote in message
news:edIFt3k#DHA.2324@.tk2msftngp13.phx.gbl...
Can someone point me in the right direction.. I want to connect to and call
either a procedure or function from access

Connect with Access

Can someone point me in the right direction.. I want to connect to and call
either a procedure or function from accessYou can add a linked server to Access from SQL Server. For examples, see
sp_addlinkedserver, and sp_addlinkedsrvlogin in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Brian" <nixtootec@.hotmail.com> wrote in message
news:edIFt3k#DHA.2324@.tk2msftngp13.phx.gbl...
Can someone point me in the right direction.. I want to connect to and call
either a procedure or function from access