Thursday, June 26th, 2008
5 Comments

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 13170 times by 3787 visitors


Related posts:

  1. How to connect a PeopleSoft schema to ODI
  2. Integration of Oracle Apps and XML Publisher
  3. PeopleTools 8.50 Hints & Tips: Configuring Report Manager
  4. The problem of waiting time when launching a unix shell command from an application engine
  5. Errors with nVision reports running via the web on a Windows 2008 process scheduler
Category: Fusion / Technical
Tags: ,
Frank KortekaasPosted 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.

5 Responses (last comment shown first)

February 18, 2010
trt
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]

Frank Kortekaas

Frank Kortekaas Reply:
February 22nd, 2010 at 8:47 am

Hi,

are you trying to install the Work repository or the Master repository?

Regards,
Frank

[Reply to this comment]


July 20, 2009
Roman
Roman

thanks! Very helpfull especially about the JDBC driver field…you save me time! 8)

[Reply to this comment]


June 18, 2009
Shabbir.Ahmed
Shabbir.Ahmed

Hi All
can anybody help me for reversive engg .it is giving me the error for the network adeptor

[Reply to this comment]


January 22, 2009
rajesh valluri
rajesh valluri

Thanks a lot, this simple step could have saved me a lot of grief had I known it sooner.

[Reply to this comment]