Oracle Provider of OLEDB - Unable to process cubes

Hi all,

Our data mart resides in Oracle 9i.

I can connect the data source views fine, and explore the data in those views.

Even in the cubes themselves, I can explore the data.

But when I go to process the cubes, I get the error ...

"Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'RTM Data Mart', Name of 'RTM Data Mart'.

Anyone know why?

The provider is the Native OLE DB provider from Oracle.

Thanks,

--Phil

Check your impersonation settings, it should be 'use the service account'|||

That did the trick - thanks.

Interesting that the Data Source Views are happy enough with a fixed identity, but processing cubes wants the service account.

But I am happy to have made progress - thanks again.

oracle oledb provider not registered in local machine

Hi,

I am trying to establish a connection to an Oracle database using the following code in a script task:

Dim oOleDbConnection As OleDbConnection
Dim sConnString As String = _
"Provider=OraOLEDB.Oracle;" & _
"Data Source=DBxxx;" & _
"User ID=Userxxx;" & _
"Password=Passxxx"
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()

When I execute the script task, I receive the following error:
The 'OraOLEDB.Oracle' provider is not registered on the local machine.
Am using the correct provider?

I do not know how to resolve the said error.
Here are some facts:
Oracle 8i is installed.
Tnsnames.ora is updated.
I have successfully connected to Oracle SQL *Plus to test the above credentials.

Please help.
Thanks.

Do you have the Oracle OLE DB driver installed?

http://www.oracle.com/technology/software/tech/windows/ole_db/index.html|||

Shouldn't this be part of SQL Server 2005 standard installation?

I tried creating a new OLEDB connection and found in the drop down: Native OLE DB\Microsoft OLE DB Provider for Oracle.
I checked its full properties and saw provider specified: MSDAORA.1
Does this mean that the provider for Oracle is already installed?
I already tried MSDAORA.1 in my code but the same error appears.

|||

r214acc wrote:

Shouldn't this be part of SQL Server 2005 standard installation?

I tried creating a new OLEDB connection and found in the drop down: Native OLE DB\Microsoft OLE DB Provider for Oracle.
I checked its full properties and saw provider specified: MSDAORA.1
Does this mean that the provider for Oracle is already installed?
I already tried MSDAORA.1 in my code but the same error appears.

The Microsoft OLE DB Provider for Oracle is not the same as the Oracle OLE DB Provider. The Oracle OLE DB Provider is published by Oracle, not Microsoft, and isn't part of the SQL Server 2005 standard installation. I know this because I'm running SQL Server Standard on one of my machines.|||So, like I said, try downloading the Oracle OLE DB driver, and then try your code.|||

Hi Duane,

You are right. However, I tried both MS OLE DB Provider for Oracle and Oracle OLE DB Provider but the error still persists.
Is it possible that Oracle 8i (on the server running SSIS) does not have the components to be used by the SQL Server 2005? or can it connect to a Oracle 9i 64-bit?

Please help me find the answer. Thanks.

|||

r214acc wrote:

Hi Duane,

You are right. However, I tried both MS OLE DB Provider for Oracle and Oracle OLE DB Provider but the error still persists.
Is it possible that Oracle 8i (on the server running SSIS) does not have the components to be used by the SQL Server 2005? or can it connect to a Oracle 9i 64-bit?

Please help me find the answer. Thanks.

Sorry, I don't have an answer for your question. However, I have a question for you. Are you using the 64 bit version of SQL Server 2005?|||

I am using 32 bit version of SQL Server 2005.
Is this the cause of the problem?

|||

There are some issues with Oracle drivers on 64 bit, hence Duane asked.

You say Oracle and SQL with SSIS are installed on the same server. That is fine, but are you really working on the server itself or a desktop?

|||

Hi,

I am working on the server itself.
I found something in the forum that may be related to the problem:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=449593&SiteID=17
It says that there is a bug on parsing folder names with parenthesis which happens when a 64-bit OS installs 32-bit programs.
Since I am running a 32-bit SSIS. This may be the problem.

Is there a fix here?
Thanks.

Oracle OleDb Provider as Source

Guys,

I am having a nightmarish time getting an Oracle Connection Manager working as a source in my SSIS package.

The CM is called "OLTP_SOURCE". When I inspect the configuration and test connection, it succeeds, however when I go to run the package (both in debug mode and via DTEXECUI) I get the following error:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

After this happens, if I go into an OLE DB Source within a DFT, I get the following:

No disconnected record set is available for the specified SQL statement.

Now, if I go back into the CM, enter the password and test, it succeeds. From this point, I will go to preview the data in the OLE DB Source, and it comes back fine. However, when I go to run the package, I get the same error time and time again:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

The quick reader will suggest that the password is not being persisted. To this end, I have tried each of the following techniques to no avail:

1. Double, Triple and Quadruple check that the "save" password option in the CM is checked.

2. Hardcode the connection string in the dtsx XML-behind.

3. Enable Package Configurations and hardcode the connection string in the dstsconfig file.

4. Run the dtsx file using DTEXECUI, providing it with the configuration (that includes the hard-coded password).

