Error passing parameter to SSIS package from Stored Proc

Question

Error passing parameter to SSIS package from Stored Proc

brian-594509

SSC Enthusiast

Points: 132
More actions
May 31, 2013 at 9:33 am

#278431

I am getting the error "Argument ""\Package\DataFlowTask.Variables[User::SvcDate].Properties[Value];"" for option "set" is not valid." when i attempt to pass a parameter to a variable within a SSIS package.
The variable SvcDate is declared as datatype string within the package. The scope of the variable is defined at the Data Flow Task level. When I execute the package without the /SET parameter and set a default value for the variable within the package, it runs fine.
I am new to SSIS and any help much appreciated!
Thanks,
Brian
Here is my code:
DECLARE @sqlquery AS VARCHAR(2000),
@SvcDate as varchar(10)
DECLARE @ServerName Varchar(200)
SET @SvcDate = '2120430'
SET @sqlquery = 'DTExec "/DECRYPT TBRIAN2" /FILE ^"C:\MCG\SSIS\Medmas Import.dtsx^" /SET \Package\DataFlowTask.Variables[User::SvcDate].Properties[Value]; '''+@SvcDate+''' ';
EXEC master..xp_cmdshell @sqlquery

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WangcChiKaBastar SSCrazy Points: 2297 More actions · Answer 1

brian-594509 (5/31/2013)
I am getting the error "Argument ""\Package\DataFlowTask.Variables[User::SvcDate].Properties[Value];"" for option "set" is not valid." when i attempt to pass a parameter to a variable within a SSIS package.
The variable SvcDate is declared as datatype string within the package. The scope of the variable is defined at the Data Flow Task level. When I execute the package without the /SET parameter and set a default value for the variable within the package, it runs fine.
I am new to SSIS and any help much appreciated!
Thanks,
Brian
Here is my code:
DECLARE @sqlquery AS VARCHAR(2000),
@SvcDate as varchar(10)
DECLARE @ServerName Varchar(200)
SET @SvcDate = '2120430'
SET @sqlquery = 'DTExec "/DECRYPT TBRIAN2" /FILE ^"C:\MCG\SSIS\Medmas Import.dtsx^" /SET /SET \Package.Variables[SvcDate].Properties[Value]; '''+@SvcDate+''' ';
EXEC master..xp_cmdshell @sqlquery

Try this...

SET @sqlquery = 'DTExec "/DECRYPT TBRIAN2" /FILE ^"C:\MCG\SSIS\Medmas Import.dtsx^" /SET \Package.Variables[SvcDate].Properties[Value]; '''+@SvcDate+''' ';

brian-594509 SSC Enthusiast Points: 132 More actions · Answer 2

Thanks for the quick response - no dice. Still the same error.

Brian

WangcChiKaBastar SSCrazy Points: 2297 More actions · Answer 3

ok try this may be..

SET @sqlquery = 'DTExec "/DECRYPT TBRIAN2" /FILE "C:\MCG\SSIS\Medmas Import.dtsx"

/SET \Package\DataFlowTask.Variables[User::SvcDate].Value; '''+@SvcDate+''' ';

brian-594509 SSC Enthusiast Points: 132 More actions · Answer 4

Thanks again! That resolved the syntax error and the package is executing, however, it isn't liking the value I am passing '2120430'.

Is there a way to display the parameter value from within the package when it executes? The variable is defined as a string in the package.

Brian

brian-594509 SSC Enthusiast Points: 132 More actions · Answer 5

Is there anything special that has to be done during the setup of the variable from within the package if the variable is to be assigned a parameter at runtime from a SP?

If I set the variable to be blank in the package (expecting the parm to be passed) I get an error when executing the SP whereas if I assign a value within the package, it runs fine. So it looks like either there is something wrong with the SP passing the variable or maybe how I have the package set up since the package isn't recognizing the parameter I'm passing to it.

Any help appreciated,

Brian

WangcChiKaBastar SSCrazy Points: 2297 More actions · Answer 6

you can add a Script task to display the value at runtime using MessageBox.Show Method.

if the package doesnt like the value you are passing then its got to do with what you are trying to do with the variable ?

are you passing it internally to some other process which might be expecting a different data type ?

put break points and debug.