Problem with Schedule DTS

[angjbsca]

Hi. I have a DTS who takes data from foxpro DB in one server via ODBC System DSN and put in excel file when I run the dts from enterprise manager the dts run very well but when I make a schedule to run the dts the system throw this error:

SQL Server Scheduled Job 'ProvidersFileSpanish 2' (0x733217491E0D3F4B987C00A5261EA520) - Status: Failed - Invoked on: 2006-06-14 14:30:00 - Message: The job failed. The Job was invoked by Schedule 90 (ProvidersFileSpanish 2). The last step to run was step 1 (ProvidersFileSpanish 2).

 

[gradley]

Hi angjbsca,

Do you have any additional information on the error. When you select View Job History, click the "show step details" checkbox at the top and then take a look at the second line. It should give you more details.

My guess is that it's either a security issue (Agent runs under a windows account), or the path of the Excel file cannot be found (you will need to use a UNC path if this is the case).

 

[angjbsca]

Here is the detail error:

Executed as user: LCAILCA\SYSTEM. ...Executing... DTSRun OnStart: DTSStep_DTSFTPTask_1 DTSRun OnProgress: DTSStep_DTSFTPTask_1; Copying file to D:\ProvidersFiles\provider_directory_english version.XLS.; PercentComplete = 0; ProgressCount = 1 DTSRun OnFinish: DTSStep_DTSFTPTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217865 (80040E37) Error string: [Microsoft][ODBC Visual FoxPro Driver]File 'provider.dbf' does not exist. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147217865 (80040E37); Provider Error: 173 (AD) Error string: [Microsoft][ODBC Visual FoxPro Driver]File 'provider.dbf' does not exist. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

 

[unclerico]

you have a rights issue, LCAILCA\SYSTEM is unknown to the source machine

 

[gradley]

It obviously cannot find teh ODBC driver for Visual FoxPro

"[Microsoft][ODBC Visual FoxPro Driver]File 'provider.dbf' does not exist."

Did you create this Driver connection as a File DSN or User/System DSN? If it's a File DSN, is it on a mapped drive?

I'm not too familiar with using DSN's in Sql Server but you may want to try setting this up as a System DSN.

 

[angjbsca]

is the system DSN, the strange is when I run the DTS Manualy its work fine the problem is when the schedule run the DTS.

 

[gradley]

It looks like LCAILCA\SYSTEM is the domain account that runs the SQl Server Agent Service? Are you signed on as LCAILCA\SYSTEM when you run the package interactively? IF not, try signing on at this user and see if you can see the System DSN.


Also, if it's an option, you may try changing the Owner on the General tab of the Sql Server Agent job to 'sa' instead of the user who added the job.

 

[angjbsca]

It's Posible to assign the user to run the Schedule DTS?
because I think that the problem is with folder permissions. because I copy the folder to local machine and was execute perfet

 

[gradley]

You should trying assigning LCAILCA\SYSTEM proper privileges to the folder in question since a believe LCAILCA\SYSTEM is the domain account running the SQL Agent Service.

You may also want to consider setting up a Proxy account for SQl Server Agent jobs.

 

[angjbsca]

I change the account on my SQl Agent and assign provileges to a foxpro folder. but I still get this. sound that the DTS start but when it get 5000 records stop..

Executed as user: DOMAIN\MyAccount. ...Start: DTSStep_DTSFTPTask_1 DTSRun OnProgress: DTSStep_DTSFTPTask_1; Copying file to D:\ProvidersFiles\provider_directory_english version.XLS.; PercentComplete = 0; ProgressCount = 1 DTSRun OnFinish: DTSStep_DTSFTPTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 3000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount ... Process Exit Code 1. The step failed.

 

[gradley]

I would add Package level logging to the DTS package and try running again. This should hopefully tell you why it's failing.