5. Run the dtsx file using DTEXECUI, providing it the connection string in the Connection Managager override UI.

Can anyone help shed some light on what might be going on? So far, it is obvious that there has to be something that I am doing wrong because (syntax dialect differences aside) I can't imagine that Oracle sources should be this much of a headache.

Thanks,

Rick

Update:

If I ignore this anamoly and try to step into debug mode, I get the following error within the IDE:

Error at DFT_LoadDimEntities [DTS.Pipeline]: "output "OLE DB Source Output" (11)" contains no output columns. An asynchronous output must contain output columns.

Error at DFT_LoadDimEntities [DTS.Pipeline]: The layout failed validation.

Error at DFT_LoadDimEntities: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

Any suggestions would be greatly appreciated.

Thank you,

Rick

|||

This turned out to be a combination of quirks related to Oracle tooling and syntax.

I have learned that integrating Oracle is a multi-faceted project. The first phase is syntax normalization and dealing with the tooling anamolies that come up. The second phase is getting to true interop, where I can flip flop providers on source and target and have the same package, same code base just work.

I think I am just about done with phase 1 stuff, and here are some things to consider if you run into similar problems (many of these sound rediculously trivial, but when something just works against SQL and stops working against Oracle, it can be madenning):

1. Make sure that you are saving the connection string information during design time. The only way to do this is to select one of the "Encrypt..." options. Whereas SQL source/target Connection Managers seem to hapilly retain connection info (i.e. Windows Auth, makes sense), Oracle credentials in the connection string will not get saved. I have found that this creates a number of un-intuitive error messages.

2. Check your syntax. Fire up Oracle SQL Worksheet and test your code to ensure it is interoperable.

3. Just because your code runs in both SQL and Oracle doesn't preclude strange "tooling" issues as I can them from cropping up. For example, in an OLE DB Source task, the MSDAORA provider does not seem to like comments (-- Blah) as the first line. This one drove me nuts for a good couple of days.

If I think of anything else, I'll post it, but I think that most of these problems have been addressed here and on the following blog posting which may prove helpful to others: http://rickgaribay.net/archive/2007/03/15/font-facearialstrikeadventuresstrikefont-contortions--with-ssis-oracle-interop.aspx

If anyone has specific questions, feel free to post or contact me with questions- I'd be happy to share any knowledge I've gleaned along the way.

Rick

Oracle OleDb Provider as Source

Guys,

I am having a nightmarish time getting an Oracle Connection Manager working as a source in my SSIS package.

The CM is called "OLTP_SOURCE". When I inspect the configuration and test connection, it succeeds, however when I go to run the package (both in debug mode and via DTEXECUI) I get the following error:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

After this happens, if I go into an OLE DB Source within a DFT, I get the following:

No disconnected record set is available for the specified SQL statement.

Now, if I go back into the CM, enter the password and test, it succeeds. From this point, I will go to preview the data in the OLE DB Source, and it comes back fine. However, when I go to run the package, I get the same error time and time again:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

The quick reader will suggest that the password is not being persisted. To this end, I have tried each of the following techniques to no avail:

1. Double, Triple and Quadruple check that the "save" password option in the CM is checked.

2. Hardcode the connection string in the dtsx XML-behind.

3. Enable Package Configurations and hardcode the connection string in the dstsconfig file.

4. Run the dtsx file using DTEXECUI, providing it with the configuration (that includes the hard-coded password).

5. Run the dtsx file using DTEXECUI, providing it the connection string in the Connection Managager override UI.

Can anyone help shed some light on what might be going on? So far, it is obvious that there has to be something that I am doing wrong because (syntax dialect differences aside) I can't imagine that Oracle sources should be this much of a headache.

Thanks,

Rick

Update:

If I ignore this anamoly and try to step into debug mode, I get the following error within the IDE:

Error at DFT_LoadDimEntities [DTS.Pipeline]: "output "OLE DB Source Output" (11)" contains no output columns. An asynchronous output must contain output columns.

Error at DFT_LoadDimEntities [DTS.Pipeline]: The layout failed validation.

Error at DFT_LoadDimEntities: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

Any suggestions would be greatly appreciated.

Thank you,

Rick

|||

This turned out to be a combination of quirks related to Oracle tooling and syntax.

I have learned that integrating Oracle is a multi-faceted project. The first phase is syntax normalization and dealing with the tooling anamolies that come up. The second phase is getting to true interop, where I can flip flop providers on source and target and have the same package, same code base just work.

I think I am just about done with phase 1 stuff, and here are some things to consider if you run into similar problems (many of these sound rediculously trivial, but when something just works against SQL and stops working against Oracle, it can be madenning):

1. Make sure that you are saving the connection string information during design time. The only way to do this is to select one of the "Encrypt..." options. Whereas SQL source/target Connection Managers seem to hapilly retain connection info (i.e. Windows Auth, makes sense), Oracle credentials in the connection string will not get saved. I have found that this creates a number of un-intuitive error messages.

2. Check your syntax. Fire up Oracle SQL Worksheet and test your code to ensure it is interoperable.

