calling DTS from stored Procedure with variable

• dan_roche

  SSC Rookie

  Points: 33

  More actions

  March 11, 2010 at 3:43 pm

  #219796

  has anyone in the history of sql server ever been able to get this to work without being a sysadmin role AND pass in a global variable from visual basic?

  Apparently the sp_OA implementation has memory leak issues and it doesn't work anyway. I can make it work by selecting the DTS in EM and executing, but the execution fails from SP.

  I tried making a job but couldn't figure out how to pass variables to the job

  It should fire from VB. User selects file, click process, fire DTS. That's it. Is this possible? Is my strategy wrong?

• shen-dest

  SSCrazy

  Points: 2498

  More actions

  April 15, 2010 at 6:15 am

  #1152035

  hi,

  maybe you can create an SP, to which you send parameters to execute DTS packages.

  create proc call_dts @dtsname varchar(20), @servername varchar(20)

  as

  declare @command varchar(100)

  set @command= 'DTSRun /S '+@SERVERNAME+' /E /N '+@dtsname+''

  print @command

  exec master..xp_cmdshell @command

  go

  Regards,
  MShenel