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)

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.