Best Way For SQL Server To Oracle Connection

Feb 5, 2003

We are in the process of setting up some type of interface between 2 applications that utilize SQL Server(ours) and theirs which utilizes Oracle. These servers will actually sit in the same server room. My thought was to take the simple approach of each DB setting up views needed by the other and assigning a user and security so the other DB can look at the data. We currently do this in house. THeir solution is to use a somewhat sophisticated mesaging system which will pass XML information back and forth between the systems. I hate to take the simpleton approach but why go through all that trouble where a simple view not only provides the same functionality, but is a dynamic as it gets. If anything goes wrong with the messaging system itself or problems with a specific message this needs to be resolved before the data is available. Non of this is a problm with the view. Any input from anyone who has this in place(either solution) appreciated. Or any arguments against my point of view

View 1 Replies


ADVERTISEMENT

Oracle Connection Fail With Microsoft OLEDB Provider For Oracle MSDAORA.1

Feb 22, 2006

Hello,

On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.

I use one or the other according to my needs.

In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.

Almost everything works except Microsoft OLEDB provider for Oracle.

ssis packages on the test machine will return an error

Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.

Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".

I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.

Does anyone could point me to the right direction to solve this issue?

Thanks,

Philippe

View 17 Replies View Related

SQL Server 6.5 To Oracle DB Connection.

Dec 19, 1999

I am having some difficulty to update a record in a table from SQL Server 6.5 to Oracle 7.3 DB.

Basically I want to write a insert trigger in Sql Server 6.5 table and I want to update the same inserting record to Unix Oracle table. I tried by writing a DLL and use that in SQL Server External Stored Procedure, but I dont know how to pass the parameter for that. Parameter here is record which I want to insert in to Oracle Table.

Is there any easy way to do that? Did any body did that before? Please help me out.

Thanks a lot

View 2 Replies View Related

Oracle Linked Server Connection Error After Upgrading To SQL 2005

Mar 14, 2007

Hello all.

I upgraded a SQL 2000 installation to SQL 2005 this past weekend. Minimal issues overall, but one that did crop up relates to a linked server to an Oracle database. The linked server worked before the upgrade, and stopped working immediately after.

We're running on Windows 2003 x64, latest service pack. The linked server is set up using the MSDAORA provider. The error I'm getting post-upgrade is:

=====================

OLE DB provider "MSDAORA" for linked server "PROD" returned message "ORA-06413: Connection not open.".

Msg 7303, Level 16, State 1, Procedure RDM_GET_REP_LIST_SP, Line 12

Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "PROD".

=====================

A tnsping from the server works fine. I've also tried recreating the linked server. Any help would be appreciated. Let me know what other information would be useful.

Thanks,

Adam

View 3 Replies View Related

Connection To Oracle Db's

Oct 1, 2001

I need a way to connect to an Oracle database and pull the information from that database into a Sql Server Database. An Oracle gateway is not an option.
Is there a way to set up an odbc connection to connect to the database or is there another way to connect?

Thank you,
Anita Ossowski

View 3 Replies View Related

Oracle Connection

Dec 14, 2007

I've created a SQL stored procedure and would like to connect to an Oracle database & query results within the procedure. Is this possible?

View 1 Replies View Related

Oracle Connection

May 19, 2008

I am moving dta afrom an Oracle source to SQL serevr 2005 using SSIS

Used: Provider: Microsoft OLE DDDB provider for Oracle and typed in Server and username with password.

When executed the package with defaul settimngs( Package ProtectionLevel: EncrptSensitiveWith userkey OR DONTSAVESENSITIVE, EncrptAllwithPassword) and Isolationlevel: serializable/ read comitted) the package responded fine.

When I try to do 'Package Configuration' and type in password in config file the package doesn't responds. it gives following error.



TITLE: Microsoft Visual Studio
------------------------------

Error at Package2 [Connection manager "DEV04.XYZ"]: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "DEV04.XYZ" failed with error code 0xC0202009.



------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
------------------------------
I also get this error:

Executed as user: XYZUHSQLServer. ...042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:01:42 PM Error: 2008-05-19 13:01:43.22 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-05-19 13:01:43.96 Code: 0xC0202009 Source:ExtAWMData Connection manager "XYZ ABC" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E4D Description: "ORA-01017: invalid username/password; logon denied ". End Error Error: 2008-05-19 13:01:43.96 Code: 0xC020801C... The package execution fa... The step failed.




Please advice on
1) Selection of provider for Oracle connection
2) ProtectionLevel
3) Isolation Level
4) Package configuration - Connection Strings (MSDAORA.1)
5) Publishing the package on Server best way.

