set_execution_parameter_value

SSIS and Import/Export
1

Hi,

Using TSQL to run the same .dtsx package multiple times, and changing the @pSourcedir parameter on each run. Problem is the below example throws the below error. Parameter exists when DTSExecUI, can be modified as usual and works as expected.

Any ideas what I am doing wrong (NB: I'm sysadmin on this instance)

Msg 27176, Level 16, State 1, Procedure set_execution_parameter_value, Line 148
The parameter '@pSourceDir' does not exist or you do not have sufficient permissions.

EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id,
@object_type=50,
@parameter_name=N'@pSourceDir',
@parameter_value=N'c:\Temp';

Thanks

2

From catalog.set_execution_parameter_value , the only parameters you can set are:

LOGGING_LEVEL
DUMP_ON_ERROR
DUMP_ON_EVENT
DUMP_EVENT_CODE
CALLER_INFO
SYNCHRONIZED

3

That would explain the error. Thank you.

Any ideas how I can execute a SSIS package using TSQL and changing a user defined parameter each run.

Something I can add to this syntax?

EXEC [SSISDB].[catalog].[create_execution] @package_name=N'name.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'folder', @project_name=N'project', @use32bitruntime=False, @reference_id=Null
Select @execution_id
DECLARE @var0 sql_variant = N'name.dtsx'
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

4

Believe these 3 stored procedures will enable me to run ssis packages while supplying a parameter. Only problem is the method throws the below error about a string. Any ideas?

The data type of the input value is not compatible with the data type of the 'String'.

DECLARE @executionID BIGINT

EXEC [catalog].[create_execution]
@folder_name=N'folder_name'
@project_name=N'project_name'
@package_name=N'name.dtsx',
@reference_id=NULL,
@use32bitruntime=FALSE,
@execution_id=@executionID OUTPUT

EXEC [catalog].[set_execution_parameter_value]
@executionID,
@object_type=30,
@parameter_name=N'pSourceDir',
@parameter_value='c:\Temp'

EXEC [catalog].[start_execution] @executionID

5

Here is my updated syntax. Added the 2nd @var sql_variant and no longer get the error in the previous post

DECLARE @executionID BIGINT
DECLARE @var sql_variant = N'\myserver\ssisdata'

EXEC [catalog].[create_execution]
@folder_name=N'folder_name'
@project_name=N'project_name'
@package_name=N'name.dtsx',
@reference_id=NULL,
@use32bitruntime=FALSE,
@execution_id=@executionID OUTPUT

EXEC [catalog].[set_execution_parameter_value]
@executionID,
@object_type=30,
@parameter_name=N'pSourceDir',
@parameter_value=@var

EXEC [catalog].[start_execution] @executionID

SELECT [STATUS]
FROM [SSISDB].[internal].[operations]
WHERE operation_id = @executionID

6

I am having same problem ?

Msg 27176, Level 16, State 1, Procedure set_execution_parameter_value, Line 718
The parameter 'pSourceDir' does not exist or you do not have sufficient permissions.

I tried same code that u used to run my package

Can you please help me ?

One more question ... Do we have to manually create the same variable in the SSIS package in Visual studio before running this in SSMS ?

Thanks .. Waiting for your quick reply ?

7

Look at these pages - they should help you figure out how to execute packages in the catalog:

https://msdn.microsoft.com/en-us/library/jj820152.aspx