Parameter error when executing a package with the built in stored procedures

  • I am using Excel VBA to run a stored procedure which executes a package using the built-in SQL Server stored procedures.

    The VBA passes two values from excel to the stored proc., which is then supposed to pass these "parameters" to the package to use as a variable within the package.

    Here is what the stored procedure looks like:


    @SourceFilePath sql_variant = 1,

    @Cycle sql_variant = 2

    WITH EXECUTE AS 'USER_ACCOUNT' - account that signs on using windows authentication




    declare @execution_id bigint

    exec ssisdb.catalog.create_execution

    @folder_name = 'folder'

    ,@project_name = 'project'

    ,@package_name = 'package.dtsx'

    ,@execution_id = @execution_id output

    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'[User::Cycle]', @parameter_value=@Cycle

    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'[user::SourceFilePath]', @parameter_value=@SourceFilePath

    exec ssisdb.catalog.start_execution @execution_id



    When I try to execute the package, from SQL Server or Excel using the Macro I built, I get the following error:

    "The parameter '[User::Cycle]' does not exist or you do not have sufficient permissions."

    I have given the USER_ACCOUNT that runs executes the stored procedure permission to read/write to the database and the SSIS project folder. I don't understand what is wrong!

  • This message is returned by catalog.set_execution_parameter_value raising error 27176 when it fails to lookup your parameter in SSISDB.internal.execution_parameter_values.

    Are you able to successfully call your stored procedure from a TSQL query window (e.g. SSMS)?

    Try selecting directly from that table and see if row exists with your criteria. FWIW, none of the parameter names in my internal.execution_parameter_values table look like that.

    Also, if you haven't already run into this, be aware that much of SSISDB functionality is crippled for users who are not sysadmin or ssis_admin members --most or all of the views in the catalog schema have this criteria hard-coded into the view definition.

  • They are variables in the SSIS package. I am trying to pass the variables' values as parameters because I can find no other way to do this through a stored procedure.

  • I get that. The value that goes inside single quotes here: @parameter_name=N'[User::Cycle]' must be a value that you can lookup in table SSISDB.internal.execution_parameter_values. Can you confirm that you've run a SELECT from that table and found a row with parameter_name column matching that string?

    You've posted your stored proc definition right? Then no substitution is going to take place and change N'[User::Cycle]' to something like N'CycleABC', because the literal string [User::Cycle] has been compiled into the stored procedure definition. That means proc will only succeed if there is row in SSISDB.internal.execution_parameter_values where parameter_name = '[User::Cycle]'. Looking at the error message, I suspect this is not the case.

  • I was having similar issues just today. The parameter value was there yet the procedure would not update and I couldn't get it to work except to update the execution parameter directly. Worked like a charm.

  • Hi, I am facing the similar issue. Could you please let us know with some details how were you able to solve the issue.

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

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