您的位置: 首页  >  技术问答  >  如何在我的 SQL Server 代理作业中创建一个步骤来运行我的 SSIS 包?

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

分类: 技术问答 • 2022-03-13 21:14:44

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

问题描述：

我正在尝试为 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.

这是我目前所拥有的:

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:InstallsUpload.dtsx"'
EXEC sp_add_jobstep @job_id = @jobid
                    ,@step_name = N'Download Data'
                    ,@step_id = 2
                    ,@command=N'/FILE "D:InstallsDownload.dtsx"'

不幸的是，当我运行此程序时，出现错误提示

Unfortunately when I run this, I get an error saying

'/' 附近的语法不正确

Incorrect syntax near '/'

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

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

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

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?

答

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

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. 转到 SQL Server Management Studio.展开 SQL Server Agent 并右键单击 Jobs，然后选择 New Job...，如屏幕截图 #1 所示.

  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.

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

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

在新建作业步骤"对话框中，提供步骤名称.从类型中选择 SQL Server Inegration Services Package.默认情况下，此步骤将在 SQL 代理服务帐户 下运行.选择包源为 File system 并通过单击省略号浏览到包路径.这将填充包路径.请参阅屏幕截图 #4.如果您不想在 SQL 代理服务帐户 下执行该步骤，请参阅步骤 #8 - 9 了解如何使用其他帐户.

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.

如果您有包的 SSIS 配置文件 (.dtsConfig)，请单击配置选项卡并添加配置文件，如屏幕截图 #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.

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

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

创建作业后，您可以右键单击该作业并选择 Script Job as -->创建到 -->新的查询编辑器窗口生成脚本#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.

要在不同帐户下运行 SSIS 步骤，请在 Management Studio 上导航到 Security -->右键单击凭据 -->选择 New Credential...，如屏幕截图 #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.

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

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.

接下来，我们需要创建一个代理.在 Management Studio 上，导航到 SQL Server Agent -->代理 -->右键单击 SSIS 包执行 -->选择 New Proxy...，如屏幕截图 #11 所示.

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.

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

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 作业中的步骤，您应该会在 运行方式 下拉列表中看到新创建的代理帐户.请参阅屏幕截图 #14.

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.

希望有所帮助.

屏幕截图 #1:

屏幕截图 #2:

屏幕截图 #3:

屏幕截图 #4:

屏幕截图 #5:

截图 #6:

屏幕截图 #7:

截图 #8:

屏幕截图 #9:

屏幕截图 #10:

屏幕截图 #11:

屏幕截图 #12:

屏幕截图 #13:

屏幕截图 #14:

相关推荐