如何在我的SQL Server代理作业中创建一个步骤，该作业将运行我的SSIS包？ [英] How do I create a step in my SQL Server Agent Job which will run my SSIS package?

问题描述

我正在尝试创建一个自动化作业，以便SQL Server代理运行。

以下是我到目前为止所做的工作：

  EXEC sp_add_job @job_name ='我的工作'
，@ description ='我的第一个SSIS工作'
，@ job_id = @jobid OUTPUT 
 
 EXEC sp_add_jobstep @job_id = @ jobid 
，@ step_name = N'Upload Data'
，@ step_id = 1 
，@ command = N'/ FILED：\Installs\Upload.dtsx 
 EXEC sp_add_jobstep @job_id = @jobid 
，@ step_name = N'Download Data'
，@ step_id = 2 
，@ command = N'/ FILE \Installs\Download.dtsx'
  

不幸的是，当我运行这个，

'/'附近的语法不正确

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

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

解决方案

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

1. 转到SQL Server Management Studio。展开 SQL Server代理并右键单击作业，然后选择新作业... ，如截图＃ strong> 1 。




2. 提供一个名称，默认情况下，所有者是创建作业的帐户，但您可以根据需要进行更改。如果您想要指定类别，并提供说明。




3. 在步骤部分，点击新建...




4. 在新建作业步骤对话框中，提供步骤名称。从类型中选择 SQL Server Inegration服务包。默认情况下，此步骤将在 SQL代理服务帐户下运行。选择包源为文件系统，然后通过单击省略号浏览到包路径。这将填充包路径。请参阅屏幕截图＃ 4 。如果您不希望在 SQL代理服务帐户 下执行该步骤，请参阅步骤＃ 8




5. 如果您有一个SSIS配置文件（.dtsConfig），您可以使用 9 程序包，请点击配置标签，并添加配置文件，如屏幕截图＃ 5 所示。




6. 点击确定，在步骤1中有包，如屏幕截图＃ 6 所示。同样，您可以创建不同的步骤。




7. 创建作业后，您可以右键单击作业并选择脚本作业 - > CREATE To - >新建查询编辑器窗口生成脚本，如屏幕截图＃ 7 所示。




8. SSIS步骤在不同的帐户下，在Management Studio上，导航到安全 - >右键单击Cedentials - >选择新凭证... ，如截图＃ 8 所示。




9. $ c>新建凭据对话框，提供凭据名称，Windows帐户和密码，您希望在其中执行SQL作业中的SSIS步骤。请参阅屏幕截图＃ 9 。




10. 接下来，我们需要创建一个代理。在Management Studio上，导航到 SQL Server代理 - >代理 - >右键单击SSIS包执行 - >




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




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

希望有帮助。

屏幕截图1：

截图＃2：

截图＃3 ：

Screenshot＃4：

截图＃5：

Screenshot＃6：

/i.stack.imgur.com/3lpPY.pngalt =7>

Screenshot＃8：

屏幕截图9：

截图＃10：

Screenshot＃11： / p>

屏幕截图＃12：

截图＃13：

截图＃14： / strong>

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

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?

解决方案

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.

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.

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 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.

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.

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 Window to 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 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.

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 as drop down. Refer screenshot #14.

Hope that helps.

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Screenshot #9:

Screenshot #10:

Screenshot #11:

Screenshot #12:

Screenshot #13:

Screenshot #14:

这篇关于如何在我的SQL Server代理作业中创建一个步骤，该作业将运行我的SSIS包？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！