I am trying to run this stored procedure to kick off an SSIS Package
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[RunSSISRemitCreate]
@BatchNumber1 as VARCHAR(8),
@BatchNumber2 as VARCHAR(8),
@BatchNumber3 as VARCHAR(8),
@BatchNumber4 as VARCHAR(8),
@BatchNumber5 as VARCHAR(8),
@BatchNumber6 as VARCHAR(8),
@BatchNumber7 as VARCHAR(8),
@result as int output
as
Declare @cmd varchar(1000)
declare @FilePath varchar (50)
set @FilePath = 'E:\SSIS Packages\HSIS'
declare @Filename varchar (30)
set @Filename = '\SSISRemit.dtsx'
--select @cmd = 'dtexec /REP N /F "' + 'E:\SSIS Packages\HSIS\SSISRemit.dtsx '+ '"' + ' /De test'
select @cmd = 'dtexec /REP N /F "' + @FilePath + @Filename + '"' + ' /De test'
+ ' /Set \package.variables[BatchNumber1].Value;' + @BatchNumber1
+ ' /Set \package.variables[BatchNumber2].Value;' + @BatchNumber2
+ ' /Set \package.variables[BatchNumber3].Value;' + @BatchNumber3
+ ' /Set \package.variables[BatchNumber4].Value;' + @BatchNumber4
+ ' /Set \package.variables[BatchNumber5].Value;' + @BatchNumber5
+ ' /Set \package.variables[BatchNumber6].Value;' + @BatchNumber6
+ ' /Set \package.variables[BatchNumber7].Value;' + @BatchNumber7
exec @result = master..xp_cmdshell @cmd
RETURN @result
--[dbo].[RunSSISRemitCreate] '116965002','116965004','116965008','116965012','117022189','0','0',0
I get this error ....
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 3:00:49 PM
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 3:00:49 PM
Finished: 3:00:50 PM
Elapsed: 0.86 seconds
NULL
Any Ideas?