如何等待 SSIS 包完成 [英] How can I wait for an SSIS package to complete

问题描述

我有一份 SQL 代理工作.作业的第一步是运行一个 SSIS 包，该包将数据从 .txt 文件加载到 SQL 表中.作业的后续步骤操作刚刚加载到表中的数据，直到最终用结果刷新 excel 中的表.我遇到的问题是，在继续执行作业的第 2 步之前，该作业似乎没有等待 SSIS 包完成，因此有时它可以正常工作，有时它会在将数据加载到表中之前启动数据操作并给出错误的结果.在继续之前，我怎样才能让工作等待 SSIS 包完成?

I have a SQL agent job. The first step in the job is to run an SSIS package that loads data from a .txt file into a SQL table. The next steps in the job manipulate the data that was just loaded into the table until finally refreshing a table in excel with results. The problem I am having is that it seems the job does not wait for the SSIS package to complete before moving on to step 2 of job, so sometimes it works fine, and sometimes it starts the data manipulation before the data is loaded into the table and gives incorrect results. How can I get the job to wait for the SSIS package to complete before moving on?

推荐答案

您是否在 SQL Server 2012 中运行此程序?IS Catalog 包有一个名为 SYNCHRONIZED 的特定参数，如果设置为 false，则在包启动后就认为包执行步骤成功.

Are you running this in SQL Server 2012? There is a specific parameter for IS Catalog packages called SYNCHRONIZED which, if set to false, considers a package execution step to be successful as soon as the package starts.

要查看您的作业正在执行的各种命令，请在运行您的作业的服务器上运行此查询:

To see the various commands being executed by your job, run this query on the server running your jobs:

select step_id, step_name, subsystem, command from msdb.dbo.sysjobsteps js
inner join
msdb.dbo.sysjobs j
on j.job_id = js.job_id
where
j.name = <Your Job Name Here>
order by step_id

如果你没有看到

/Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True

在命令字段中，这意味着它正在异步运行，并且您的下一步将在包开始执行后立即开始.

In the command field, that means it's running asynchronously and your next step will start as soon as the package begins execution.

我知道的唯一修复方法是直接修改 dbo.sysjobsteps 表中作业步骤的命令字段，使用 sp_update_jobstep 存储过程，或者使用 GUI 删除并重新添加您的步骤.

The only way I know to fix it is to either modify the command field for your job step in the dbo.sysjobsteps table directly, use the sp_update_jobstep stored procedure, or drop and re-add your step with the GUI.

这篇关于如何等待 SSIS 包完成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！