Post reply

running DTS from stored procedure

  • September 21, 2006 at 8:12 pm

    #67791

    Can't get any luck with line below, anyone knows how to do this ?

    EXEC master..xp_cmdshell 'dtsrun /SSNYMFNRSQLDEV01 /E /N DTSCCRMfeedTransfer'

    i tried with and without uid/password

  • September 22, 2006 at 1:21 am

    #661915

    Schedule the DTS and Copy the DTSRun.....from the schedule job and paste it in after xp_cmdshell

    Thanks

    Sreejith

  • September 22, 2006 at 4:56 am

    #661939

    sorry, i don't understand.

    if i schedule it it'll run in the scheduled time. I'd like it to run when stored procedure is run

  • September 22, 2006 at 6:46 am

    #661957

    Bugsy,

    I think what Sreejith is saying is to schedule the job to create a DTSrun statement that you can then copy in to your statement, and then you would just delete the scheduled job.

    After you schedule the DTS job, open it and go to the Steps tab.  When you click on the step, you'll see a command that looks a lot like this:

    DTSRun /~Z0x647CE2795533388C81A093016D3677FBA947C723B2B7432D08543F543060E13A3464D683F0E1FABFD6B500605E2C26F69EF57136DCD02968C6E6892EDA09CE2F7FA79817CE4B3AB3D324E48613CD2794393FAB157825CCCA9B4981E776A00B0E020EEF3471A98CF16AB851695C71703F9A7D7A

    I believe that's what you need to copy into your xp_cmdshell parameter.

    Mattie

  • September 22, 2006 at 7:35 am

    #661968

    like this ?

    didn't work..

       Error string:  Error opening datafile: Access is denied.

    EXEC  master..xp_cmdshell 'DTSRun /~Z0xBDAD6C87207249AE418680C80C66217DD12F84FE8C153592ADD365456EBFE7A5C3C4E80F86521367169BC4FE599356894188BF7B53417AC84DF6B0DA71885066FE2D34CA912B188AEACC7C7ACA484DE7D6368BD490DD46848F66365E7CF4DFC89B4FEF44C215667E267D5EC3224EF89AB336471064A3411692567E692CB4D91265851FC864F0CBF3F71E01B20796F39015770758BF95993EA7588DD81270C4CD1AC772'

  • September 22, 2006 at 2:34 pm

    #662040

    Thanks Mattie for clarifying.

    Your problem is u(Or the Account SQL Starts) don't have permissions to access one of the files mentioned in the connection object.

    Thanks

    Sreejith

     

  • September 22, 2006 at 4:40 pm

    #662051

    hmm.. interesting

    but when i just run dts from dts window, or from the same VB front end it works ! so i don't understand why i wouldn't have permission to run it from stored procedure wich is on the same server as dts..

  • September 25, 2006 at 8:00 am

    #662240

    You may try creating a job without a schedule that executes the DTS.  Then from VB execute the job.

  • September 27, 2006 at 2:20 pm

    #662857

    This worked if you have window authenication sign on in the server, otherwise you need to put in login name and password.

    EXEC master..xp_cmdshell 'DTSRun /S "ServerName" /E /N "PackageName"  /W "0" '

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply