当前位置：theitroad>如何在我的 SQL Server 代理作业中创建一个步骤来运行我的 SSIS 包？

如何在我的 SQL Server 代理作业中创建一个步骤来运行我的 SSIS 包？

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/6712811/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 11:21:10 　来源:igfitidea点击:

How do I create a step in my SQL Server Agent Job which will run my SSIS package?

sqlsql-serverdatabasetsqlssis

提问by Slider345

I'm trying to create an automated job for the SQL Server Agent to run. The job is supposed to run my SSIS package.

我正在尝试为 SQL Server 代理创建一个自动化作业来运行。该作业应该运行我的 SSIS 包。

Here's what I have so far:

这是我到目前为止所拥有的：

EXEC sp_add_job @job_name = 'My Job'
            ,@description = 'My First SSIS Job'
            ,@job_id = @jobid OUTPUT

EXEC sp_add_jobstep @job_id =@jobid
                    ,@step_name = N'Upload Data'
                    ,@step_id = 1
                    ,@command=N'/FILE "D:\Installs\Upload.dtsx"'
EXEC sp_add_jobstep @job_id = @jobid
                    ,@step_name = N'Download Data'
                    ,@step_id = 2
                    ,@command=N'/FILE "D:\Installs\Download.dtsx"'

Unfortunately when I run this, I get an error saying

不幸的是，当我运行它时，我收到一条错误消息

Incorrect syntax near '/'

'/' 附近的语法不正确

I suspect it's complaining about the /FILEin my command.

我怀疑它在抱怨/FILE我的命令。

I can't find documentation about the appropriate syntax to use within @command anywhere -- I pulled /FILEout of some old code I found somewhere. What is the correct syntax for running an SSIS package in a job?

我无法在任何地方找到有关在 @command 中使用的适当语法的文档——我/FILE从某处找到的一些旧代码中取出。在作业中运行 SSIS 包的正确语法是什么？

回答by

I ran the SQL script in the question but didn't encounter any errors. So, I am not sure what is causing the error in your case. However, if you have access to SQL Server Agentthrough SQL Server Management Studio. Here are the steps to create a job using the Graphical User Interface.

我在问题中运行了 SQL 脚本，但没有遇到任何错误。所以，我不确定是什么导致了你的情况下的错误。但是，如果您可以SQL Server Agent通过 SQL Server Management Studio访问。以下是使用图形用户界面创建作业的步骤。

  1. Go to SQL Server Management Studio. Expand SQL Server Agentand right-click on Jobs, then select New Job...as shown in screenshot #1.

  2. Provide a name and Owner by default will be the account that creates the job but you can change it according to your requirements. Assign a Category if you would like to and also provide a description. Refer screenshot #2.

  3. On the Steps section, click New...as shown in screenshot #3.

  4. On the New Job Step dialog, provide a Step name. Select SQL Server Inegration Services Packagefrom Type. This step will run under SQL Agent Service Accountby default. Select the package source as File systemand browse to the package path by clicking on ellipsis. This will populate the Package path. Refer screenshot #4. If you don't want the step to execute under the SQL Agent Service Account, then refer the steps #8- 9to know how you can use a different account.

  5. If you have a SSIS configuration file (.dtsConfig) for the package, click on the Configurationstab and add the Configuration file as shown in screenshot #5.

  6. Click OK and there is the package in step 1 as shown in screenshot #6. Similarly, you can create different steps.

  7. Once the job has been created, you can right-click on the job and select Script Job as --> CREATE To --> New Query Editor Windowto generate the script as shown in screenshot #7.

  8. To run the SSIS step under different account, on the Management Studio, navigate to Security --> right-click on Cedentials --> select New Credential...as shown in screenshot #8.

  9. On the New Credentialdialog, provide a Credential name, Windows account and Password under which you would like to execute SSIS steps in SQL jobs. Refer screenshot #9. Credential will be created as shown in screenshot #10.

  10. Next, we need to create a proxy. On the Management Studio, navigate to SQL Server Agent --> Proxies --> right-click on SSIS Package Execution --> select New Proxy...as shown in screenshot #11.

  11. On the New Proxy Account window, provide a Proxy name, select the newly created Credential, provide a description and select SQL Server Integration Services Package as shown in screenshot #12. Proxy account should be created as shown in screenshot #13.

  12. Now, if you go back to the step in SQL job, you should see the newly created Proxy account in the Run asdrop down. Refer screenshot #14.

  1. 转到 SQL Server 管理工作室。展开SQL Server Agent并右键单击 Jobs，然后选择New Job...如屏幕截图 # 1所示。

  2. 提供名称，默认情况下所有者将是创建作业的帐户，但您可以根据需要更改它。如果您愿意，请分配一个类别并提供说明。参考截图# 2。

  3. 在 Steps 部分，New...如截图 # 3所示点击。

  4. 在“新建作业步骤”对话框中，提供步骤名称。SQL Server Inegration Services Package从类型中选择。这一步将SQL Agent Service Account默认运行。选择包源为File system并通过单击省略号浏览到包路径。这将填充包路径。参考截图# 4。如果您不想在 下执行该步骤SQL Agent Service Account，请参阅步骤 # 8- 9以了解如何使用不同的帐户。

  5. 如果您有包的 SSIS 配置文件 (.dtsConfig)，请单击“配置”选项卡并添加配置文件，如屏幕截图 # 5所示。

  6. 单击“确定”，就会出现步骤 1 中的包，如屏幕截图 # 6所示。同样，您可以创建不同的步骤。

  7. 创建作业后，您可以右键单击该作业并选择Script Job as --> CREATE To --> New Query Editor Window生成脚本，如屏幕截图 # 7所示。

  8. 要在不同帐户下运行 SSIS 步骤，请在 Management Studio 上导航至Security --> right-click on Cedentials --> select New Credential...屏幕截图 # 8 中所示的位置。

  9. 在New Credential对话框中，提供您希望在 SQL 作业中执行 SSIS 步骤的凭据名称、Windows 帐户和密码。参考截图# 9。凭据将被创建，如屏幕截图 # 10所示。

  10. 接下来，我们需要创建一个代理。在 Management Studio 上，导航到SQL Server Agent --> Proxies --> right-click on SSIS Package Execution --> select New Proxy...屏幕截图 # 11 中所示。

  11. 在“新建代理帐户”窗口中，提供代理名称，选择新创建的凭据，提供描述并选择 SQL Server 集成服务包，如屏幕截图 # 12所示。应创建代理帐户，如屏幕截图 # 13所示。

  12. 现在，如果您返回到 SQL 作业中的步骤，您应该会在“运行方式”下拉列表中看到新创建的代理帐户。请参阅屏幕截图 # 14。

