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.

odi_mapped_drive

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.