Oracle drivers in connection managers

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?This might be a novice answer, but are you sure the actual OLE driver is installed? I'm no Oracle expert by far, but the client and the actual driver that my Oracle dba gives for sql server installation are on seperate disks.|||Yes, the oledb drivers are installed. One important thing I failed to mention is that this is a 64 bit server. What appears to be happening is that even though we can create a linked server with the 64 bit oracle oledb driver, when you create a package in ssis it only lists the 32 bit native oledb drivers. I confirmed this by installing the 32 bit oracle client stuff as well and now the oracle oledb driver is listed in the drop downs. This seems a little absurd that ssis would only use the 32 bit drivers, can anyone explain this?|||

"The 32-bit SSIS Designer displays only 32-bit providers that are installed on the local computer. If you want to select a specific 64-bit provider to configure a connection manager, you must have the 32-bit version of the provider installed. However, you can still run the package in 64-bit mode in the development environment. Because the 32-bit and 64-bit versions of a provider have the same ID, the runtime selects the appropriate version to use based on the value of the Run64BitRuntime project property, which by default is True."

Found in this article....

http://msdn2.microsoft.com/en-us/library(d=robot)/ms141766.aspx

|||

Thanks for the info, that's a big help.

I understand why they did this but just like every other weird x64 quirk you have to spend hours digging through various documentation sources to even find a mention of it. Please just give us a comprehensive x64 guide instead of spreading stuff out in a thousand different places.

oracle connection using configuration file

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

For an OLE-DB connection the value of the config file property assignment, for connection string, would look something like this-

Data Source=ABC.XYZ;User ID=UserName;Password=secret;Provider=MSDAORA.1;Persist Security Info=True;

ABC.XYZ is the alias from tnsnames.

|||

Thanks Darren...

I exactly have the same line as you suggested. The issue was with InitialCatalog propterty. I took that out from configuration file and it worked fine.

I read somewhere only the servername, userid and password property is enough to make a connection to oracle database.

Thanks

Mahesh

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.