hchan_resolve
asked on
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..
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..
dro_law
have you tried using a credential & proxy for the ssis task that does have access to the mapped drives?
Can you map the drives on the SQL Server where the Agent is running?
ASKER
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.
ASKER
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.
ASKER
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
--------------------------
ADDITIONAL INFORMATION:
Error at COMPANY_INFORMATION [DataReader Source [150]]: System.Data.Odbc.OdbcExcep
M:\Accounting\Screen\OD.sc
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.OdbcConne
at System.Data.Odbc.OdbcConne
at System.Data.Odbc.OdbcConne
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.Odbc.OdbcConne
at Microsoft.SqlServer.Dts.Ru
at Microsoft.SqlServer.Dts.Ru
at Microsoft.SqlServer.Dts.Pi
at Microsoft.SqlServer.Dts.Pi
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.
(Microsoft.DataTransformat
--------------------------
BUTTONS:
OK
--------------------------
TITLE: Package Validation Error
--------------------------
Package Validation Error
--------------------------
ADDITIONAL INFORMATION:
Error at COMPANY_INFORMATION [DataReader Source [150]]: System.Data.Odbc.OdbcExcep
M:\Accounting\Screen\OD.sc
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.OdbcConne
at System.Data.Odbc.OdbcConne
at System.Data.Odbc.OdbcConne
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.Odbc.OdbcConne
at Microsoft.SqlServer.Dts.Ru
at Microsoft.SqlServer.Dts.Ru
at Microsoft.SqlServer.Dts.Pi
at Microsoft.SqlServer.Dts.Pi
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.
(Microsoft.DataTransformat
--------------------------
BUTTONS:
OK
--------------------------
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.
From what I read, connecting via Pervasive is the way to go. But you tried that? Can we walk through it.
ASKER
Sure...
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.
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.
I know you have probably searched everywhere, but just to be sure ... did you read this one?
https://www.experts-exchange.com/questions/21426283/Linking-to-a-Pervasive-DB.html?sfQueryTermInfo=1+timberlin
https://www.experts-exchange.com/questions/21426283/Linking-to-a-Pervasive-DB.html?sfQueryTermInfo=1+timberlin
>>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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.