Thnak a lot for all your valuable time and efforts.

AWM

View 6 Replies View Related

64 Bit Oracle Connection

Oct 17, 2007

I've installed 32 and 64 bit oracle drivers. I was testing running the two different dtexec.exe programs the other day, logged in as the sql agent and they both worked. Running the 64 bit as a job did not work. 32 bit does not work, since the agent process is 64 bit, from what I understand.

Today I'm working on it again and trying the same steps. Running the 32 bit executable dtexec is now returning "not a valid win32 executable". I swear this worked the other day, and works on our other server.

Running the 64bit executable from the agent just hangs up on this log entry:


HRD1.Cardiff (my oracle connection)















ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.

Anyone know what is going on?

http://support.microsoft.com/kb/918760/

I read this article, but didn't think it applied,since I chose for protectionLevel = sql to handle permission (I think they ) -
So I created a proxy using my credentials and gave it access to cmdexec and ssis and it worked. Can anyone explain this to me??

View 11 Replies View Related

Connection To Oracle

May 10, 2007

Hi,



I have created a simple report that reads data from Oracle within Visual Studio. I have no problem with previewing the report within Visual Studio. When I deploy the report to the Report Server (same machine) and try to open the report through Report Manager, I get the following error message:



An error has occurred during report processing.

Cannot create a connection to data source 'xxxx'.

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.



I am aware of the following issue that requires extra security to be assigned to the Oracle directories: http://support.microsoft.com/Default.aspx?kbid=870668



Even after applying the Read/Execute access to the Network Service account that is running the Reporting Service, I still get the error message.



Can anyone help me with the debugging of this issue?



Thanks,

Ben

View 6 Replies View Related

Connection To Oracle

May 9, 2006

I have created reports in SQl Server 2005 Reporting Services using an Oracle Data Sources. When I Preview the report in VS 2005, It runs fine but when I deploy it, it fails to make a coonection to the oracle data source.

Reports off the SQL Server Data Sources arerunning fine.



Can any one out there help me out on this. I will really appreaciate.



Regards,

Sam.

View 8 Replies View Related

Setting Up Oracle Linked Server : Need Help : Sql2005 Running On XP Linking In Oracle 10.2

Oct 26, 2006

Is there any step by step help sites for setting up SQL 2005 linked (oracle 10) server?

I find MSDN articles but they referance winNT and 2000, I'm not getting very far and I'm not a DBA but need to get this working asap.

View 1 Replies View Related

Data Access :: Accessing Oracle Tables From Server Via Oracle DBLINK?

May 8, 2015

we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.

what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?

View 2 Replies View Related

Trouble With: Linked Server To Oracle Using OraOLEDB.ORacle Provider

Jan 11, 2007

Hi--

 

I am running SQL Server 2005 on Win2k3:

Microsoft SQL Server Management Studio      9.00.2047.00
Microsoft Analysis Services Client Tools      2005.090.2047.00
Microsoft Data Access Components (MDAC)      2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML      2.6 3.0 4.0 6.0
Microsoft Internet Explorer      6.0.3790.1830
Microsoft .NET Framework      2.0.50727.42
Operating System      5.2.3790


I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.

I am having problems querying from linked oracle server.  When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password.   So it at least knows that.  when i set the correct password and run a query I get this error:

(i replaced the real server name with "someServer".)

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".

 

This is how I set up my Linked server:

Provider: "Oracle Provider for OLE DB"

Product Name: SomeServer

Data Source: SomeServer

Provider String:  "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword"

 

 

The query I run is:

Select * from [Someserver].[schema or database]..[tbl_name]

 

Any help???  What am i missing?

View 3 Replies View Related

Connection To Oracle Sometimes Hangs

Jan 3, 2006

Hi,For a customer we have implemented a datawarehouse with a connection toOracle. From the beginning of the project the connection to Oraclegives troubles. The next morning is the connection still active andwhen the customer looks in the processes of SQL Server a " Wait type,RESOURCE_SEMAPHORE." is shown. For what i know. SQL Server is waitingfor a resource, in this case Oracle but doesn't tell me anything aboutwhat could be the problem.The customer uses the Oracle driver instead of a microsft oracledriver.What could it be? and how can we tackle this problem? Are there ways tolook from Oracle point of view? Can we monitor something?Greetz,Hennie

View 2 Replies View Related

SSRS Oracle Connection

Apr 18, 2007

Hi,



