如何在sql作业中从step1到step2获取值 [英] How to get the value from step1 to step2 in sql Job

问题描述

我需要创建一个SQL JOB.

I need to create a SQL JOB.

第1步: 在TaskToProcess表中插入一行并返回ProcessID(PK和身份)

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

第二步: 检索在 step1 中生成的ProcessID，并将其值传递给SSIS包并执行SSIS包.

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

在SQL Server JOB中有可能吗?

Is this Possible in SQL server JOB??

请帮助我

谢谢.

推荐答案

没有在作业步骤之间传递变量值的内置方法.但是，有两种解决方法.

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

一种选择是在第1步末将值存储在表中，并在第2步从数据库中查询回来.

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.

听起来像是通过插入表并返回所插入行的SCOPE_IDENTITY()来生成ProcessID.如果作业步骤1是插入该表的唯一过程，则可以使用 IDENT_CURRENT('<tablename>') 函数.

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.

编辑

如果可以在您的过程控制表中插入多个过程，则最好的解决方案可能是将步骤1和2重构为一个步骤-可能使用可在步骤之间传递变量的控制性SSIS主软件包(或其他等效技术)

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.

这篇关于如何在sql作业中从step1到step2获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！