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

Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes

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.