We are trying to change our current SSRS reports to point to an Oracle 10g database. I can connect to the Oracle db from the report server via the ODBC Administrator and also through the BI 2005 studio. The connections work fine. However, when I publish the reports and try to run them from the web I always get a connection error. When I use the oracle connection it acts as if it doesn't see the oracle client and when I use ODBC it throws a werid 114 error.



Is there anything I am missing? I just don't understand why the report will work in BI Studio, but not when it is published (on the same machine)?



Any thoughts?



S

View 1 Replies View Related

Oracle Connection Trouble

Oct 19, 2007

Hi,

I'm trying to get some developers going with ssis on their desktops running



XP sp 2

Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00

Oracle client drivers 9.2.0.1.0

Oracle Enterprise 8.1.7.4.0 and 9.2.0.1.0

I have the same setup on my machine and it's working fine. I duplicated steps I used to get data on the develoeper machine and got these errors.



Code Block

Pre-Execute
Execute phase is beginning.
Execute phase is beginning.
PrimeOutput will be called on a component. : 1 : OLE DB Source

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01403: no data found".

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01403: no data found".

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.





I'm at a loss of where to go next and I need to get these guys rolling!
Next step is to reinstall drivers...but I'm doubting that is going to have any effect.

Thanks for any help

View 9 Replies View Related

Problem In Oracle Connection Through DTS

Feb 15, 2006

The problem being faced is : I am unable to access the oracle data through DTS Transform Data Task or Data Driven Query Task.

I have added the connection object . The test connection succeeds but when I move to the properties of either TDT or DDQ , It just hangs up

View 1 Replies View Related

DTS Oracle Connection - Importing Data

Dec 9, 2003

I have SQL Server 2000 and am using DTS to simply import some data located on a remote Oracle DB.

If I have the following information:

TNS Name:
ThisApp.world
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcpxxx.world)
(PROTOCOL = TCP)
(Host = SomeHost)
(Port = 1234)
)
)
(CONNECT_DATA =
(SID = App)
(GLOBAL_NAME = ThisApp.world)
)
)

And I also have the name of the Table.


Using the DTS Wizard, on the Choose a Data Source screen, I pick the following:

Data Source: Microsoft ODBC Drive for Oracle
Server:
Username:
Password:

I don't know what I should put into the other fields, given what I have above, i.e. what to put in for Server, Username, and Password! Or should I use a different procedure to import this Oracle data? I am almost positive I was not supplied the username/password. I was told I could connect given the above TNS information. What do you all recommned?

Your assistance will be greatly appreciated.

View 1 Replies View Related

Changing DB Connection From SqlServer To Oracle

Jan 23, 2007

Hi

I am trying to switch between oracle and sqlserver databases to read the source data. I have used a parameter file to specify the connection parameters. For SQLserver the connection looks like in the file as below:

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="abvsh" GeneratedFromPackageName="Package" GeneratedFromPackageID="{8A304BF7-5325-4079-9D92-2B9BBF8793AA}" GeneratedDate="1/23/2007 4:46:08 PM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=dl;Initial Catalog=PM_DW;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue>PM_DW</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Name]" ValueType="String"><ConfiguredValue>dl.PM_DW</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Password]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ServerName]" ValueType="String"><ConfiguredValue>dl</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[UserName]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration></DTSConfiguration>

the pacakge runs fine. But when i change the connection to Oracle, it gives me error.

The config file looks like when i put oracle connection is as follows:

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="I2vshrivas" GeneratedFromPackageName="Package" GeneratedFromPackageID="{8A304BF7-5325-4079-9D92-2B9BBF8793AA}" GeneratedDate="1/23/2007 4:46:08 PM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=pm62;User ID=pcm_62;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Name]" ValueType="String"><ConfiguredValue>dl.PM_DW</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Password]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ServerName]" ValueType="String"><ConfiguredValue>i2pm62</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[UserName]" ValueType="String"><ConfiguredValue>pcm_62</ConfiguredValue></Configuration></DTSConfiguration>



The error which i get when running from oracle source is this:

Information: 0x40016041 at Package: The package is attempting to configure from the XML file "C:oraTOsql-DataTXIntegration Services Project1Integration Services Project1Integration Services Project1 estConfig.dtsConfig".
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at Package, Connection manager "dl.PM_DW": An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Error: 0xC020801C at Data Flow Task, OLE DB Source [1]: The AcquireConnection method call to the connection manager "dl.PM_DW" failed with error code 0xC0202009.
Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "Package.dtsx" finished: Failure.
The program '[7264] Package.dtsx: DTS' has exited with code 0 (0x0).


