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.

Answer 1

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.

Answer 2

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.