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

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 2

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.