I am trying to executed the SSIS package file (dtsx) stored at file server. in SSISDB catalog I have folder dynamiFoldertest and package dynamiPackagetest.
I am loading an external package dynaPackage.dtsx with package variables in dynamiPackage using stored procedure catalog.deploy_packages, which is successful as I can see dynaPackage.dtsx under
dynamiFoldertest => dynamiPackagetest => dynaPackage.dtsx
Then I start the package execution using stored procedure catalog.create_execution which is successful as I get an ExecutionId as out parameter. for next step is to set the package variables value using catalog.set_execution_parameter_value stored procedure for each variable using @object_type parameter value as 30 (indicating package variables). But for every package variable I get following error
The parameter {variable name} does not exist or you don't have sufficient permissions
I checked in internal.execution_parameter_values and in
catalog.execution_parameter_values, there is no record for any package variable.
What might be reason for this?
Permissions: I can deploy package using stored procedure so I think I have enough permissions.
NOTE: I want stored procedure solution only and not using integration and DTS packages in c# as they are no more supported in .NET 6.
Please help.
Instead of using catalog.set_execution_parameter_value stored procedure to set package variables, I used catalog.set_execution_property_override_value stored he procedure and I am able to set the variable values. More information =>
https://learn.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-set-execution-property-override-value?view=sql-server-ver16