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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply