CODEWITHSUNDEEP
sqlssis
user1082748
user1082748

Reputation: 365

ssisdb: set_object_parameter_value, Cannot find the parameter because it does not exist

If I hard code the values for @param_name, @param_value then the script works. However, If I use the following script, I get a "Cannot find the parameter because it does not exist" error. I tried many variations without success. The parameters are properly populated and have the correct data types.

declare @param_name as nvarchar(128), @param_value sql_variant
declare set_environment_params cursor for
    select cast(name as nvarchar(128)) as name,  value from internal.environment_variables
    where environment_id=00

open set_environment_params
fetch next from set_environment_params
into @param_name, @param_value

while @@FETCH_STATUS=0
begin

    EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=20, 
        @parameter_name=@param_name, 
        @object_name=N'Test', 
        @folder_name=N'DevOps', 
        @project_name=N'Test', 
        @value_type=R, 
        @parameter_value=@param_value


fetch next from set_environment_params
into @param_name, @param_value
end


deallocate  set_environment_params;
close  set_environment_params;

GO

Upvotes: 0

Views: 3243

Answers (1)

billinkc
billinkc

Reputation: 61269

TL;DR;

set_object_parameter_value is overloaded. If you want to set a Value, then specify the value in @parameter_value If you want to set a reference to an environmental variable, then @parameter_value is the name of the variable being referenced.

Details

It's working for me, after

I have my project parameters defined as

project parameters

In SSIS catalog, I created an environment called SOEnvironment and populated it with 3 variables as such

DECLARE @var datetime = N'2018-01-02';

EXEC SSISDB.catalog.create_environment_variable
    @variable_name = N'aDateTime'
,   @sensitive = False
,   @description = N''
,   @environment_name = N'SOEnvironment'
,   @folder_name = N'So'
,   @value = @var
,   @data_type = N'DateTime';
GO

DECLARE @var int = N'0';

EXEC SSISDB.catalog.create_environment_variable
    @variable_name = N'anInt'
,   @sensitive = False
,   @description = N''
,   @environment_name = N'SOEnvironment'
,   @folder_name = N'So'
,   @value = @var
,   @data_type = N'Int32';
GO

DECLARE @var sql_variant = N'A';

EXEC SSISDB.catalog.create_environment_variable
    @variable_name = N'aWideString'
,   @sensitive = False
,   @description = N''
,   @environment_name = N'SOEnvironment'
,   @folder_name = N'So'
,   @value = @var
,   @data_type = N'String';
GO

After transposing your last two statements (close, then deallocate the cursor), I clicked on Configure for the project and confirmed I have values connected to the environmental values

enter image description here

That looked odd to me and so I clicked in to see how SSMS does it. We have 3 options: Edit Value, use Default, Use Reference

enter image description here

When you explicitly supply a value, it shows as bold (aDateTime) When you use an environmental reference, it shows as underlined (aWideString) The Default is left in normal font (anInt)

enter image description here

So, I then looked at the generated SQL and the last argument should be

@parameter_value = @param_name

for environmental references.

Upvotes: 1

Related Questions