dtsrun from stored procedure

Hi all,

I am currently using a series of dts packages to extract and export data. To run the packages, I have a table (TW_DTS) which lists each dts package name, I then use a stored procedure to loop through each and 'dtsrun' it. If a package fails then the error is stored in another table from within the individual dts.

This solution works in principle, however when the stored procedure runs some of the dts packages fail for an unknown reason. Unfortunately the problem is not consistent - it is not always the same package, or even same number of packages that fail. Each package will work when run individually.

I have listed the stored procedure below, any advice may save me from throwing my laptop through the window.

Cheers.

----

CREATE PROCEDURE [spTWDTS] AS

DECLARE @.unique_id varchar(50)
DECLARE @.dts_name varchar(50)
DECLARE @.start_dttm datetime
DECLARE @.end_dttm datetime
DECLARE @.CMD varchar(1000)
DECLARE @.ERROR varchar(1000)

DECLARE
dts_cur CURSOR FOR SELECT unique_id, dts_name, start_dttm, end_dttm from TW_DTS where status = 'A'

open dts_cur

FETCH dts_cur INTO @.unique_id, @.dts_name, @.start_dttm, @.end_dttm

while @.@.fetch_status = 0

BEGIN
-- Set as No Error
SET @.ERROR = 0

--update the start date
UPDATE TW_DTS set start_dttm = getdate() where unique_id = @.unique_id

--run the package
SET @.CMD = 'dtsrun /S myserver /U myusername /P mypassword/N '+@.dts_name
EXECUTE @.ERROR = master..xp_cmdshell @.CMD

--update the end date
UPDATE TW_DTS set end_dttm = getdate() where unique_id = @.unique_id

--move to next dts package
FETCH dts_cur INTO @.unique_id, @.dts_name, @.start_dttm, @.end_dttm

END

CLOSE dts_cur
DEALLOCATE dts_cur

RETURN @.ERROR
GONot sure about this but perhaps dtsrun /L might help in finding out where/when the execution fails. You might want to add a select 'executed: ' + @.dts_name, 'Error', @.error after the xp_cmdshell. I assume there's a space between the password and the /N ? And perhaps adding a 'start /w ' to the command might help.

Like I said, I'm not sure if their any help but it might get you on the right track.|||Thanks Kaiowas,

Tried that and got the following error message in the log file:

Step 'Copy Data from Results to [export].[dbo].[extract] Step' failed

Step Error Source: Microsoft OLE DB Provider for Oracle
Step Error Description:Oracle error occurred, but error message could not be retrieved from Oracle.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:

The step just runs an oracle query and put the results into the 'extract' table.

Not really any the wiser now, do you know how I can get to the oracle error?|||It's an MDAC error message, so Oracle won't help you there. I find google to be very helpful in such cases, it came up with: http://support.microsoft.com/kb/255084/EN-US/