Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004

    Unhappy Unanswered: 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.




    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)

    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

    -- 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


    CLOSE dts_cur
    DEALLOCATE dts_cur


  2. #2
    Join Date
    Feb 2004
    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.

  3. #3
    Join Date
    Oct 2004
    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?

  4. #4
    Join Date
    Feb 2004
    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:

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts