Running a SQL Agent Job with SMO with custom parameter values without having to Alter() the JobStep?

Question

I have some SSIS packages that are stored in the SQL Server and exposed via a SQL Agent Job

I have written a component that allows me to dynamically build up the correct command line for the job step containing the SSIS package which allows me to override package variables using the /SET command

Unfortunately, in SMO, I need to do the following:

jobStep.Command = MyNewComandString;
jobStep.Alter();
parentJob.Start();

The problem with this approach is that it requires me to call .Alter() in order for the new command line to take effect, which updates the JobStep definition on the SQL Server Agent. If I don't call Alter() and just call Start() then it will not take effect

This is obviously a PITA because now I need to worry about 'restoring' the original Command line ...is there any way I can pass a custom command string to the JobStep, have it execute without having to call Alter() and thus be responsible for restoring the original command line?

PS the xp_cmdshell is not an option on my environment

Answer 1

One suggestion --  you could create an entirely new "temporary" job and delete it when finished executing. By way of example if I look an SSRS database server, SQL Agent jobs are created with GUID's as job names for each scheduled report and are deleted when the report job schedule is deleted (at least I think they are).

Answer 2

This is actually what I've implemented in the meantime, but the disadvantage of this is that I lose the job execution history after the job is deleted (I have .DeleteLevel set to Always)

One of the features I wanted to have on this web page was the ability to monitor the job-execution status and to report Success / Fail by polling and refreshing the JobStep every 5 seconds asynchronously via Ajax.

Suppose I went with the temporary job approach, is there anyway to still retrieve the job outcome?

It's a little frustrating because I feel I've exhausted every option - xp_cmdshell isn't available, sp_start_job won't let me pass custom parameters and SMO doesn't provide a way to ad-hoc invoke an SSIS package with custom parameters without altering the job step first :(

Answer 3

Thinking about this a little more. What about using an SSIS_Configurations table instead of passing in your package variables to dtexec /SET? That way you could update the SSIS_Configurations table using T-SQL to the variable values you'd like then execute the package with sp_start_job. The added benefit is that you don't have mess creating jobs or altering jobs.

I've seen developers use this technique in my organization.

Answer 4

That's a great suggestion, and prior to SQL Server 2005 that would've worked fine, however it doesn't work this way in 2008 for the purposes we require - our SSIS packages do use the [SSIS Configurations] table with default values we've populated for things like filename masks, folder locations, user name / passwords for FTP sites, various package modes etc.

We used to be able to manually call DTEXEC /SET on those variables to override the package variable values that are present already in [SSIS Configurations] for ad-hoc and one-time / on-demand loads, and the DTEXEC would happily do this for us.

Unfortunately, with the introduction of SQL Server 2008, and in perhaps one of the most baffling moves by Microsoft, they changed this behavior now such that package variables that are mapped to a value in [SSIS Configurations] can no longer be overriden by DTEXEC /SET commands - it will simply ignore this and use the value in [SSIS Configurations] hence robbing us of the flexibility to have our packages use a default value and use an override value!

We would rather not have to alter the values of [SSIS Configurations] for ad-hoc runs of SSIS packages, since this would similarly place the burden of being responsible for restoring these values on this process, similar to the original conundrum of altering the JobStep via SMO :/

If there simply isn't another alternative, this may have to be one of the compromises we're considering, but it would require some re-factoring of several of our packages to map package variables to [SSIS Configurations] and not really a desirable solution. The only other 2 solutions we're considering are creating a temporary job with auto-delete and caching / restoring the original Command property. Of these, I'd probably prefer the temporary job route, which again, isn't ideal but might be the best compromise

Answer 5

Sorry I can't think of any other method. Jobs don't really support passing params. One thing that comes close and I have used is job tokens, but unfortunately you can't create user defined tokens. They are fixed to things like server name. You can read about job tokens here: http://msdn.microsoft.com/en-us/library/ms175575.aspx