Need to Pass Date to each Job Step

Transact-SQL
1

Hi Experts,
I can't use SSIS for this project.
In my Agent Job I will derive a date as a variable. Then each job step needs to reference that date.
Is this doable or will I have to derive the date in each job step?
Thanks

2

Write the value(s) to a table. Then each step after that can read it/them from the table. (Use job_id as the key to the table.)

1 Like
3

There's also SESSION_CONTEXT:

I believe this persists across job steps, but you should verify that in a sample job before committing it to production.

1 Like
4

I expect this is the way I'll go, @ScottPletcher. For this reason:
If I derive the date within the job (current date - 365) and the job fails in step 23, whne I restart the job at Step 23, say the following day, then steps 1 thru 22 ran with a different date than the remaining steps.
(These steps delete from tables based on the compare date.)
I don't like that there is a manual task of having to enter the date into a table but it's the better design.
Thanks

5

Hmm, not sure I follow you. If you restart at Step 23 the next day, the table should contain the data written to it the day before by that job. Only Step 1 modifies that table row. The other steps only read it.

If the job fails, SESSION_CONTEXT is gone for good, period. You don't even have an accurate record of what it was when the job failed.

6

I think we are in agreement that Session Context is not the way to go for this effort.

If the job fails, it's best to just restart from the beginning so all tables are processed (deleted from)
based on the same date.

7

Then the first step would write the new date to the table and process as normal.

I would never have a manual step in a task like this, never.

And if there some issue with how the job processed the day before, I'd want something to show me exactly what data was used by the job that day, rather than just assuming it used the default day-before date because that's what it's supposed to do.

1 Like