DTS Package does not run in Stored Procedure

[snr]

Hi,
I have a stored procedure where I am executing a dts package :
SET @cmdString ='dtsrun /S WEBSRV /E /N Load_textFile '
EXEC master..xp_cmdshell @cmdString,no_output

In this DTS package
1) Truncate existing Table
2) Load the text file in the table.

The problem is , when executed it performs just the task 1 and not task 2. It was working fine before. What can be the reason ?

Please help
Thanks

 

[tlbroadbent]

Add a log file to the package so you can see any errors that occur and then correct them. Open the package properties. Click on teh logging tab. On the bottom of the dialog where it says Error Handling, add teh name of an error file. Then exemaine the file for possible reasons the package is failing. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[snr]

Thanks for your reply
I checked that , and I observed that if there is data in table 1 from step1 , then it executes step 1 and creates the error file. If it doesn't have data , then it doesn't create the log file.
In first case it writes that the task completed succesfully but doesn't do anything .
The file is on network drive. Is it possible that the package couldn't locate the file ? But in that case it should give error I guess.
The error file when it deletes the records :
The execution of the following DTS Package succeeded:

Package Name: Load_text_file
Package Description: Load text File
Package ID: {CBF1FA6B-A460-455E-8F10-852529799F1F}
Package Version: {64CA4B2A-F5FB-41AA-8971-29226DA8193C}
Package Execution Lineage: {D0407D16-EACF-449C-8BD6-FB78AB2A033C}
Executed On: ABC
Executed By: abc
Execution Started: 2/6/2003 9:40:27 AM
Execution Completed: 2/6/2003 9:40:27 AM
Total Execution Time: 0.234 seconds

Package Steps execution information:


Step 'DTSStep_DTSDataPumpTask_1' succeeded
Step Execution Started: 2/6/2003 9:40:27 AM
Step Execution Completed: 2/6/2003 9:40:27 AM
Total Step Execution Time: 0.218 seconds
Progress count in Step: 119

Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 2/6/2003 9:40:27 AM
Step Execution Completed: 2/6/2003 9:40:27 AM
Total Step Execution Time: 0 seconds
Progress count in Step: 0
****************************************************************************************************

 

[tlbroadbent]

Is the package referencing a mapped network drive? SQL Server runs in its own context and DO NOT SEE drives mapped in windows. The UNC must be used to access the file. And proper permissions must be granted to the SQL Server startup account to read the file. If you want to get the best answer for your question read faq183-874.

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm