SSIS 2008 - getting data from Oracle - oledb drivers issue
Hi All,
I am currently wanting to build a package to extract data from Oracle 9i to SQL Server 2008 R2. I started by going to the start menu and then selecting "Import and Export Data (64-bit)" option from SQL 2008 menu items. I connect my source to "Oracle Provider for OLE DB" I enter data source and my credentials and connectivity tests OK. I proceed to my SQL Server destination select my table to extract and everything works just fine. All rows transferred. I also saved the package to use it inside of BIDS.
Now the problem. I open a new project and import my newly created package into a new solution. When I try to check on my Oracle "SourceConnectionOLDB" I get the following message: "The specific provider is not supported" and also when I test the connection I get "The 'OraOLEDB.Oracle.1' provider is not registered on the local machine."
Another interesting issue is when I go to my solution and attempt to add datasources I do not see in the drop down the source "Oracle Provider for OLE DB" that I get to see when I run "Import and Export Data (64-bit)" option from the start menu.
I can connect fine and get my data from Oracle using "Import and Export Data (64-bit)" but when I use that same package from BIDS I get all the connection errors.
Any ideas on how to get BIDS to see the proper "Oracle Provider for OLE DB"?
My systems:
Windows Server 2008 R2 ENT
SQL Server 2008 R2 ENT
Oracle 11g 11.2 64-bit client