Configuring Connection from MS SQL 2005 (x64) to Oracle 10g (x64) on Windows 2003 Server (x64)

These 3 links have helped me to configure access to Oracle from MS SQL on Windows 2003 Server (x64):
Troubleshooting Oracle Publishers
Linked server from MSSQL2005 64 bit to Oracle (in Russian)
SSIS & Oracle (in Russian)

Now, here are the steps:

1. Download and install Oracle Data Access Components (ODAC) x64.
2. Place correct sqlnet.ora and tnsnames.ora to the network\admin folder of the ODAC home.
3. Run Oracle Universal Installer, select Installed Products->Environment and place the ODAC home to the top.
4. Restart the computer.
5. Open SQL Server Management Studio
- Provide the Server Name, Authentication, Username and Password to connect to your SQL Server at the dialog box. This will authenticate you and log you into Management Studio.
- Expand the Server Objects folder by clicking on the + sign
- Now, expand the Linked Servers folder and you will see Providers folder. Expand the Providers folder. You will see Oracle Provider for OLEDB. Right click on it and go to Properties. Check the check boxes for "Dynamic Parameters" and "Allow InProcess". Click OK and the dialogue box will close.
- Next, right click on the Linked Servers folder and choose New Linked Server
- In the New Linked Server Properties dialogue box, do the following:
----- Type in a Linked Server Name, for this example, we will call it 'Tiger'
----- Under Server Type, select Other Data Sources - for the provider name, select Oracle Provider for OLEDB
----- In the Data Source field, type in the Oracle Service name (SQL Net Alias)
----- In the Product Name field, type in "Oracle"
----- Leave Provider String blank.
- Now, click on the "Security option" on the left pane.
----- Select the radio button "Be made using this security context" at the bottom of the box.
----- Type in the User ID (your Oracle login), ie. Scott in the "Remote login" field and your Password, ie. Tiger in the "With Password" field.
- Go to the "Server Options" tab in the Linked Server Properties.
----- While leaving the default selections selected, also click on "Collation Compatible". Select "true" from the drop down button.
----- Click OK. This will create your Linked Server named Tiger.
6. Verify that the registry settings at
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI and
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI
are correct:
OracleOciLib = oci.dll
OracleSqlLib = orasql10.dll
OracleXaLib = oraclient10.dll
7. If the Oracle OLEDB provider is installed, make sure that it is registered. To register the provider DLL, execute the following command from the directory in which the DLL is installed, and then stop and restart the SQL Server instance:
regsvr32 OraOLEDB10.dll or regsvr32 OraOLEDB.dll.

Now we are able to work with Oracle from MS SQL, and it's fine, but... on one of my hosts I've got the next problem: the Oracle linked server in MS SQL Database Engine works ok, connection from SSAS to Oracle works ok, but Oracle connection in SSIS packages doesn't work! So, let's do the next steps:

as the x64 ODAC driver doesn't work with SSIS in x64, I've tried to make ODAC working with SSIS in x32 subsystem.

Steps:
1. Download and install by default ODTwithODAC1020221 (x32).
2. BIDS, DTExecUI, DTExec are started with next command files:

set path=C:\oracle\product\10.2.0\client_odac_2_x32\BIN;%path%;
"C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE\devenv.exe"

set path=C:\oracle\product\10.2.0\client_odac_2_x32\BIN;%path%;
"C:\Progra~2\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\DTExecUI.exe"

set path=C:\oracle\product\10.2.0\client_odac_2_x32\BIN;%path%;
c:
cd \
cd "C:\PROGRA~2\Microsoft SQL Server\90\DTS\Binn"
DTExec /Server SERVERNAME /SQL "Test Oracle Driver Package" /Reporting E

In x32 subsystem all works.

About This Blog

I'll post here interesting and important things about Business Intelligence solutions based on the Microsoft BI stack (SQL Server, SSAS, SSIS, SSRS, .NET Framework and C#, Windows Server). I use MS SQL Server and Oracle Database as a data warehouse database platform.

News

July 12, 2007
CTP3 of Microsoft Office PerformancePoint Server 2007 with new Monitoring and Analytics functionality is now available!
You can download the files within the "Download" section of Microsoft Connect (https://connect.microsoft.com/default.aspx). Prior to getting access to the download page you must take the mandatory survey (if you haven't already) and agree to the Licensing terms. Additional product information about Office PerformancePoint Server 2007 can be found at www.microsoft.com/performancepoint.

June 30, 2007
Microsoft® Visual Studio® 2005 Service Pack 1 is available.

June 12, 2007
Updated SQL Server 2005 Books Online (May 2007) are available for download here.

June 04, 2007
SQL Server 2008 (Katmai) Community Technology Preview (CTP) is available for download here.

March 29, 2007
SQL Server 2005 Analysis Services Performance Guide goes live!

March 06, 2007
SQL Server 2005 Service Pack 2 is available for download here.

Best Practices and White Papers

Analysis Services
SQL Server 2005 Analysis Services Performance Guide
OLAP Design Best Practices for Analysis Services 2005
Analysis Services Processing Best Practices
Real-time Business Intelligence Using Analysis Services
Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services
Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques

SQL Server Data Warehousing
SQL Server 2005 Data Warehousing
Business Intelligence and Data Warehousing in SQL Server 2005
SQL Server 2005 Data Warehousing White Papers
Strategies for Partitioning Ralational Data Warehouses in Microsoft SQL Server
Choosing Microsoft SQL Server 2005 for Data Warehousing
High Performance Data Warehouse with SQL Server 2005
Manage Large Data Warehouses with SQL Server 2005

Documentation and Info

SQL Server Home
Microsoft SQL Server 2005 Home
Microsoft SQL Server 2008 Home

SQL Server Analysis Services
MSDN: SQL Server Analysis Services Books Online
MSDN: SQL Server Analysis Services Tutorials
TechNet: SQL Server 2005 – Analysis Services

SQL Server Integration Services
SQLIS.com - SQL Server Integration Services
MSDN: Integration Services
MSDN Forum: SQL Server Integration Services

SQL Server Database Engine
MSDN: SQL Server Database Engine Books Online
TechNet: SQL Server 2005 – Database Engine

Other
MSDN: SQL Server 2005
MSDN: SQL Server 2005 Books Online
SQL Server 2005 Project REAL
Microsoft BI and PerformancePoint Server
Microsoft Office PerformancePoint Server
The many-to-many revolution by Marco Russo

Oracle
Oracle Database Online Documentation 10g Release 2 (10.2)

Literature

Here are links to some useful books.
I've chosen all these books as very helpful for developing Business Intelligence solutions on the MS BI Platform.

Data Warehousing and ETL
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling, 2nd Edition by Ralph Kimball, Margy Ross
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data by Ralph Kimball, Joe Caserta
The Data Warehouse Lifecycle Toolkit: Tools and Techniques for Designing, Developing, and Deploying Data Warehouses by Ralph Kimball, Laura Reeves, Margy Ross and Warren Thornthwaite

Microsoft BI
The Microsoft Data Warehouse Toolkit: With SQL Server 2005 and the Microsoft Business Intelligence Toolset by Joy Mundy, Warren Thornthwaite, Ralph Kimball
Microsoft SQL Server 2005 Analysis Services (SQL Server Series) by Edward Melomed, Irina Gorbach, Alexander Berger, Py Bateman
Applied Microsoft Analysis Services 2005: And Microsoft Business Intelligence Platform by Teo Lachev
Microsoft SQL Server 2005 Integration Services (SQL Server Series) by Kirk Haselden
Fast Track to MDX by Mark Whitehorn, Robert Zare, Mosha Pasumansky
MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase by George Spofford, Sivakumar Harinath, Christopher Webb, Dylan Hai Huang, Francesco Civardi
Data Mining with SQL Server 2005 by ZhaoHui Tang, Jamie MacLennan

Microsoft SQL Server
Inside Microsoft SQL Server 2005: The Storage Engine by Kalen Delaney
Inside Microsoft SQL Server 2005: T-SQL Programming (Pro-Developer) by Itzik Ben-gan, Dejan Sarka, Roger Wolter
Inside Microsoft SQL Server 2005: T-SQL Querying (Solid Quality Learning) by Itzik Ben-Gan, Lubor Kollar, Dejan Sarka

.NET Framework and C#
CLR via C#, Second Edition (Pro Developer) by Jeffrey Richter