Thursday, September 22, 2011

Configure MS SQL database as a Physical Data Source in Oracle DAC

How to use/ configure MS SQL database as a physical datasource in Oracle DAC?

The general approach in Oracle DAC is to use Oracle databases are physical data sources but once in a while we come across datawarehouse environments which have MS SQL as sources/ targets. Configuring MS SQL physical data source is a little different than Oracle db.

Follow these steps

  1. Download sqljdbc4.jar file (many web sources available) and place it in the ..bifoundation\dac\lib folder
  2. Modify the file config.bat in folder ..bifoundation\dac . Set SQLSERVERLIB=.\lib\msbase.jar;.\lib\mssqlserver.jar;.\lib\msutil.jar;.\lib\sqljdbc4.jar
  3. Open DAC Client and go to Setup -> Physical Data Sources. Create a new physical data source for MS SQL
  4. Enter the following details. Name = <source name>, Type = <type of db> , Connection type = MSSQL, Database name = master (default), table owner = <db owner>, password = <password>, DB Host= <machine name\SQLEXPRESS> . URL =  jdbc:sqlserver://<hostname:port>;databaseName=<schema name> Eg: jdbc:sqlserver://client39:3617;databaseName=datawarehouse1


No comments:

Post a Comment