How to get the value from step1 to step2 in sql Job

Question

I need to create a SQL JOB.

Step1: Insert a Row into TaskToProcess Table and return ProcessID(PK and Identity)

Step2: Retrive the ProcessID which is generated in step1 and pass the value to SSIS package and execute the SSIS Package.

Is this Possible in SQL server JOB??

Please help me on this

Thanks in advance.

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

3

There is no built-in method of passing variable values between job steps. However, there are a couple of workarounds.

One option would be to store the value in table at the end of step 1 and query it back from the database in step 2.

It sounds like you are generating ProcessID by inserting into a table and returning the SCOPE_IDENTITY() of the inserted row. If job step 1 is the only process inserting into this table, you can retrieve the last inserted value from job 2 using the IDENT_CURRENT('<tablename>') function.

EDIT

If multiple process could insert into your process control table, the best solution is probably to refactor steps 1 and 2 into a single step - possibly with a controlling SSIS master package (or other equivalent technology) which can pass the variables between steps.

edited Jun 20 '20 at 09:12

Community

1
1

answered May 21 '11 at 09:28

Ed Harper

21,127
4
54
80

yes this will work.. but what IF any other process(not this job) insert a row into table in between Step1 and Step2? my entire Process will differ correct? – VInayK May 21 '11 at 09:32
@VinnaKanna - yes, if another process inserts into the table between step 1 and step 2 then then step 2 would pick up the wrong value. See my edit for another alternative approach. – Ed Harper May 22 '11 at 06:11
Yes i like the solution which you given under EDIT section.. Thanks – VInayK May 23 '11 at 05:54
But as of i know we cont catch the value which is returned at Step1 in job at Step2 – VInayK May 23 '11 at 06:19
@VinnaKanna - if steps 1 and 2 run from an SSIS package, you should be able to return a value from step 1 into a variable in the controlling package. – Ed Harper May 23 '11 at 07:35

score 1 · Answer 2 · edited May 23 '17 at 11:46

Similar to Ed Harper's answer, but some details found in "Variables in Job Steps" MSDN forum thread

For the job environment, some flavor of Process-Keyed Tables (using the job_id) or Global Temporary Tables seems most useful. Of course, I realize that you might not want to have something left 'globally' available. If necessary, you could also look into encrypting or obfuscating the value that you store. Be sure to delete the row once you have used it.

The Process-Keyed Tables are described in article "How to Share Data between Stored Procedure"

Another suggestion in Send parameters to SQL server agent jobs/job steps MSDN forum thread to create a table to hold the parameters, such as:

CREATE TABLE SQLAgentJobParms
(job_id uniqueidentifier,
 execution_instance int,
 parameter_name nvarchar(100),
 parameter_value nvarchar(100),
 used_datetime datetime NULL);

Your calling stored procedure would take the parameters passed to it and insert them into SQLAgentJobParms. After that, it could use EXEC sp_start_job. And, as already noted, the job steps would select from SQLAgentJobParms to get the necessary values.

How to get the value from step1 to step2 in sql Job

2 Answers2

Linked