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.
Not 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.
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?