3. Just because your code runs in both SQL and Oracle doesn't preclude strange "tooling" issues as I can them from cropping up. For example, in an OLE DB Source task, the MSDAORA provider does not seem to like comments (-- Blah) as the first line. This one drove me nuts for a good couple of days.

If I think of anything else, I'll post it, but I think that most of these problems have been addressed here and on the following blog posting which may prove helpful to others: http://rickgaribay.net/archive/2007/03/15/font-facearialstrikeadventuresstrikefont-contortions--with-ssis-oracle-interop.aspx

If anyone has specific questions, feel free to post or contact me with questions- I'd be happy to share any knowledge I've gleaned along the way.

Rick

Oracle OLEDB in SSIS - problem with 64bit

I have a 64bit Windows 2003 Server with SQL 2005 (64bit) installed. I would want to fetch data from an Oracle database that resides on a different server and load it into SQL2005 through SSIS. This is part of migration from SQL2000.

I have tried to run Oracle client 10G (64bit) with no success, since SSIS is a 32bit application.

I uninstalled the 64bit Oracle client and installed the 32bit version and can create an Oracle OLEDB Provider as Connection Manager and preview the data from the Oracle source, but when I run the package it displays an error almost directly and the transfer stops.

--

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "BOFLEV.bofinc" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

--

I have tried to change the path for SSIS from Program Files (x86) to ProgramFilesx86 with no luck.

I have changed the Run64BitRunTime to false.

Is there anyone that has managed to resolve this, I really do not want to use a third party software for this!

SSIS has both 32-bit and 64-bit flavors. The dev environment (BIDS) only uses the 32-bit version, though.

Can you connect to Oracle through the 32-bit driver from any other application (like Access)? If not, it sounds lile the driver installation is corrupt.

|||

Yeah we managed to get this one sorted after much searching.

Be very careful with the version of oracle 10g you are using, if memory serves me right it needs to be 10.2..0.1.8 or higher, it is not sufficient to have only 10g 64 bit, it must be patched also.

We used the microsoft oracle driver for the 32bit development of the packages using BIDS, we used configurations for the connections thus allowing us to change the provider type from msdaora to oraoledb.oracle.1 for when we transfered and scheduled the package on the SSIS 64 bit. So far we have used this scenario for about 80 packages without fail.

Regards

John

Oracle OLEDB drivers problem with Numbers

Hi All:

I am using oracle oledb drivers to write to a oledb destination.

if i give decimal values to decimal fields in the source table, i get the same in destination. But if the input is integers, in some cases, the value in the destination is different from that of source

Source Target

50 50.00
100 0.000
111 111.000
600 0.0000
520 20.00
178 178
4546.50 4546.50

I have Sql server SP2 9.0.3042 installed on my machine. Please let me know if theres something i am missing out.

Thanks,

Vipul

There is no such thing as an integer in Oracle. It's a NUMERIC(p,0) field. That is, it has no scale. SSIS doesn't support this at the moment. Instead, write your query such that you convert the "integer" field into a NUMERIC(p+1,1) field, or something like that. Then map to a decimal field in SSIS. From there, if you want integers out of the data, use a derived column to cast the values to integers.|||

Let me put it this way phil. Have you come across decimal data being changed from source to target without any transforms in between ? I was not correct in putting the question but the cause of my concern is that if the source has 500 how the target is getting it as 0.

Is there some problem in SSIS for this or this is oracle oledb driver problem?

|||Have you looked at the data with a data viewer to see what is contained there? You might need to recreate the OLE DB source.|||

ya i have viewed data with the data viewer before the oledb destination. Data is fine till data viewer. Theres something happening in oledb destination and thats why i suspect the drivers.

Also, the same behaviour is not happening on one of my other machine. The machine confguration of both the machine are same. I am executing the same pacakge from both the machines.

The version of software on both mahcine are:

-

SQL Server sp2 9.0.3042

Oracle 10g

Let me know your thoughts on this..

|||Is your destination SQL Server?

Have you looked at the advanced properties of the OLE DB Destination to ensure that the data types for all of the columns are correct?|||

Destination is Oracle.

And i have checked all the datatypes as per ur suggestion but still the problem exists.

|||I'm going to have to bow out as I don't have an Oracle instance to test with.

Oracle OLEDB and dates not matching

I have a ssas2005 cube built from data in an oracle data warehouse. The Time dimension has begin week dates as a key and joins to the fact table Week Begin Date field. Both are built in the dsv using named queries. The cube built fine until we changed the provider to the Oracle provider for OLEDB. Now the cube build is giving an error : "The attribute key cannot be found: Table: Fact_x0020_Service_x0020_Level, Column: WEEK_BEGIN_DATE, Value: 7/3/2005." on the first record. The dates look the same and the properties look the same. I tried converting the dates to character in the queries and the cube builds - but I have a lot of data missing. We changed the provider because of internal rounding problems throwing the numbers off. Why is this happening? Is there a way to fix this?

The answer is trivial but situation overall quite confusing.

The implementation of OLEDB providers to the same relational datbase varies quite a lot. The changes in functionality happen from one version of OLEDB provider to another, the differences are even greater between OLEDB providers implemented by different companies.

In short: You could think that changing OLEDB provider in the conneciton string is a trivial, but in the reality it is not so at all. If this is absolutely neccessary that you use another OLEDB provider, you should work through every processing error.

Hope that helps

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I've been able to resolve all the problems but the date. It seems the issue is in the internal representation of the date between the .Net provider and SSAS - and there's no way i know of to fix the problem without converting the date to some other format - which causes other problems down the line with my date functions. Anybody have an idea on getting around this?

Oracle OLEDB and dates not matching

I have a ssas2005 cube built from data in an oracle data warehouse. The Time dimension has begin week dates as a key and joins to the fact table Week Begin Date field. Both are built in the dsv using named queries. The cube built fine until we changed the provider to the Oracle provider for OLEDB. Now the cube build is giving an error : "The attribute key cannot be found: Table: Fact_x0020_Service_x0020_Level, Column: WEEK_BEGIN_DATE, Value: 7/3/2005." on the first record. The dates look the same and the properties look the same. I tried converting the dates to character in the queries and the cube builds - but I have a lot of data missing. We changed the provider because of internal rounding problems throwing the numbers off. Why is this happening? Is there a way to fix this?

The answer is trivial but situation overall quite confusing.

The implementation of OLEDB providers to the same relational datbase varies quite a lot. The changes in functionality happen from one version of OLEDB provider to another, the differences are even greater between OLEDB providers implemented by different companies.

In short: You could think that changing OLEDB provider in the conneciton string is a trivial, but in the reality it is not so at all. If this is absolutely neccessary that you use another OLEDB provider, you should work through every processing error.

Hope that helps

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I've been able to resolve all the problems but the date. It seems the issue is in the internal representation of the date between the .Net provider and SSAS - and there's no way i know of to fix the problem without converting the date to some other format - which causes other problems down the line with my date functions. Anybody have an idea on getting around this?

Oracle Linked Server Error & ODBC Drivers Missing

I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When using the Microsoft OLEDB Oracle driver I get the following error
Error 7399: OLE DB Provider 'MSDAORA' reported and error
OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:
The native Oracle driver seems to work ok, and I can also connect usine Orcale SQL+ untility just fine, but of course the native driver requires the older flavor of SQL which means that all of my currently coded SPs would have to be ported. The same setup works fine from another SQL2k server using the MS ODBC Oracle driver so I'd like to try using that, however the ODBC driver don't appear on the drop down list of driver in EM linked server screen! I tried installing MDAC 2.8 but it doesn't give me any options for installing the MS ODBC drivers?...Thanks in advance for any ideas on how to resolve this!..."Joe Loyd" <joe.loyd@.us.didata.com> wrote in message
news:570D6F0B-C76B-46FC-BF45-22D47A37128E@.microsoft.com...
> I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When
using the Microsoft OLEDB Oracle driver I get the following error:
> Error 7399: OLE DB Provider 'MSDAORA' reported and error.
> OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ]
> The native Oracle driver seems to work ok, and I can also connect usine
Orcale SQL+ untility just >fine, but of course the native driver requires
the older flavor of SQL which means that all of my >currently coded SPs
would have to be ported. The same setup works fine from another SQL2k
>server using the MS ODBC Oracle driver so I'd like to try using that,
however the ODBC driver don't >appear on the drop down list of driver in EM
linked server screen! I tried installing MDAC 2.8 but it >doesn't give me
any options for installing the MS ODBC drivers?...Thanks in advance for any
ideas >on how to resolve this!...
You want to use the MSDAORA OleDb driver, and MDAC installs it.
It looks like it's having trouble loading the Oracle OCI libraries. If the
Oracle Client is properly installed, this is usually either a permissions
problem or a path problem. Make sure that the [oracle home]\bin is in the
path. If you have to add it, or you installed the Oracle client since your
last reboot, you must reboot the server before the path change will take
effect for services (like SqlServer).
And make sure that the account running SQL has rights to the Oracle home.
David

Oracle Linked Server Error & ODBC Drivers Missing

I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When using the Microsoft OLEDB Oracle driver I get the following error:
Error 7399: OLE DB Provider 'MSDAORA' reported and error.
OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ]
The native Oracle driver seems to work ok, and I can also connect usine Orcale SQL+ untility just fine, but of course the native driver requires the older flavor of SQL which means that all of my currently coded SPs would have to be ported. The same setup
works fine from another SQL2k server using the MS ODBC Oracle driver so I'd like to try using that, however the ODBC driver don't appear on the drop down list of driver in EM linked server screen! I tried installing MDAC 2.8 but it doesn't give me any op
tions for installing the MS ODBC drivers?...Thanks in advance for any ideas on how to resolve this!...
"Joe Loyd" <joe.loyd@.us.didata.com> wrote in message
news:570D6F0B-C76B-46FC-BF45-22D47A37128E@.microsoft.com...
> I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When
using the Microsoft OLEDB Oracle driver I get the following error:
> Error 7399: OLE DB Provider 'MSDAORA' reported and error.
> OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ]
> The native Oracle driver seems to work ok, and I can also connect usine
Orcale SQL+ untility just >fine, but of course the native driver requires
the older flavor of SQL which means that all of my >currently coded SPs
would have to be ported. The same setup works fine from another SQL2k
>server using the MS ODBC Oracle driver so I'd like to try using that,
however the ODBC driver don't >appear on the drop down list of driver in EM
linked server screen! I tried installing MDAC 2.8 but it >doesn't give me
any options for installing the MS ODBC drivers?...Thanks in advance for any
ideas >on how to resolve this!...
You want to use the MSDAORA OleDb driver, and MDAC installs it.
It looks like it's having trouble loading the Oracle OCI libraries. If the
Oracle Client is properly installed, this is usually either a permissions
problem or a path problem. Make sure that the [oracle home]\bin is in the
path. If you have to add it, or you installed the Oracle client since your
last reboot, you must reboot the server before the path change will take
effect for services (like SqlServer).
And make sure that the account running SQL has rights to the Oracle home.
David

