I have DTS packages that import data from SQL Anywhere and MS Access. The
ones that import from SQL Anywhere run fine (that was last month's battle).
Only the jobs importing from MS Access are failing with the following message:

Executed as user: FED\service. ...n OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Copy Data
from JJ_ConsWPayRestrDetail to
[ThankYouAcknowledgements].[dbo].[JJ_ConsWPayRestrDetail] Step DTSRun
OnError: Copy Data from JJ_ConsWPayRestrDetail to
[ThankYouAcknowledgements].[dbo].[JJ_ConsWPayRestrDetail] Step, Error =
-2147467259 (80004005) Error string: [Microsoft][ODBC Microsoft Access
Driver] '(unknown)' 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. Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file: Help context: 0 Error Detail Records: Error:
-2147467259 (80004005); Provider Error: 1023 (3FF) Error string:
[Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path.
Make sure that the path name is spelled correctly and that you are connect...
Process Exit Code 1. The step failed.



I have read this article: http://support.microsoft.com/?id=269074
Here are the answers to the questions (by section)

Where is the DTS Package Running?
On the server. It was also developed there (using Remote Desktop).

Who Owns the Job That Runs the DTS Package?
FED\SERVICE which is a member of the SYSADMIN group.

How is the DTS Package Launched?
By the Job Scheduler in Enterprise Manager.

Mapped Drives
The package is using a mapped drive. However, I can see the mapped drive
when I open Windows Explorer from the server on which the package runs & the
package still fails.

Relative Path
Same as previous answer.

COM Components in ActiveX Scripts
Not used.

Package Security
None used.

Thanks in advance.
--
Tyler

Not sure but the mapped drive may be your problem, the package is being run
in the context of the account for sql server agent, which would not have an
iteractive session, therefore drive would not be mapped ?, can you use UNC
instead.

"Tyler" <tylerf(REMOVETHISPART)@bocafed.org> wrote

I'm not sure if I'm using mapped drives or a UNC.

The DTS package has 2 steps & was created using the Import Wizard.
Step 1 truncates a table on the SQL server. this works when the package is
run manually or by the scheduler.
Step 2 copies a table from a MS Access to SQL Server.
Connection 1 Properties:
Data Source: Microsoft Access
File name: Q:\REHold2005.mdb
Username: Admin
Password: <blank>

I changed the File Name to: \\Backoffice\REShare\REHold2005.mdb

The package succeeds when run manually & fails when run by the job scheduler.

--
Tyler


"David J. Cartwright" wrote:

does FED\SERVICE have access to \\Backoffice\REShare\REHold2005.mdb (The
Share and File ???)

"Tyler" <tylerf(REMOVETHISPART)@bocafed.org> wrote

FWIW I have a very similar problem, except that I am importing from an
ODBC data source.

The reported error messag eis

Error string: [Microsoft][ODBC Driver Manager] Data source name not
found and no default driver specified Error source: Microsoft OLE
DB Provider fo... Process Exit Code 7. The step failed.

I guess that the problem lies in then context of the account for sql
server agent, but I'm not too sure WHAT to check...

Any help welcome

--alexT

I think so because FED/SERVICE is in the Sysadmin group.
How can I tell for sure?
--
Tyler


"David J. Cartwright" wrote:

check permisssion on the share and files, log onto computer as FED\SERVICE
then try to access files

"Tyler" <tylerf(REMOVETHISPART)@bocafed.org> wrote

To revive this thread (because the scheduled job still fails), when I logon
as FED\SERVICE I can access the files.

--
Tyler


"David J. Cartwright" wrote: