Geek asks and answers

How to get value from step 1 to step2 in sql Job

I need to create an SQL JOB.

Step 1: Insert a row into the TaskToProcess table and return the ProcessID (PK and Identity)

Step 2: Extract the ProcessID that is generated in step1 and pass the value to the SSIS package and execute the SSIS package.

Is this possible on a SQL server JOB ??

Please help me with this.

Thanks in advance.

+3
source share
3 answers

There is no built-in method for passing variable values ​​between job steps. However, there are several workarounds.

One option is to save the value in the table at the end of step 1 and request it back from the database in step 2.

, ProcessID, SCOPE_IDENTITY() . 1 , , 2 IDENT_CURRENT('<tablename>').

, , , 1 2 - , SSIS ( ), .

+1

, @@identity, .

0

, " " MSDN

( job_id) . , , , , , - "", . , . .

"

/ SQL Server. MSDN ,

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

In your stored procedure, the parameters will be passed and paste them into SQLAgentJobParms. After that, he can use EXEC sp_start_job. And, as already noted, the steps will be SQLAgentJobParms to get the required values.

0
source

More articles:


All Articles