Oracle Linked Server Error & ODBC Drivers Missing

I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When using
the Microsoft OLEDB Oracle driver I get the following error:
Error 7399: OLE DB Provider 'MSDAORA' reported and error.
OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize r
eturned 0x80004005: ]
The native Oracle driver seems to work ok, and I can also connect usine Orca
le SQL+ untility just fine, but of course the native driver requires the old
er flavor of SQL which means that all of my currently coded SPs would have t
o be ported. The same setup
works fine from another SQL2k server using the MS ODBC Oracle driver so I'd
like to try using that, however the ODBC driver don't appear on the drop dow
n list of driver in EM linked server screen! I tried installing MDAC 2.8 but
it doesn't give me any op
tions for installing the MS ODBC drivers?...Thanks in advance for any ideas
on how to resolve this!..."Joe Loyd" <joe.loyd@.us.didata.com> wrote in message
news:570D6F0B-C76B-46FC-BF45-22D47A37128E@.microsoft.com...
> I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When
using the Microsoft OLEDB Oracle driver I get the following error:
> Error 7399: OLE DB Provider 'MSDAORA' reported and error.
> OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize[/vbc
ol]
returned 0x80004005: ][vbcol=seagreen]
> The native Oracle driver seems to work ok, and I can also connect usine
Orcale SQL+ untility just >fine, but of course the native driver requires
the older flavor of SQL which means that all of my >currently coded SPs
would have to be ported. The same setup works fine from another SQL2k
>server using the MS ODBC Oracle driver so I'd like to try using that,
however the ODBC driver don't >appear on the drop down list of driver in EM
linked server screen! I tried installing MDAC 2.8 but it >doesn't give me
any options for installing the MS ODBC drivers?...Thanks in advance for any
ideas >on how to resolve this!...
You want to use the MSDAORA OleDb driver, and MDAC installs it.
It looks like it's having trouble loading the Oracle OCI libraries. If the
Oracle Client is properly installed, this is usually either a permissions
problem or a path problem. Make sure that the [oracle home]\bin is in t
he
path. If you have to add it, or you installed the Oracle client since your
last reboot, you must reboot the server before the path change will take
effect for services (like SqlServer).
And make sure that the account running SQL has rights to the Oracle home.
David

Oracle Linked Server

Hi,
I have an Oracle 9i client installed on SQL Server 2000, and I want to add a
linked Oracle server to SQL Server, I got an error says 'OLEDB error, OLEDB
error trace: initialize returned 0x80004005', and I can not view the tables
or views in the Oracle lin
ked server, can anybody help me on this?
HongHi
0x80004005 is usually a general error, have you checked the connection
string?
http://www.connectionstrings.com/
John
"Hong Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:8AB9BCEB-F6C5-4AD6-ACA2-0A3C0A8890CC@.microsoft.com...
> Hi,
> I have an Oracle 9i client installed on SQL Server 2000, and I want to add
a linked Oracle server to SQL Server, I got an error says 'OLEDB error,
OLEDB error trace: initialize returned 0x80004005', and I can not view the
tables or views in the Oracle linked server, can anybody help me on this?
> Hong|||Try turning on trace flag 7300 on or use SQL Profiler to capture the
OLEDB Errors event. This should allow you to get more information
about the error.
For additional info, you may want to refer to the following:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL
Server
http://support.microsoft.com/?id=280106
-Sue
On Wed, 2 Jun 2004 13:01:08 -0700, Hong Wang
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I have an Oracle 9i client installed on SQL Server 2000, and I want to add a linked
Oracle server to SQL Server, I got an error says 'OLEDB error, OLEDB error trace: i
nitialize returned 0x80004005', and I can not view the tables or views in the Oracle
li
nked server, can anybody help me on this?
>Hong

Oracle connection fail with Microsoft OLEDB provider for Oracle MSDAORA.1

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

Are you running it on a 64 bit system? Or are you using BLOBs?

If answer to either of them is yes, then you have a problem. Are you using Oracle as a source or destination?

|||

Hi,

I do not use BLOBs nor 64 bits. I have a 32 Bits DELL Precision worksation that I use as test server with Windows 2003 Enterprise SP1 all patches and the RTM release of SQL 2005 dev. For Oracle Client, I use the 9.i version.

I use Oracle only to read data from using select statements, I do not use linked servers.

