Troubleshooting ODI connectivity to SQL Server Express 2005
Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
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:
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
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.
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.
Change to the JDBC tab
JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC URL: jdbc:sqlserver://;selectMethod=cursor;databaseName=;integratedSecurity=false
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)
