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.