All other Oracle providers works on that machine but the Microsoft one.

All databases were restored from the dev backup

Before rebuilding this machine, this provider was working.

After disk format and OS install, I did rebuild the machine in this order:

1) Oracle Client

2) SQL 2005

3) VS Studio 2005

4) Restore of DB's

5) Restore of sys variables and xml configurations as well as surface config

6) Restore of logins and access settings

7) VSS 2005 RTM, I take the SSIS package from there, I know it works on the other server.

Thanks,

Philippe

|||Hi,

I use "Microsoft OLE DB Provider for Oracle" and it works fine.
I remember having problems with the TNS-Entry in the connection manager
that didn't match with the TNSNAMES.ORA

Did you try the "Test connection" button in the connection config?
Do you save the pwd in the connection?
What is your package protection level? When you select don't save sensitive the pwd isn't saved.|||

Hi,

I did copy over the TNSNAME.ORA that I use in the DEV Server.

I made sure that the default domain is the same than it is in Dev in the Oracle configuration assistant and can test the connection successfully.

I do not save the sensitive in my packages, i use config files to store the connection string including the password.

This should not be relevant to this problem anyway because, if I create a new connection in the package and supply the password and then test the connection, it fails right there so the problem is at the data provider level.

Thanks,

Philippe

|||

Try changing your package to not use configuration files and instead embed the information into it.

Then try and see if it magically works.

I've been finding that using configuration files seems a bit buggy. When they run on the server it seems like sometimes SSIS can't load them causing the package to fail.

In my most recent case, I am getting the exact same error as you above and once i removed the configuration file it is now working seeming to indicate a problem loading the configuration. Since SSIS doesn't log any info that i can find about what is happening, it is virtually impossible to tell what is going on so I'm having to go with just hardcoding for now. Can't spend more time on figuring it out.

|||

This issue is very disturbing.

Yesterday, I connected again to this test server and all by magic, the Microsoft OLEDB provider was working again. I was able to create a new ssis, a new MS OLEDB For Oracle and it worked.

I then tried to run one of these packages that uses config files (and that run perfectly on the dev machine).

Same error than before.

Then, I went back to VS Studio, creatyed a new package, created a new MS OLEDB For Oracle, and it did not work. Nor did work the new package created just before trying the other older package...

I am pulling my hair on that one. The reason why I use config files is to avoid 2 major issues:
1) Package creator Password expiration every 90 days that kills all the encrypted content
2) Difficulties to quickly deploy packages from machine to machine

I will get a new production server real soon. I will then know if this problem is a reproductible bug or if it is specific to my newly rebuild test machine. I am not too optimistic...

Regards,

Philippe

|||I'm seeing this same problem as well. As soon as a configuration file is create the error starts. Even when modifying the .dtsconfig file to add the username & password then using dtexec to launch the package with the .dtsconfig file the error still occurs.|||

Could this be the problem?

http://blogs.conchango.com/jamiethomson/archive/2006/09/19/OLE-DB_3A00_-Cannot-connect-using-Microsoft-OLE-DB-Provider-for-Oracle.aspx

-Jamie

|||What helped me, too, was setting the system variable ORACLE_HOME to the oracle home directory.|||

hi,

This article was good.

Oracle connection is used to get Data in my case.

I get this error becasue package protection level was don't save sensitive the pwd.

So at execution time it says null password.

although it is not good practice to use protection level EncryptSensitiveWithPassword. but it works for my case.

Oracle connection fail with Microsoft OLEDB provider for Oracle MSDAORA.1

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

Are you running it on a 64 bit system? Or are you using BLOBs?

If answer to either of them is yes, then you have a problem. Are you using Oracle as a source or destination?

|||

Hi,

I do not use BLOBs nor 64 bits. I have a 32 Bits DELL Precision worksation that I use as test server with Windows 2003 Enterprise SP1 all patches and the RTM release of SQL 2005 dev. For Oracle Client, I use the 9.i version.

I use Oracle only to read data from using select statements, I do not use linked servers.

All other Oracle providers works on that machine but the Microsoft one.

All databases were restored from the dev backup

Before rebuilding this machine, this provider was working.

After disk format and OS install, I did rebuild the machine in this order:

1) Oracle Client

2) SQL 2005

3) VS Studio 2005

4) Restore of DB's

5) Restore of sys variables and xml configurations as well as surface config

6) Restore of logins and access settings

7) VSS 2005 RTM, I take the SSIS package from there, I know it works on the other server.

Thanks,

Philippe

|||Hi,

I use "Microsoft OLE DB Provider for Oracle" and it works fine.
I remember having problems with the TNS-Entry in the connection manager
that didn't match with the TNSNAMES.ORA

Did you try the "Test connection" button in the connection config?
Do you save the pwd in the connection?
What is your package protection level? When you select don't save sensitive the pwd isn't saved.
|||

Hi,

I did copy over the TNSNAME.ORA that I use in the DEV Server.

I made sure that the default domain is the same than it is in Dev in the Oracle configuration assistant and can test the connection successfully.

I do not save the sensitive in my packages, i use config files to store the connection string including the password.

