如何在SQL Server代理工作中创建一个步骤，以运行我的SSIS包?

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 /FILE in my command.

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

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

我找不到任何地方使用@command的合适语法文档——我从某个地方找到的一些旧代码中提取/文件。在作业中运行SSIS包的正确语法是什么?

2 个解决方案

#1

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 Agent through SQL Server Management Studio. Here are the steps to create a job using the Graphical User Interface.

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

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

   进入SQL Server Management Studio。展开SQL Server代理并右键单击作业，然后选择New Job…如截图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.

   提供一个名称，默认的所有者将是创建作业的帐户，但是您可以根据您的需求更改它。如果您愿意，请指定一个类别，并提供描述。请参考截图# 2。

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

   在步骤部分，单击New…如图3所示。

4. On the New Job Step dialog, provide a Step name. Select SQL Server Inegration Services Package from Type. This step will run under SQL Agent Service Account by default. Select the package source as File system and 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 - 9 to know how you can use a different account.

   在新的作业步骤对话框中，提供一个步骤名称。从类型中选择SQL Server Inegration服务包。这个步骤将在默认的SQL代理服务帐户下运行。选择包源作为文件系统，单击省略号浏览到包路径。这将填充包路径。请参考截图# 4。如果不希望在SQL代理服务帐户下执行该步骤，那么请参考步骤#8 - 9，了解如何使用不同的帐户。

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

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

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

   点击OK，在第1步中有一个包，如截图#6所示。类似地，您可以创建不同的步骤。

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

   创建作业后，可以右键单击作业并选择脚本作业—> CREATE To—> New Query Editor窗口，以生成脚本，如截图#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.

   要在不同的帐户下运行SSIS步骤，请在Management Studio上导航到Security——>右键单击Cedentials——>选择New Credential…如截图#8所示。

9. On the New Credential dialog, 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.

   在新的凭据对话框中，提供您希望在SQL作业中执行SSIS步骤的凭据名称、Windows帐户和密码。请参考截图# 9。Credential将被创建，如屏幕截图#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.

    接下来，我们需要创建一个代理。在Management Studio中，导航到SQL Server Agent—>代理—>在SSIS包执行上右键单击—>选择New Proxy…如图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.

    在新的代理帐户窗口中，提供一个代理名称，选择新创建的凭据，提供描述并选择SQL Server Integration Services包，如屏幕截图#12所示。应该创建代理帐户，如屏幕截图#13所示。

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

    现在，如果您回到SQL job中的步骤，您应该看到在运行中新创建的代理帐户为下拉。请参考截图# 14。

Hope that helps.

希望有帮助。

Screenshot #1:

截图# 1:

Screenshot #2:

截图# 2:

Screenshot #3:

截图# 3:

Screenshot #4:

截图# 4:

Screenshot #5:

截图# 5:

Screenshot #6:

截图# 6:

Screenshot #7:

截图# 7:

Screenshot #8:

截图# 8:

Screenshot #9:

截图# 9:

Screenshot #10:

截图# 10:

Screenshot #11:

截图# 11:

Screenshot #12:

截图# 12:

Screenshot #13:

截图# 13:

Screenshot #14:

截图# 14:

#2

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，我认为您需要显式地设置要调用的子系统，一个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 this MSDN 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:\安装\上载。作为一个T-SQL命令。