Execute an SSIS Package as a SQLAgent job from C# code and pass a parameter to the SSIS package

Question

I need to pass a parameter to the SSIS package, which is being executed from an SQLAgent job. The job is triggered from C# code. I could not get a definitive answer in the internet. Is it possible?

SQLServer Version: 2012

From the C#, I am using the following code to start the job.

exec msdb..sp_start_job @job_name='Upload_Job'

Answer 1

Create a parameters table and have your C# app insert them into that. Then modify your SSIS job to grab the parameters from their as the first step of the package. Add a final step to the package to clear down the parameter table.

@Nick.McDermaid stated there was a concern about "parallelism". If there is a chance that this job may be set to run in quick succession you could have a "parameter queue". The first step of the SSIS package would "pop" the top parameter off the queue. This way it can be run pretty quickly time after time with no issue.

Answer 2

A SQL Agent job is static - the definition of the actions to take is specified at job/job step creation time.

If you need to pass parameters to a job, SQL Agent doesn't support this.

If you have a finite set of parameters to pass: Update Hr, Load Finance, Delta Processing, Full Load - then create a job per scenario and be done with it.

If the parameters cannot be bound, then create one-time jobs within SQL Agent. This allows you to specify the exact parameters you need for this run with no worries about concurrent access to a configuration table. The reason for taking this route and not just running the SSISDB procedures themselves usually involves the need to specify a proxy user/credential.

Answer 3

Like some have said in the comments, the better answer is to not use the agent, but instead, call the SSIS task itself in SSISDB. This means you can run the task multiple times at the same time (impossible with Agent), and you can pass parameters.

Depending on your set up, you might want to create a Stored Procedure that calls the relevant SP's in SSISDB for your specific task, rather than calling them all in the application. I've found this easier, in my opinion, as you have a little more control and only need to change one place if someone on the package changes.

This is an example, however, might help you get the idea:

USE SSISDB;
GO
--I'm going to create the SPs in a new schema in SSISDB, however, you can create this elsewhere if you want
--Create the new schema
CREATE SCHEMA app;
GO
--Create the proc, I've made up some parameters
CREATE PROC app.UploadTask @FileName sql_variant, @FileDate date, @RetryNum int AS

    DECLARE @execution_id bigint;

    --Create the execution
    EXEC [catalog].create_execution @package_name = N'UploadDocument.dtsx', --Made up package name
                                    @execution_id = @execution_id OUTPUT,
                                    @folder_name = N'FTP Packages', --Madeup name
                                    @project_name = N'FileTranfers', --Madeup Project Name 
                                    @use32bitruntime = FALSE,
                                    @reference_id = NULL;

    --Add the paramters
    EXEC [catalog].set_execution_parameter_value @execution_id = @execution_id,
                                                 @object_type = 30,
                                                 @parameter_name = N'FileName',
                                                 @parameter_value = @FileName;
    EXEC [catalog].set_execution_parameter_value @execution_id = @execution_id,
                                                 @object_type = 30,
                                                 @parameter_name = N'SubmissionDate',
                                                 @parameter_value = @FileDate;
    EXEC [catalog].set_execution_parameter_value @execution_id = @execution_id,
                                                 @object_type = 30,
                                                 @parameter_name = N'Retries',
                                                 @parameter_value = @RetryNum;

    --Set the logging level
    EXEC [catalog].set_execution_parameter_value @execution_id =  @execution_id,
                                                 @object_type = 50,
                                                 @parameter_name = N'LOGGING_LEVEL',
                                                 @parameter_value = 1;

    --This is optional, comment out or delete the following if you do not want it
    --Set the package to run synchronously
    EXEC [catalog].set_execution_parameter_value @execution_id =  @execution_id,
                                                 @object_type = 50,
                                                 @parameter_name = N'SYNCHRONIZED',
                                                 @parameter_value = 1;
    --And now, run the package
    EXEC [catalog].start_execution @execution_id;
GO

--Now a sample call:
EXEC app.UploadTask @FileName = N'\\YourFileFile\SomeShare\SomeFolder\YourFile.txt', --It's important this is a nvarchar, varchar won't work!
                    @FileDate = '20180704',
                    @RetryNum = 3;

Any questions, please do ask.

Answer 4

Why do you use SQL Server Agent job? It is not possible to pass parameters to a job. There is another way to execute the package: The DTExec utility, which allows passing parameters to the package configuration:

dtexec /f "PathToMyPackage\Package.dtsx" /set \package.variables[myvariable].Value;myvalue