Hope that helps.

希望有帮助。

Screenshot #1:

截图 #1：

1

1

Screenshot #2:

截图#2：

2

2

Screenshot #3:

截图 #3：

3

3

Screenshot #4:

截图 #4：

4

4

Screenshot #5:

截图 #5：

5

5

Screenshot #6:

截图 #6：

6

6

Screenshot #7:

截图 #7：

7

7

Screenshot #8:

截图 #8：

8

8

Screenshot #9:

截图 #9：

9

9

Screenshot #10:

截图 #10：

10

10

Screenshot #11:

截图 #11：

11

11

Screenshot #12:

截图 #12：

12

12

Screenshot #13:

截图 #13：

13

13

Screenshot #14:

截图 #14：

14

14

回答by DaveE

If you're building the job using the sp_add... procs, I think you need to explicitly set the subsystem to be called, a la

如果您使用 sp_add... procs 构建作业，我认为您需要显式设置要调用的子系统，a la 

EXEC sp_add_jobstep @job_id = @jobid,
@step_name = N'Upload Data',
@step_id = 1,
@subsystem = 'DTS',
@command=N'/FILE "D:\Installs\Upload.dtsx"'

This is equivalent to @Siva's Screenshot #4 'Type'='SQL Server Integration Services Package'.

这相当于@Siva 的截图 #4 'Type'='SQL Server Integration Services Package'。

See thisMSDN article for the complete list of subsystems.

有关子系统的完整列表，请参阅此MSDN 文章。

EDIT: 'TSQL' is the default, so your current setup is trying to execute '/FILE "D:\Installs\Upload.dtsx"' as a T-SQL command.

编辑：'TSQL' 是默认设置，因此您当前的设置正在尝试将 '/FILE "D:\Installs\Upload.dtsx"' 作为 T-SQL 命令执行。

相关推荐

SQL 一次更新多行

SQL 一次更新多行

SQL 如何连接两个表但只返回不匹配的行？

SQL 如何连接两个表但只返回不匹配的行？

如何在 SQL Server 中拆分字符串

如何在 SQL Server 中拆分字符串

比较 SQL Server 中的两个字符串

比较 SQL Server 中的两个字符串

相关推荐
最近更新
标签