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, 2010 at 05:18 PM in Default

avatar 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, 2010 at 06:19 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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.

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:

x1152
x33
x20

asked: Apr 26, 2010 at 05:18 PM

Seen: 5778 times

Last Updated: Apr 26, 2010 at 06:13 PM

Copyright 2016 Redgate Software. Privacy Policy