This should not be relevant to this problem anyway because, if I create a new connection in the package and supply the password and then test the connection, it fails right there so the problem is at the data provider level.

Thanks,

Philippe

|||

Try changing your package to not use configuration files and instead embed the information into it.

Then try and see if it magically works.

I've been finding that using configuration files seems a bit buggy. When they run on the server it seems like sometimes SSIS can't load them causing the package to fail.

In my most recent case, I am getting the exact same error as you above and once i removed the configuration file it is now working seeming to indicate a problem loading the configuration. Since SSIS doesn't log any info that i can find about what is happening, it is virtually impossible to tell what is going on so I'm having to go with just hardcoding for now. Can't spend more time on figuring it out.

|||

This issue is very disturbing.

Yesterday, I connected again to this test server and all by magic, the Microsoft OLEDB provider was working again. I was able to create a new ssis, a new MS OLEDB For Oracle and it worked.

I then tried to run one of these packages that uses config files (and that run perfectly on the dev machine).

Same error than before.

Then, I went back to VS Studio, creatyed a new package, created a new MS OLEDB For Oracle, and it did not work. Nor did work the new package created just before trying the other older package...

I am pulling my hair on that one. The reason why I use config files is to avoid 2 major issues:
1) Package creator Password expiration every 90 days that kills all the encrypted content
2) Difficulties to quickly deploy packages from machine to machine

I will get a new production server real soon. I will then know if this problem is a reproductible bug or if it is specific to my newly rebuild test machine. I am not too optimistic...

Regards,

Philippe

|||I'm seeing this same problem as well. As soon as a configuration file is create the error starts. Even when modifying the .dtsconfig file to add the username & password then using dtexec to launch the package with the .dtsconfig file the error still occurs.|||

Could this be the problem?

http://blogs.conchango.com/jamiethomson/archive/2006/09/19/OLE-DB_3A00_-Cannot-connect-using-Microsoft-OLE-DB-Provider-for-Oracle.aspx

-Jamie

|||What helped me, too, was setting the system variable ORACLE_HOME to the oracle home directory.|||

hi,

This article was good.

Oracle connection is used to get Data in my case.

I get this error becasue package protection level was don't save sensitive the pwd.

So at execution time it says null password.

although it is not good practice to use protection level EncryptSensitiveWithPassword. but it works for my case.

Oracle connection fail with Microsoft OLEDB provider for Oracle MSDAORA.1

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

Are you running it on a 64 bit system? Or are you using BLOBs?

If answer to either of them is yes, then you have a problem. Are you using Oracle as a source or destination?

|||

Hi,

I do not use BLOBs nor 64 bits. I have a 32 Bits DELL Precision worksation that I use as test server with Windows 2003 Enterprise SP1 all patches and the RTM release of SQL 2005 dev. For Oracle Client, I use the 9.i version.

I use Oracle only to read data from using select statements, I do not use linked servers.

All other Oracle providers works on that machine but the Microsoft one.

All databases were restored from the dev backup

Before rebuilding this machine, this provider was working.

After disk format and OS install, I did rebuild the machine in this order:

1) Oracle Client

2) SQL 2005

3) VS Studio 2005

4) Restore of DB's

5) Restore of sys variables and xml configurations as well as surface config

6) Restore of logins and access settings

7) VSS 2005 RTM, I take the SSIS package from there, I know it works on the other server.

Thanks,

Philippe

|||Hi,

I use "Microsoft OLE DB Provider for Oracle" and it works fine.
I remember having problems with the TNS-Entry in the connection manager
that didn't match with the TNSNAMES.ORA

Did you try the "Test connection" button in the connection config?
Do you save the pwd in the connection?
What is your package protection level? When you select don't save sensitive the pwd isn't saved.
|||

Hi,

I did copy over the TNSNAME.ORA that I use in the DEV Server.

I made sure that the default domain is the same than it is in Dev in the Oracle configuration assistant and can test the connection successfully.

I do not save the sensitive in my packages, i use config files to store the connection string including the password.

This should not be relevant to this problem anyway because, if I create a new connection in the package and supply the password and then test the connection, it fails right there so the problem is at the data provider level.

Thanks,

Philippe

|||

Try changing your package to not use configuration files and instead embed the information into it.

Then try and see if it magically works.

I've been finding that using configuration files seems a bit buggy. When they run on the server it seems like sometimes SSIS can't load them causing the package to fail.

In my most recent case, I am getting the exact same error as you above and once i removed the configuration file it is now working seeming to indicate a problem loading the configuration. Since SSIS doesn't log any info that i can find about what is happening, it is virtually impossible to tell what is going on so I'm having to go with just hardcoding for now. Can't spend more time on figuring it out.

|||

This issue is very disturbing.

Yesterday, I connected again to this test server and all by magic, the Microsoft OLEDB provider was working again. I was able to create a new ssis, a new MS OLEDB For Oracle and it worked.

I then tried to run one of these packages that uses config files (and that run perfectly on the dev machine).

Same error than before.

Then, I went back to VS Studio, creatyed a new package, created a new MS OLEDB For Oracle, and it did not work. Nor did work the new package created just before trying the other older package...

