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.
Uli Bethke
Uli has been rocking the data world since 2001. As the CEO of Sonra, the data liberation company, he’s on a mission to set data free. A self-proclaimed data evangelist, Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker. Uli is a co-founder of DAMA Ireland, helping build a thriving community of data enthusiasts, and a proud Snowflake Data Superhero, leading the charge as the chapter lead of the Snowflake User Group Ireland. Whether sharing insights, empowering others, or championing the coolest data tools, Uli makes data fun. Growing up in Germany and making Ireland his home, Uli brings a global perspective to everything he does. On the academic side, he holds degrees in political science from Freie Universität Berlin, Albert Ludwigs Universität Freiburg, and the University of Ulster, Coleraine, adding a unique interdisciplinary flair to his work.
