running DTS from stored procedure

Question

Post reply

running DTS from stored procedure

Bugsy-369262

SSC Rookie

Points: 39
More actions
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

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

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

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

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

Thanks

Sreejith

Bugsy-369262 SSC Rookie Points: 39 More actions · Answer 2

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

MattieNH SSCertifiable Points: 7463 More actions · Answer 3

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

Bugsy-369262 SSC Rookie Points: 39 More actions · Answer 4

like this ?

didn't work..

Error string: Error opening datafile: Access is denied.

EXEC master..xp_cmdshell 'DTSRun /~Z0xBDAD6C87207249AE418680C80C66217DD12F84FE8C153592ADD365456EBFE7A5C3C4E80F86521367169BC4FE599356894188BF7B53417AC84DF6B0DA71885066FE2D34CA912B188AEACC7C7ACA484DE7D6368BD490DD46848F66365E7CF4DFC89B4FEF44C215667E267D5EC3224EF89AB336471064A3411692567E692CB4D91265851FC864F0CBF3F71E01B20796F39015770758BF95993EA7588DD81270C4CD1AC772'

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 5

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

Bugsy-369262 SSC Rookie Points: 39 More actions · Answer 6

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

Perry Harts Ten Centuries Points: 1271 More actions · Answer 7

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

Loner SSC-Insane Points: 21279 More actions · Answer 8

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