I found a post claiming that it is possible to pass parameters to a SQL Agent Job, but no "how to" was included. Can someone explain how this is done?
我发现一篇帖子声称可以将参数传递给SQL代理作业，但没有包含“如何”。有人能解释一下这是怎么做到的吗？
In my scenario, I need a stored proc to invoke the SQL Agent and pass a parameter(s). The SQL Agent job, in turn, must pass the parameter(s) to an SSIS package step.
在我的场景中，我需要一个存储过程来调用SQL代理并传递一个参数。反过来，SQL Agent作业必须将参数传递给SSIS包步骤。
The alternative I've heard is to have the stored proc write values to a table, and then have the SQL Agent job (or the SSIS package it invokes) read those values from the table. I will take this latter approach if I must, though it is klugey.
我听说过的替代方法是将存储的proc写入值写入表，然后让SQL Agent作业（或它调用的SSIS包）从表中读取这些值。如果必须的话，我将采用后一种方法，尽管它很糟糕。
Update: The motive for this exercise is to form an integration test consisting of (a) the SQL Agent job which provides a package configuration file and (b) the SSIS package which needs the values in the package configuration file. Thus I do not want to invoke the SSIS package directly. Furthermore, testers have neither permission to launch the SQL Agent job directly, nor should they be allowed to cause SQL Agent jobs to be created dynamically. The sproc (legally) circumvents the permission issue. Finally, the integration test can target one of dozens of SSIS packages, but it is not practical in my environment to have dozens of SQL Agent Job definitions. Ergo, a sproc launches the SQL Agent job, and the parameter indicates which SSIS package to launch.
更新：本练习的动机是形成一个集成测试，包括（a）提供包配置文件的SQL代理作业和（b）需要包配置文件中的值的SSIS包。因此，我不想直接调用SSIS包。此外，测试人员既没有直接启动SQL代理作业的权限，也不允许他们动态创建SQL代理作业。 sproc（合法地）规避许可问题。最后，集成测试可以针对数十个SSIS包中的一个，但在我的环境中拥有数十个SQL代理作业定义是不实际的。 Ergo，一个sproc启动SQL Agent作业，参数指示要启动的SSIS包。
2 个解决方案
#1
2
Seems like you should be using the SSIS package configurations feature. Your stored proc can update the configuration value(s).
好像你应该使用SSIS包配置功能。您的存储过程可以更新配置值。
#2
0
A different approach could be to dynamically create the job with the SSIS parameters set in the job step. A possible advantage of this is the job is persisted so if there are issues or questions about what a parameter was, the job is around and you can debug the job/job step creation logic.
另一种方法可以是使用作业步骤中设置的SSIS参数动态创建作业。这样做的一个可能的优点是作业是持久的，因此如果存在关于参数是什么的问题或问题，则作业就在您身边，您可以调试作业/作业步骤创建逻辑。
If the Agent is simply a means to running an SSIS package, the DBAs at my last gig created 2 procs, RunSSISPackage and RunSSISPackage32 that did nothing more than call out to dtexec to run a package passing all the supplied parameters.
如果Agent只是运行SSIS包的一种方法，那么我上次演出的DBA创建了2个procs，RunSSISPackage和RunSSISPackage32，除了调用dtexec运行一个传递所有提供参数的包之外什么也没做。
There's also GiiM's approach which can work just as well but until we know what problem you are attempting to solve, it's hard to state what is a better approach.
还有GiiM的方法也可以正常工作但是在我们知道你试图解决什么问题之前，很难说明什么是更好的方法。
#1
2
Seems like you should be using the SSIS package configurations feature. Your stored proc can update the configuration value(s).
好像你应该使用SSIS包配置功能。您的存储过程可以更新配置值。
#2
0
A different approach could be to dynamically create the job with the SSIS parameters set in the job step. A possible advantage of this is the job is persisted so if there are issues or questions about what a parameter was, the job is around and you can debug the job/job step creation logic.
另一种方法可以是使用作业步骤中设置的SSIS参数动态创建作业。这样做的一个可能的优点是作业是持久的，因此如果存在关于参数是什么的问题或问题，则作业就在您身边，您可以调试作业/作业步骤创建逻辑。
If the Agent is simply a means to running an SSIS package, the DBAs at my last gig created 2 procs, RunSSISPackage and RunSSISPackage32 that did nothing more than call out to dtexec to run a package passing all the supplied parameters.
如果Agent只是运行SSIS包的一种方法，那么我上次演出的DBA创建了2个procs，RunSSISPackage和RunSSISPackage32，除了调用dtexec运行一个传递所有提供参数的包之外什么也没做。
There's also GiiM's approach which can work just as well but until we know what problem you are attempting to solve, it's hard to state what is a better approach.
还有GiiM的方法也可以正常工作但是在我们知道你试图解决什么问题之前，很难说明什么是更好的方法。