I know how to create a job dynamically from this create-sql-server-job-automatically
The issue I have with this technique is clean up is required to delete all these jobs I create What I am looking for is a way to pass in a parameter to a Job or change a Job step dynamically
Background I have a series of SSIS jobs I need a tester to run. I was shot down in creating a webpage to call a WCF service that sits on the DB Server to execute the package. I can not use xp_cmdshell. So the third option was calling the package through a sql job. Now while I can create one job per package ideally I would like one job where I can pass in the package name, a config file, and maybe some dynamic variables.
Looking at this post Calling SSIS Package from Stored Procedure in SQL Server 2008 I can see how I create the dynamic package, again the concern is cleaning up the jobs.
Constraints The reason I am going down the road of creating a job dynamically is a result of the following constraints
The testers do not have Sql installed on there machine so they do not have access
to the BIDS environment nor DTEXEC
For reasons not completely logical I am restricted from using c# and building a
WCF service that would call the package through code
I can not use xp_cmdshell due to permission and security issues.
Now if there is a better way to handle this without Sql Jobs I am open to that possibility but the main goal is to create a form (web or windows) that the tester can choose a package, run it and see the internal logs of the package run that we record.
For starters I proposed a 'cleaner' version on how to solve the question you linked too. Going from there it's only a small step to here : sp_add_job where you'll find the explanation for @delete_level
Value Description
----- -----------
0 Never
1 On success
2 On failure
3 Always
So in short, if you want jobs to clean up after themselves, use @delete_level 1 or 3 according to your needs.