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..