Issue with SSIS using Timberline Datasource

I'm trying to schedule a SSIS job to run nightly using Timberline as a datasource.  Here's my issue:

The Timberline ODBC driver has a constraint which will only allow the use of drive letters when pointing to its source.  The driver will not accept a UNC path.  Because of this constraint, the package has to be run when a user is logged in.  When run as either a scheduled system task or a SQL agent job, the package fails because the package does not have access to the mapped drives (N and M).  The login script which maps the drives are only executed when a user logs in.  

Heres a couple things I tried already:

1. Hardcode the connection string (Driver=Timberline Data;DBQ=//UNC Path/;UID=XX;PWD=XXXX) within the package to use the UNC path to the Timberline data.   --> There is still something within Timberline which points to od.scr in M:\Accounting\Screen (This is true if I copy down the data locally)

2. Create a windows service in my test environment here to map the drive when Windows  load.  
--> Same issue.  The script used to map the drive by the Service will not run unless someones logged in.

3.  Tried  creating a DSN using the Pervasive driver but because Timberline does not use DDF files (from what I can tell), this was not possible..
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

have you tried using a credential & proxy for the ssis task that does have access to the mapped drives?
Steve HoggITCommented:
Can you map the drives on the SQL Server where the Agent is running?
hchan_resolveAuthor Commented:
Permissions is not the issue.  When the job is scheduled, the user impersonating the SQL Agent service does not have access to the mapped drives because the login script is not run.  
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

hchan_resolveAuthor Commented:
Sure... the drives is mapped when I'm logged in and the SSIS package runs sucessfully.  The problem is when the job is scheduled to run and a user is not logged in to run it manually.
hchan_resolveAuthor Commented:
This is the error I get if I try to run the job when the connection string is hardcoded to either a local drive or a unc path:

TITLE: Package Validation Error

Package Validation Error


Error at COMPANY_INFORMATION [DataReader Source [150]]: System.Data.Odbc.OdbcException: ERROR [S1000] [TimberlineODBC][TimberlineODBCEngine ODBC Driver][DRM File Library]Drive or directory unavailable [IO-WIN 3]
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
   at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.Odbc.OdbcConnection.Open()
   at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
   at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
   at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)
Error at COMPANY_INFORMATION [DTS.Pipeline]: component "DataReader Source" (150) failed validation and returned error code 0x80131937.

Error at COMPANY_INFORMATION [DTS.Pipeline]: One or more component failed validation.

Error at COMPANY_INFORMATION: There were errors during task validation.



Steve HoggITCommented:
The SQL Agent user, can you login to the Server with this account and map the drives? But then what you are saying is that the login does not occur to map the drives when the job runs. Hmmm.
Steve HoggITCommented:
From what I read, connecting via Pervasive is the way to go. But you tried that? Can we walk through it.
hchan_resolveAuthor Commented:

I created a system DSN using the Pervasive ODBC Client interface driver.  The driver points to the timberline server,  From there, I entered credentials to create a Pervasive Engine DSN.  

Here's the problem... Within the engine DSN, I have to create a new database pointing to the Timberline data.  The problem is that during part of the creation, I have to specify a dictionary location.   Timberline from what I can tell does not use DDF files.  There's 2 shares that contain timberline data.  The first share has the data I want the DB to point to.  The send share does have DDF files but everythign combined is less then 2 mbs.  I verifed that the DDF filles on the second share does not contain the structure for the timberline tables by bounding to them when I initially created the database.
Steve HoggITCommented:
I know you have probably searched everywhere, but just to be sure ... did you read this one? 
Anthony PerkinsCommented:
>>The Timberline ODBC driver has a constraint which will only allow the use of drive letters when pointing to its source.  The driver will not accept a UNC path. <<
Than you are out of luck, at least as far as using SQL Server Agent.  SQL Server Agent cannot used mapped drives.  Consider using some other scheduler, perhaps even Windows Scheduled Tasks.
hchan_resolveAuthor Commented:
Solution has been resolved.  The trick was to map the drives and execute the SSIS package in one process.  We try trying to do this in multiple process which would now work.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.