asked
Pass SQL Agent Job Schedule Name to SSIS Package
Hi, I have a TSQL job with two schedules (monthly and weekly) and both schedules call the same SSIS package. However, the SSIS package needs to know which schedule called it. I know how to pass variables to the package using Set Values but I don't know how to say which schedule it is that has started the job. I'm hoping that the SQL Agent has a collection of variables (this job name, this schedule name or id etc) that can be called upon. I would like to avoid: Creating two jobs with one schedule and hard coding the values separately. Having two separate SSIS packages. Having SSIS run a query to the MSDB. Creating and dropping jobs at runtime. I'd really appreciate someone answering or attempting to answer this. I'm not sure if it's possible. I'm running 2008 R2. Thanks!
The scheduler executes a job, but the executing schedule_id is not passed to the job, neither is it preserved in msdb as far as I can see: use msdb select object_name(object_id) from sys.columns where name like '%schedule_id%' --sysmaintplan_subplans --sysschedules --sysschedules_localserver_view --sysjobschedules Which makes sense really, because the agent doesn't care what called the job, just what it needs to do. I would probably do this using a log table, a config table and a bit of coding logic. Presumably the monthly job runs on a set day of the month? And the monthly job always runs either before or after the weekly one? Assuming that the monthly job runs after the weekly one: Create a config table and add a config item, RunType, with config value = 'Weekly' Add a job step before you execute the SSIS package to check the current date. If current day of month = monthly package expected day of month and current date = last package run date (i.e. weekly job has run today), then set config item value to 'Monthly' else set to 'Weekly'. Read the config table from your package and you now know what type of run it is.
15 People are following this question.
