Subscribe to BAOA

Thursday, June 26th, 2008

How to connect an SQL Server 2005 db to ODI

I’ve been busy trying to get Oracle Data Integration to work. First step after installation is to connect one or more data sources using the Topology Manager. Although ODI is a fairly new product, it only comes with a SQL Server 2000 driver.

To install the SQL Server 2005 JDBC driver and use it to connect to the database you need to follow the following steps:

Step 1: download JDBC driver

Go to the Microsoft site and download the latest JDBC driver for SQL Server 2005. Check the following URL: http://msdn.microsoft.com/en-us/data/aa937724.aspx. You will get a zip file. Unzip it and place the sqljdbc.jar file into the %ODI_HOME%\drivers directory (where %ODI_HOME% of course is the directory where you installed the Oracle DataIntegration application).

Step 2: Define Data Server

Next, start the Topology Manager and in the Physical Architecture tab right-click the SQL Server tree node. Select the InsertData Server option and fill in the first tab (Definition). Now go to the second tab (JDBC). DO NOT select a JDBC driver using the browse button on the right! You can only select the SQL Server 2000 driver from here.

Instead, type the following string directly into the JDBC driver field: com.microsoft.sqlserver.jdbc.SQLServerDriver. Note that this string is slightly different from the SQL Server 2000 driver, which is com.microsoft.jdbc.sqlserver.SQLServerDriver.

And finally for the JDBC URL use the following format: jdbc:sqlserver://<host>:<port>, where 1433 is the default SQL Server port.

Press the Test button and voila!

Viewed 24151 times by 6702 visitors

Category: Fusion / Technical

Tags: ODI, SQL Server

Posted by: Frank Kortekaas (8 post(s)).

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

9 Responses (last comment shown first)

February 29, 2012

Kash

Hello,

I wanted to extract data from Microsoft SQL Server Analysis Services cubes using ODI?
Is it possible? If yes, Pls let me know the steps

Thanks

[Reply to this comment]

August 20, 2011

srinath

Hi Frank,

ive installed ODI 10g and trying to connect it with my SQL server 2005. Ive even downloaded jdbc driver 3.0 and placed the jar file in the proper location. But still am not able to go and create a master repository to work with.
In ur procedure, the second step uve asked to go to topology manager and work with inserting a data server. But am not able to login to topology manager on the first hand. please do help me out.

Thanks

[Reply to this comment]

June 13, 2011

jojibabu

Hi,
I am new to ODI, I installed ODI11g, now challenge is Source will be Oracle CRM onDemand and need to use ODI to extract the data using webservices and load to Oracle Warehouse.
Can anyone help me, how to start…

[Reply to this comment]

February 18, 2010

trt

I am new to ODI and I have just installed ODI 10.1.3 and when I get to step 2 I am prompted immediately with “Security Repository Connections” which has a drop down with only one choice “Oracle”. There is a “new repository connection” which when selected presents the following :

orcl Data Integrator Connection
login name:
user:
pw:

Database connection(master repository)
user: sa
pw: xxxxx
driver list: user defined
driver name: com.microsoft.sqlserver.jdbc.SQLServerDriver
url: jdbc:sqlserver://ip address:1433

Any help would be greatly appreciated.

[Reply to this comment]