Accessing a mapped network drive with the ODI agent as a Windows service

Uli Bethke Oracle Data Integrator (ODI)

The problem

The other day we had a requirement to extract data from an Access database on a mapped network drive. For reasons too complicated to outline here, we could not use a UNC.

While there is no problem to access the mapped drive from the Designer module using the Local Agent, things don't look so good if you are using an agent running as a Windows service.

The error we got was as follows:

-1023 : S1009 : java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] 'T:xxxxxx.mdb' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

What is the issue?

This is basically a limitation of running programs as a Windows service

Below is an extract from INFO: Services and Redirected Drives from Microsoft support

If a service is configured to run under a user account, the system will always create a new logon session for the user and then launch the service in that new logon session. Thus, the service cannot manipulate the drive mappings that are established within the user's other session(s).

Microsoft recommned to only use UNCs to access resources on network drives when using a Windows service.

The workaround

So what if you can't use a UNC? To workaround the problem you can map your network drive on the fly in your specific session.

How does this work in ODI?

We simply create a mapped drive in an ODI procedure or as part of a KM that uses the net use command.

NET USE t: \<server>ap$ /USER:<Domain><User> <Password>

Make sure to tick the Ignore Errors checkbox in your procedure step. The mapped drive exists for the lifetime of the agent, i.e. until it is restarted.