I am pulling my hair on that one. The reason why I use config files is to avoid 2 major issues:
1) Package creator Password expiration every 90 days that kills all the encrypted content
2) Difficulties to quickly deploy packages from machine to machine

I will get a new production server real soon. I will then know if this problem is a reproductible bug or if it is specific to my newly rebuild test machine. I am not too optimistic...

Regards,

Philippe

|||I'm seeing this same problem as well. As soon as a configuration file is create the error starts. Even when modifying the .dtsconfig file to add the username & password then using dtexec to launch the package with the .dtsconfig file the error still occurs.|||

Could this be the problem?

http://blogs.conchango.com/jamiethomson/archive/2006/09/19/OLE-DB_3A00_-Cannot-connect-using-Microsoft-OLE-DB-Provider-for-Oracle.aspx

-Jamie

|||What helped me, too, was setting the system variable ORACLE_HOME to the oracle home directory.|||

hi,

This article was good.

Oracle connection is used to get Data in my case.

I get this error becasue package protection level was don't save sensitive the pwd.

So at execution time it says null password.

although it is not good practice to use protection level EncryptSensitiveWithPassword. but it works for my case.

Oracle connection fail with Microsoft OLEDB provider for Oracle MSDAORA.1

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

Are you running it on a 64 bit system? Or are you using BLOBs?

If answer to either of them is yes, then you have a problem. Are you using Oracle as a source or destination?

|||

Hi,

I do not use BLOBs nor 64 bits. I have a 32 Bits DELL Precision worksation that I use as test server with Windows 2003 Enterprise SP1 all patches and the RTM release of SQL 2005 dev. For Oracle Client, I use the 9.i version.

I use Oracle only to read data from using select statements, I do not use linked servers.

All other Oracle providers works on that machine but the Microsoft one.

All databases were restored from the dev backup

Before rebuilding this machine, this provider was working.

After disk format and OS install, I did rebuild the machine in this order:

1) Oracle Client

2) SQL 2005

3) VS Studio 2005

4) Restore of DB's

5) Restore of sys variables and xml configurations as well as surface config

6) Restore of logins and access settings

7) VSS 2005 RTM, I take the SSIS package from there, I know it works on the other server.

Thanks,

Philippe

|||Hi,

I use "Microsoft OLE DB Provider for Oracle" and it works fine.
I remember having problems with the TNS-Entry in the connection manager
that didn't match with the TNSNAMES.ORA

Did you try the "Test connection" button in the connection config?
Do you save the pwd in the connection?
What is your package protection level? When you select don't save sensitive the pwd isn't saved.
|||

Hi,

I did copy over the TNSNAME.ORA that I use in the DEV Server.

I made sure that the default domain is the same than it is in Dev in the Oracle configuration assistant and can test the connection successfully.

I do not save the sensitive in my packages, i use config files to store the connection string including the password.

This should not be relevant to this problem anyway because, if I create a new connection in the package and supply the password and then test the connection, it fails right there so the problem is at the data provider level.

Thanks,

Philippe

|||

Try changing your package to not use configuration files and instead embed the information into it.

Then try and see if it magically works.

I've been finding that using configuration files seems a bit buggy. When they run on the server it seems like sometimes SSIS can't load them causing the package to fail.

In my most recent case, I am getting the exact same error as you above and once i removed the configuration file it is now working seeming to indicate a problem loading the configuration. Since SSIS doesn't log any info that i can find about what is happening, it is virtually impossible to tell what is going on so I'm having to go with just hardcoding for now. Can't spend more time on figuring it out.

|||

This issue is very disturbing.

Yesterday, I connected again to this test server and all by magic, the Microsoft OLEDB provider was working again. I was able to create a new ssis, a new MS OLEDB For Oracle and it worked.

I then tried to run one of these packages that uses config files (and that run perfectly on the dev machine).

Same error than before.

Then, I went back to VS Studio, creatyed a new package, created a new MS OLEDB For Oracle, and it did not work. Nor did work the new package created just before trying the other older package...

I am pulling my hair on that one. The reason why I use config files is to avoid 2 major issues:
1) Package creator Password expiration every 90 days that kills all the encrypted content
2) Difficulties to quickly deploy packages from machine to machine

I will get a new production server real soon. I will then know if this problem is a reproductible bug or if it is specific to my newly rebuild test machine. I am not too optimistic...

Regards,

Philippe

|||I'm seeing this same problem as well. As soon as a configuration file is create the error starts. Even when modifying the .dtsconfig file to add the username & password then using dtexec to launch the package with the .dtsconfig file the error still occurs.|||

Could this be the problem?

http://blogs.conchango.com/jamiethomson/archive/2006/09/19/OLE-DB_3A00_-Cannot-connect-using-Microsoft-OLE-DB-Provider-for-Oracle.aspx

-Jamie

|||What helped me, too, was setting the system variable ORACLE_HOME to the oracle home directory.|||

hi,

This article was good.

Oracle connection is used to get Data in my case.

I get this error becasue package protection level was don't save sensitive the pwd.

So at execution time it says null password.

although it is not good practice to use protection level EncryptSensitiveWithPassword. but it works for my case.