x

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?

more ▼

asked Apr 26 '10 at 05:18 PM in Default

Shiloy Reinhart gravatar image

Shiloy Reinhart
2 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Apr 26 '10 at 06:19 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x900
x26
x16

asked: Apr 26 '10 at 05:18 PM

Seen: 1935 times

Last Updated: Apr 26 '10 at 06:13 PM