I am able to run multiple dts scripts from a stored procedure using xp_cmdshell. The stored proc runs a loop and makes call to dtsrun like so:

dtsrun -U sa -P password -S SERVERNAME -N Client_jobcast -A ClientID:8=testdb1_foxpro -A DestObjName:8=[testdb1].[dbo].[jobcast] -L d:\log\dts_8_18_2003_22_6_11.txt

dtsrun -U sa -P password -S SERVERNAME -N Client_letter -A ClientID:8=testdb2_foxpro -A DestObjName:8=[testdb2].[dbo].[letter] -L d:\log\dts_8_18_2003_22_6_11.txt

etc...

I am getting the same intermittent errors as follows:
The execution of the following DTS Package succeeded:

Package Name: client_career_mail
Package Description: Import data into Client jobcast table
Package ID: {8EE139F4-2342-4CB5-8B80-AAF36A5D783D}
Package Version: {C61654D9-F8CE-4E52-A7A2-B90C82B24765}
Package Execution Lineage: {3A4E62DD-7420-46E7-9545-7715DE49E908}
Executed On: STAGINGSQL
Executed By: Administrator
Execution Started: 8/18/2003 10:08:56 PM
Execution Completed: 8/18/2003 10:08:56 PM
Total Execution Time: 0.25 seconds

Package Steps execution information:


Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

Step Execution Started: 8/18/2003 10:08:56 PM
Step Execution Completed: 8/18/2003 10:08:56 PM
Total Step Execution Time: 0.219 seconds
Progress count in Step: 0

Step 'DTSStep_DTSDynamicPropertiesTask_1' succeeded
Step Execution Started: 8/18/2003 10:08:56 PM
Step Execution Completed: 8/18/2003 10:08:56 PM
Total Step Execution Time: 0.219 seconds
Progress count in Step: 0

I am running as sa so I should have proper priv.

Each of the dtsrun db1 pass or fail intermittently so the above error might not occur the next time I run the stored proc.

I have looked all over internet for answers and I don't see too many people trying to automate DTS like I am doing. Any ideas?