DTS package with txt file as source wouldn't run

[tbke]

Hi there,
I have a stored procedure that calls a DTS package.
I have designed the DTS package and its data source is a text file locaed on a map drive.
When I created the package a job with the same name has been created under the SQL Server Agent. I removed the scheduler for this job as I onl run it when the user wants to.
The problem is that if I run the package from within the SQL dbase, it runs with no problems. Hoever if I run it as it should from a web based application then it wouldn't run (would not copy th data into the table).
If I now look at Jobs under Management, I see my Job showing a red error icon next to it. Refreshing it does not return the job to its correct state.

Can anyone help on this? Thank you in advance.

Tibi

 

[JayKusch]

Look at the history of the job. It may have some info as to where it may have failed. In your DTS package, set the switch to spool all of the runtime info, including errors, to a text file. You will be able to see better what is failing and maybe why.

As for the job still being marked as "red". Until that job run successfully, it will always retain the status of the last time it ran. In this case the status was "failed".

Thanks

J. Kusch

 

[tbke]

Hi Jay,

ok, it took me a while to discover the check box which enables the job history details but thank you for putting me on the right track.
The reason the job is not running is that the path to source file is not found when i call it from a web application. Very tricky as it finds this source file when I design or run the package from the SQL server itself.
Got to do more debugging on this one. I set the permission of the mapped drive that contains the path to the source txt so that Everyone can do anything but still not working. Any clues on this?

Thanks a lot.
Tibi