question

Shiloy Reinhart avatar image
Shiloy Reinhart asked

Execute SSIS Package from stored procedure with Parameters

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?

ssispackageexecute
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Matt Whitfield avatar image
Matt Whitfield answered

Have you checked that the account under which SQL Server is running has the access rights to the resources that the package needs? From MSDN:

The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account.

So this means that the service account that SQL Server is running under needs the access rights, rather than the user logged on to SQL Server.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.