how to run DTS Package using sql query

• DURGESH-769360

  SSC Eights!

  Points: 968

  More actions

  March 29, 2009 at 11:12 pm

  #73624

  Hi all,

  I have created a DTS package can u help to run the package using

  sql query

  thanks in advance

  Regards

  Durgesh

• Adiga

  One Orange Chip

  Points: 27284

  More actions

  March 29, 2009 at 11:41 pm

  #968144

  Hi Durgesh,

  You can call the "DTSRUN" utility via "xp_cmdshell". Read more about DTSRUN here

  Pradeep Adiga
  Blog: sqldbadiaries.com
  Twitter: @pradeepadiga

• JeremyE

  SSCoach

  Points: 16231

  More actions

  March 30, 2009 at 3:23 pm

  #968731

  You could also create a job for the DTS package and then use sp_start_job to run it.

• cloud-754230

  SSC Veteran

  Points: 219

  More actions

  April 1, 2009 at 4:59 am

  #969721

  Using the procedure below enables us to check which DTS Packages are actually used and rename/delete unused packages after appropriate time to save confusion

  create procedure RunPackage

  as

  declare

  @server varchar(50)

  ,@cmd varchar(250)

  ,@cmd2 varchar (20)

  set @server = (select top 1 originating_server from msdb.dbo.sysjobs)

  if upper(@server)= {Production Server Name}

  begin

  set @cmd = 'c:\Progra~1\Micros~4\80\Tools\Binn\DTSRun.exe /S '

  end

  if upper(@server)= {Development Server Name}

  begin

  set @cmd = 'c:\Progra~1\Micros~3\80\Tools\Binn\DTSRun.exe /S '

  end

  set @cmd = @cmd + upper(@server) + ' /E /N ' + @PackageName

  exec @Error = master..xp_cmdshell @cmd

  INSERT INTO PackageRunHistory(PackageName, DateTime,Error)

  VALUES(@PackageName, getdate(),@Error)

  if @Error <>0

  begin

  set @ErrorMess = 'The Package ' + @PackageName + ' Failed'

  RAISERROR (@ErrorMess, 16, 1)

  end