Please let me know if you guys have any suggestion in what i am doing wrong.

Thanks,

Vipul

View 3 Replies View Related

Oracle Connection Manager Configuration Help

Apr 14, 2006

Hi, I dont know if I should post this question here, or on oracle forums, but here goes ...

I made a simple package that performs 2 Excecute SQL Tasks, one task does a select query on a SQL Server DB Table, the other task does a select query on an Oracle DB Table.

The package runs fine in BIDS when debugging, but when I deployed the package to a file system and added it to an sql server agent Job, it all went wrong ...

I tried the ms oledb dataprovider (ole db) for oracle but also the oracleclient data provider (ado.net) but the package always fails.

The error messages in the logfile are these:








event
OnError

computer
ComputerName

operator
NT AUTHORITYSYSTEM

source
Execute SQL Task PLSQL

sourceid
{B29C4FC9-3345-4657-8680-F770B8C2E136}

executionid
{7312A7EC-2555-4268-BB29-38659507BADD}

starttime
14/04/2006 16:40

endtime
14/04/2006 16:40

datacode
-1073573396

databytes
0x

message
Failed to acquire connection "aaa.bbb.oracleClient". Connection may not be configured correctly or you may not have the right permissions on this connection.

the OnError logrow for the package itself says almost the same.

Now my question is: What do I need to configure to get it all working? I think I need to configure the oracle db no? What should I configure? ...

View 6 Replies View Related

Oracle Source Connection Error

Aug 30, 2007



Hi,

I was trying to connect to Oracle Source in SSIS

1. Err: Test connection failed because of an error in initializing provider. Oracle Client and networking components were not found
Sol: I installeed the Oracle Client tools

2. Err: ORA -12154: TNS: Could not resolve teh connect identifier specified
Sol: Then I point out the TNS file

3. Now I coonect the Oracle db . I go to Connection Manager . Specifies the provide as: Native OLE DB Oracle Provide for OLE DB, Select my DB, provide teh user name and password. and check if the connection passed.
Then in my data Flow Task when I add this source and check the Colmns its gives me an error./ sort of warning

The component reported the following warnings:

Warning at {C947B.......} [Ole DB Source[1]]: Cannot retrieve the column code page info from the OLE DB provider. if the components supports teh "DefaultCodePage" property, the code page from that property will be used.....

Will this warning has an significance. i dont see any error in my output data. but is there something I m doing wrong.

4. When I set my ProtectionLevel under Security in package property to "Dont Save Sensitive" in order to easily configure the package I m getting this error: failed Validation. The default one was EncryptSensitiveWithUserKey then its running smoothly.
Can somebody advice me on this?

I addded the all the errors and solution for 1 and 2 becuase in that way this will be helpful in future.







View 5 Replies View Related

Dynamic Connection String For Oracle

Oct 31, 2007

i have successfully implemented a dynamic connection string based on a dropdown list of environments (dev, test, prod). it works well during testing in the vs2005 ide; but once i deploy it to the rs server, it complains that the credentials are not stored in the rs server database and won't run the report.

as in most large organizations, the developers do not have control over the rs server, so i cannot manipulate rs config or web config files on the server side; so, how do i get past this obstacle?

thanks in advance

View 1 Replies View Related

Connection To Oracle / Sqlserver / Db2 Databases

Jan 23, 2007

Hi All,

Is there a way in SSIS to change the database connection to sqlserver or oracle or db2 without choosing the connection manager in the source.

For e.g. I ran a package which is reading data from sqlserver and later i want to connect to oracle to read the data but without changing the connection manager in the source.

Basically, i want to find out from you gurus is about a way to switch between databases without changing the connection managers. OR the only way is to make different sets of packages for oracle / db2 and sqlserver sources ?

Thanks,

Vipul

View 2 Replies View Related

Oracle 9i OLE DB Data Source Connection

Sep 13, 2006

help - I€™ve been stuck on this for days.

My software vendor limits the sessions to one (1) per log-in to Oracle 9i and I€™m having tons of problems trying to extract data through an SSIS package. Everytime I run the package SSIS already logged in and the OLE DB source fails because of the sessions exceeded error.
Is there any way to limit the connection to just when I need the data to pump across on the OLE DB source?

Thank you very much for any help.

Regads,
Nestor

View 1 Replies View Related

SSIS Connection To Oracle View

Jun 20, 2006

Hi

I have a number of scripts that run against an oracle server to retreive data to manipulate before populating a sqlserver database. I am connecting using an Odbc connection and using the DataReader Source to read from the Oracle table. It all seems to work ok.

When I have tried to extract data from an Oracle view in the same way it doesn't seem to work. The error message mentioned the PrimeOutput() method and error code 0xC02090F5. The view is very big so is it possible that the connection simply timed out?

Any ideas?

Thanks

B

View 6 Replies View Related

Oracle Connection Using Configuration File

Jun 23, 2006

I created a package that uses configuration file for db connections. SQL Server connection works fine, but for oracle it fails.

I m putting servername=alias from tnsnames.ora file.

the connection works fine from ide



View 4 Replies View Related

Connecting To Oracle DB (connection Error)

Feb 6, 2007

While upgrading several packages from DTS 2000 to SSIS I came across a couple of packages where the source data comes from an Oracle DB. Until now I have not migrated data from an Oracle source.

When I tried to create a new connection I got the error:
"System.Data.Oracle client requires Oracle client software version 8.1.7 or greater."

This is being developed on a machine that would not have any oracle drivers before now, so I THOUGHT it would be as simple as downloading the drivers, but the resolution seems to be much more complex than that. When I researched it, it appeared that I needed to download the Oracle 9i software. The installation is about 10 GB and this seemed like overkill to just be able to connect to a database.

Is this in fact the correct solution? If so, or even if not, can I get a quick run down of the steps involved, and anyone's experience (problems, etc..) on the topic?

View 5 Replies View Related

Oracle Connection In Solution Explorer

Mar 10, 2008

When trying to create a data source in Solution Explorer the Provider is greyed out and only allows me to create a connection to a SQL server database although I can connect to an oracle database in Server Explorer

View 5 Replies View Related

How To Get Database Name In Oracle DB Connection In SSIS

Apr 27, 2007



Hi,



I used OLE DB for oracle connection provider, it takes only Server name, User id, Password but along with it,

I want to give it Database name like in OLE DB for Sql Server.



Is it possible to provide it in some other way, so that i can put DataBase name directly also.



Thanks

View 12 Replies View Related

Oracle Drivers In Connection Managers

Feb 15, 2006

I am not seeing an option to use the OraOLEDB.Oracle driver when defining connections in ssis. This driver shows up in management studio when trying to create linked servers so I don't know why it wouldn't show up here. All the client tools are installed fine and the linked server works. Any ideas?

View 4 Replies View Related

Connection Problems To Oracle Database

Mar 6, 2007

Hi,

I have a package connecting to oracle database ,this was developed on 32 bit Windows OS now i moved the same to 64 bit OS and it fails to connect to oracle database.

I have created system DSN and tested it was successfull.

In my package i am using ado.net connection object ,I am using ODBC data provider.I select the Data source in the drop down .provide user name and password and when i hit on test connection it fails with the following message

Test connection failed because of error in initilizing the provider error[08004]

[oracle][odbc][ora]ORA 12154 : TNS could not resolve the connect identifier specified

[Error]IM006[Microsoft][ODBC driver manager] drivers SQL setconnectattr failed



Any help on this will be appreciated

Note:- even though the OS is 64 bit i am using 32 bit ODBC as BI studio can only access 32 bit odbc

View 2 Replies View Related

Connection Manager Reuse For Oracle And SQL Databases

May 2, 2008

I'm trying to implement a configurable way of executing a group of SQL statements using either an Oracle or SQL database as the source for the data.

I'm currently building a connection string with a Script Task and then assigning it to a package variable so that it can be used by a connection manager but this only works with a SQL OLEDB connection.


I'm holding all the connection details in a database table, hence the need for the scripting but would it be possible to extend the task to reuse a single connection manager by changing it's properties?

View 7 Replies View Related

How To Create Connection Manager For Oracle DB Using ODBC?

Jan 17, 2008

Hi,
I want to import data from Oracle database programmatically using ODBC connection manager. I was able to import data by creating an ODBC DSN and then using it BIDS with ADO.NET connection using ODBC data provider for Oracle. Now I want to do the same programmatically. How should I create a connection manager? I tried using code below


ConnectionManager cmOracle = this.package.Connections.Add("ADO.NET: ODBC");

cmOracle.Name = "OracleSourceConnection";

cmOracle.ConnectionString = "Dsn=MyDSN;uid=MyID;";

But I get an error when acquiring connection
ErrorCode=-1071611874

The connection manager "__" is an incorrect type. The type required is "__". The type available to the component is "__".
Which one of the connection managers given by Microsoft here should be used?
http://msdn2.microsoft.com/en-us/library/ms136093.aspx
Has anyone come across similar scenario?

Thanks


View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved