Troubleshooting ODI connectivity to SQL Server Express 2005

Uli Bethke Oracle Data Integrator (ODI)

It took me an hour or two today to connect to an SQL Server Express 2005 instance. This was mainly due to some default settings in SQL Server Express 2005.

Here is what you need to do to get this to work.

1. Make sure that SQL Server and SQL Server Browser services are started

Go to Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager > SQL Server 2005 Services

Start any services that are stopped

2. Remote connections need to be enabled

Go to Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration > Remote Connections

Make sure that the Local and remote connections radio button is selected:

odi_mssql1

3. JDBC driver not installed in ODI drivers folder

Download the driver from: http://www.microsoft.com/downloads/details.aspx?FamilyID=99b21b65-e98f-4a61-b811-19912601fdc9&displaylang=en

As far as I understand it you need to download the JDBC 1.2 driver. The JDBC 2.0 driver does not support JRE 1.4.2.

Next stick the sqljdbc.jar file into the odi drivers folder: oracledidrivers

4. Set TCP port for IP addresses

Go to Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager > SQL Server 2005 Network Configuration > Right Click TCP/IP > IP addresses tab

odi_mssql2

5. Configure Data Server in ODI

In the screenshot below we are connecting the Data Server to the Adventure Works sample database (via Topology Manager). Fill out the fields as per screenshot.

Note: In a production environment you should not use the sa user to connect to a SQL Server database. Instead create a separate user for each database.

odi_mssql3

Change to the JDBC tab

JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC URL: jdbc:sqlserver://;selectMethod=cursor;databaseName=;integratedSecurity=false

odi_mssql4

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O'Reilly Scripting)