Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: DTS from stored procedure.

    This issue has come up in our office.

    Is there any way to call a DTS package from a stored procedure without jumping into xp_cmdshell to kick off DTSRUN?

    blindman

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: DTS from stored procedure.

    I think this one has been addressed before here (but I can't find it right now either). I think it basically involved using sp_start_job to start a job linked to the DTS task.

    Regards,

    hmscott

    Originally posted by blindman
    This issue has come up in our office.

    Is there any way to call a DTS package from a stored procedure without jumping into xp_cmdshell to kick off DTSRUN?

    blindman

  3. #3
    Join Date
    Nov 2003
    Posts
    48
    http://www.houseoffusion.com/cf_list...6&threadid=227

    for your reference, I copy the code in the above link and post it here

    the user executing this package must have execute rights to the sp_OA* sps
    in master.

    Code:
    CREATE PROC <SP Name> as 
    
    DECLARE @hr int, @oPKG int 
    
    EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT 
    IF @hr <> 0 
    BEGIN 
        PRINT '***  Create Package object failed' 
        EXEC sp_displayoaerrorinfo @oPKG, @hr 
        RETURN 
    END 
    
    
    --Loading the Package: 
    -- DTSSQLServerStorageFlags : 
    --- DTSSQLStgFlag_Default = 0 
    --- DTSSQLStgFlag_UseTrustedConnection = 256 
    EXEC @hr = sp_OAMethod @oPKG, 
    'LoadFromSQLServer("<servername>", "<user>", "<password>", 0, , , , "<DTS 
    Package Name>")', 
      NULL 
    IF @hr <> 0 
    BEGIN 
        PRINT '***  Load Package failed' 
       EXEC sp_displayoaerrorinfo @oPKG, @hr 
        RETURN 
    END 
    
    --Executing the Package: 
    EXEC @hr = sp_OAMethod @oPKG, 'Execute' 
    IF @hr <> 0 
    BEGIN 
        PRINT '***  Execute failed' 
       EXEC sp_displayoaerrorinfo @oPKG , @hr 
        RETURN 
    END 
    
    --Cleaning up: 
    EXEC @hr = sp_OADestroy @oPKG 
    IF @hr <> 0 
    BEGIN 
        PRINT '***  Destroy Package failed' 
       EXEC sp_displayoaerrorinfo @oPKG, @hr 
        RETURN 
    END
    Shianmiin

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why don't you want to use xp_cmdshell?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Re: DTS from stored procedure.

    Originally posted by blindman
    This issue has come up in our office.

    Is there any way to call a DTS package from a stored procedure without jumping into xp_cmdshell to kick off DTSRUN?

    blindman
    the holy book also lists how to do the same from VB
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thanks everybody.

    blindman

Posting Permissions

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