Post reply

Error passing parameter to SSIS package from Stored Proc

  • 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

  • May 31, 2013 at 9:40 am

    #1620144

    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+''' ';

  • May 31, 2013 at 9:43 am

    #1620146

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

    Brian

  • May 31, 2013 at 9:54 am

    #1620148

    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+''' ';

  • May 31, 2013 at 10:07 am

    #1620153

    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

  • May 31, 2013 at 11:17 am

    #1620177

    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

  • May 31, 2013 at 11:58 am

    #1620187

    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.

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

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