Unable to select Oracle as a Target in ARS Sync service

I took a different approach and actually installed the Oracle client on my Sync Service host and use ODBC to get to Oracle.

There is an ODBC data source setup on my Sync Service host that uses the Oracle driver and points to the MyApplicationName settings described below.

FWIW, the connection string (configured in an ODBC Connector in the Sync Service) looks like this:

Driver={Oracle in OraHome1};Dbq=DatabaseName;Uid=ARSQC;Pwd=**********;

The file TNSNAMES.ORA found in this folder (created by the Oracle client setup):

D:\app\MySyncServiceAccount\OraHome_1\Network\Admin\Sample

This file location must be setup in an environment variable TNS_ADMIN

The TNSNAMES.ORA file contains additional settings required to make the database connection.  In my environment, the internals look like this:

(I've changed some of the names to protect the innocent):

MyApplicationName = 

(Description =

(Address = (Protocol = TCP)(Host = xxx.xxx.xxx.xxx)(PORT = xxxxx) (CONNECT DATA = 

(SERVER = DEDICATED)

(SERVICE_NAME = MyDatabaseName)

)

)

Note that since the Connection string contains credentials, those that exist in the ODBC data source are ignored.

Is this complicated?  Yep.  But I hope someone finds it helpful.

I took a different approach and actually installed the Oracle client on my Sync Service host and use ODBC to get to Oracle.

There is an ODBC data source setup on my Sync Service host that uses the Oracle driver and points to the MyApplicationName settings described below.

FWIW, the connection string (configured in an ODBC Connector in the Sync Service) looks like this:

Driver={Oracle in OraHome1};Dbq=DatabaseName;Uid=ARSQC;Pwd=**********;

The file TNSNAMES.ORA found in this folder (created by the Oracle client setup):

D:\app\MySyncServiceAccount\OraHome_1\Network\Admin\Sample

This file location must be setup in an environment variable TNS_ADMIN

The TNSNAMES.ORA file contains additional settings required to make the database connection.  In my environment, the internals look like this:

(I've changed some of the names to protect the innocent):

MyApplicationName = 

(Description =

(Address = (Protocol = TCP)(Host = xxx.xxx.xxx.xxx)(PORT = xxxxx) (CONNECT DATA = 

(SERVER = DEDICATED)

(SERVICE_NAME = MyDatabaseName)

)

)

Note that since the Connection string contains credentials, those that exist in the ODBC data source are ignored.

Is this complicated?  Yep.  But I hope someone finds it helpful.

Johnny,

Thanks for the tips I will give this a try. Complicate a bit but, hey as long as it works it works.

Appreciate your guidance.

Lu

I was able to make a Oracle connection and map it to a SQL table.

Although when I go to create the workflow I choose SQL as the Source and want to choose OLEDB as target  i cannot see the OLEDB as a Target option.

I can however make OLEDB the source and SQL the target so that is weird. 

The Admin guide states that OLEDB connections are read only so any connection of this type can never be selected as a Target system.

Nice catch!!!!!