Execute SSIS package stored in Database - From Stored Procedure

• REMullins

  SSCommitted

  Points: 1820

  More actions

  May 28, 2009 at 1:14 pm

  #138171

  I am trying to execute a SSIS package that is stored in a SQL Server 2005 database - want to execute from a stored procedure in same database. What commands/operations are necessary.

  I tried:

  dtexec /SQL "\PKG_Name" /Server_Name "." /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E /De "Password"

  Msg 102, Level 15, State 1, Line 1

  Incorrect syntax near '/'.

• Phil Parkin

  SSC Guru

  Points: 246993

  More actions

  May 28, 2009 at 7:50 pm

  #1001855

  For a start, try using single quotes around your literals ...

  --edit:

  You may first want to create an sp to help. Something like:

  Create procedure [dbo].[s_ExecutePackage]

  @FilePath varchar(1000) ,

  @Filename varchar(128)

  as

  Declare @cmd varchar(1000)

  select @cmd = 'dtexec /F "' + @FilePath + @Filename + '"'

  exec master..xp_cmdshell @cmd

  go

  This will help you execute the package from within your sp, once you have enabled use of xp_cmdshell, of course.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel