July 23, 2014 at 5:02 pm
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
AS
BEGIN
SET NOCOUNT ON;
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
END
--------------------------------------------------------------------------------------------------
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!
July 29, 2014 at 11:21 am
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.
July 31, 2014 at 9:27 am
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.
July 31, 2014 at 9:51 am
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.
October 14, 2015 at 7:28 am
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.
September 24, 2021 at 8:29 am
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy