Connecting Oracle Data Integrator and MS SQL Server

1. Refer to this Download link for Microsoft SQL Server 2005 JDBC Driver 1.2

http://www.microsoft.com/downloads/details.aspx?FamilyId=C47053EB-3B64-4794-950D-81E1EC91C1BA&displaylang=en

and for Microsoft SQL Server JDBC Driver 2.0 here

http://www.microsoft.com/downloads/details.aspx?familyid=99B21B65-E98F-4A61-B811-19912601FDC9&displaylang=en

JDBC Driver 1.2

Extract the downloaded .exe or .gz and copy jar file ” sqljdbc.jar” into the $ODI_HOMEDRIVERS folder

Now go to the topology and Under the Physical architecture of Microsoft SQL Server. Define a New Data Server with the required Name , User connection.

Go the Next JDBC tab , enter the JDBC Driver and JDBC Url

JDBC Driver : com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC Url : jdbc:sqlserver://<Server address> :<Default port=1433 >selectMethod=cursor
JDBC Url : jdbc:sqlserver://<Server address> :<Default port=1433 >selectMethod=cursor;responseBuffering=adaptive

The above command works great with both SQL Server 2005 and for SQLServer 2000 try this

JDBC Driver : com.microsoft.jdbc.sqlserver.SQLServerDriver
JDBC Url : jdbc:microsoft:sqlserver://<Server address> :<Default port=1433 >selectMethod=cursor

Learn more about option like selectMethod=cursor and responseBuffering=adaptive here http://msdn.microsoft.com/en-us/library/bb879937.aspx and other connection strings here http://msdn.microsoft.com/en-us/library/ms378428.aspx.

To learn more about the Url Option check the ” Release ” document available with extracted .exe or . gz

Click Test to test the connection and Select the appropriate database and owner for Physical Schema and define the appropriate Logical Schema.

Note for ‘ Owner ( Schema )‘ Select the right owner of the database , mostly ” dbo ” . check with your SQL Server DBA for right schema owner

For the Work schema owner , the right user who have permission to access the schema has to be selected.

JDBC Driver 2.0

JDBC Driver ver 2.0 works only with JDK or JRE 1.5 or greater and as ODI is shipped with 1.4 so you would surely encounter the error.

To Resolve this issue download Jdk or Jre 1.5 or greater and change the ODI_JAVA_HOME accordingly and point to the new JAVA_HOME,

Sql Server JDBC Driver is shipped with both ‘sqljdbc‘ and ‘sqljdbc4‘ .To use JDBC Type 4 driver copy only ‘ sqljdbc4‘ in the ‘ oracledi/drivers ‘ folders .

[ Note : Please refer to the release document – point number 1 under Known Issues ]

Copy the ‘ sqljdbc4 ‘driver and test the connection. Restart the agent and try the same Driver and URL as shown above and refer to its ‘ Release ‘ document for more details.

Learn more about  SQL JDBC driver from your extracted driver folder at  ‘ enuhelpindex.html ‘ or access all these information online http://msdn.microsoft.com/en-us/library/ee229547%28SQL.10%29.aspx