SSIS数据流任务挂在执行前阶段 [英] SSIS Data Flow Task hangs on excecution of Pre-excecute phase

问题描述

我有一个执行中的数据流任务。

流程很简单，对不同的表进行两个查询（两个都有两个联接），然后通过一个公共ID对otuputs进行排序和合并，向所有记录添加一个静态列，保存该行计算用户变量以备后用，最后插入另一个数据库的表中。
我们正在使用OLE DB源和目标。源是MSSQL 2000，目标是MSSQL 2012


症状：

执行时，数据流会出现通常的黄色运行 图标。但是，当您双击以查看数据流时，没有一个元素具有任何黄色，红色或绿色标记。

这种情况持续了很长时间，最初持续了20分钟左右，之后又开始变长甚至根本没有返回。

输出显示：

信息：在SSIS加载沙箱表处为0x40043006。管道：开始执行执行阶段。

信息：在SSIS加载沙箱表处为0x40043007 .Pipeline：预执行阶段开始。

在执行停止之前，仅此而已。

是的，这以前曾经有用。是的，我们使用单个查询（在存储过程中）执行此ETL，但我们希望将所有步骤迁移到SSIS。

I have a Data Flow Task that is hanging on excecution.
The flow is simple, makes two queries to different tables (Both with a couple of joins), then sorts and merges the otuputs through a common id, adds a static column to all the records, saves the row count in a user variable for later use and finally inserts into a table on another DB. We are using OLE DB Sources and Destination. Source is MSSQL 2000 and Destination is MSSQL 2012

Symptoms:

When excecuting, the Data Flow gets the usual yellow "running" icon. However when you double click to see the Data Flow, non of the elements have any yellow, red or green mark.

This goes on for long periods of time, at first it lasted around 20 minutes, after that it started getting longer or simply not returning at all.

Output shows:
Information: 0x40043006 at Load Sandbox Table, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Load Sandbox Table, SSIS.Pipeline: Pre-Execute phase is beginning.
And nothing more until the excecution is stopped.

Yes, this has worked before. And yes, we have used a single query (in a stored procedure) to do this ETL but we wanted to migrate all the steps to SSIS.

< b>失败的解决方案：

没有查找。

任务流的默认缓冲区大小增加到40485760，然后增加到80971520。

该任务的默认缓冲区最大行设置为1000000。

该任务的延迟验证设置为True。

任务中的所有元素均设置为Validate External Data为False。

两个查询都具有：
SET FMTONLY OFF；

SET NOCOUNT ON；
在开始时添加了。

两个查询的 MAXDOP 都设置为1。

将项目的运行64位运行时设置为False。

将目标负载从表或视图更改为表或视图-快速加载，没有锁定或约束。

将每个批次的行设置为1000，以实现快速加载。

一些解决方法建议将任务流分成两个或多个任务流。但这是不可能的，因为我们需要做的是合并两个源查询中找到的信息。

Failed solutions:

There are no lookups.

Default buffer size for the task flow was increased to 40485760 then to 80971520.

Default buffer max rows for task was set to 1000000.

Delay Validation was set to True for the task.

All elements inside the task were set Validate External Data to False.

Both queries had:
SET FMTONLY OFF;
SET NOCOUNT ON;
added at the beggining.

Both queries had MAXDOP set to 1.

Setting project's Run 64 bit Runtime to False.

Changed destination load from Table or View to Table or View - Fast load with no locks or constraints.

Set rows per batch to 1000 for fast load.

Some work arounds propose to separate the task flow into two or more task flows. But this is not possible since what we need to do is a merge of the information found on both source queries.

其他位：
我真的希望有人能帮助我。我对SSIS还是很陌生，这是我第一次使用它。我通常与Pentaho一起使用ETL，但是客户需要在SSIS上实现解决方案。我一直在与这个问题作斗争两天，而我的想法已经耗尽。

Extra bits: I really hope someone can help me. I am fairly new to SSIS, this is the first time I use it. I usually work with Pentaho for my ETL but the client needs the solution to be implemented on SSIS. I've been battling with this issue for a couple of days now and I'm starting to run out of ideas to solve it.

当通过命令行运行时，它也卡住了，我得到以下输出：

When ran through the command line it gets stuck too and I get the following output:

Progress: 2013-03-19 14:36:26.21
   Source: Load Sandbox Table
   Validating: 0% complete
End Progress
Progress: 2013-03-19 14:36:26.21
   Source: Load Sandbox Table
   Validating: 12% complete
End Progress
Progress: 2013-03-19 14:36:26.22
   Source: Load Sandbox Table
   Validating: 25% complete
End Progress
Progress: 2013-03-19 14:36:26.22
   Source: Load Sandbox Table
   Validating: 37% complete
End Progress
Progress: 2013-03-19 14:36:26.23
   Source: Load Sandbox Table
   Validating: 50% complete
End Progress
Progress: 2013-03-19 14:36:26.25
   Source: Load Sandbox Table
   Validating: 62% complete
End Progress
Progress: 2013-03-19 14:36:26.25
   Source: Load Sandbox Table
   Validating: 75% complete
End Progress
Progress: 2013-03-19 14:36:26.25
   Source: Load Sandbox Table
   Validating: 87% complete
End Progress
Progress: 2013-03-19 14:36:26.25
   Source: Load Sandbox Table
   Validating: 100% complete
End Progress
Warning: 2013-03-19 14:36:26.26
   Code: 0x80047076
   Source: Load Sandbox Table SSIS.Pipeline
   Description: The output column "ITEM_OID (1)" (47) on output "Merge Join Outp
ut" (28) and component "Merge Join" (11) is not subsequently used in the Data Fl
ow task. Removing this unused output column can increase Data Flow task performa
nce.
End Warning
Progress: 2013-03-19 14:36:26.27
   Source: Load Sandbox Table
   Prepare for Execute: 0% complete
End Progress
Progress: 2013-03-19 14:36:26.27
   Source: Load Sandbox Table
   Prepare for Execute: 12% complete
End Progress
Progress: 2013-03-19 14:36:26.27
   Source: Load Sandbox Table
   Prepare for Execute: 25% complete
End Progress
Progress: 2013-03-19 14:36:26.27
   Source: Load Sandbox Table
   Prepare for Execute: 37% complete
End Progress
Progress: 2013-03-19 14:36:26.27
   Source: Load Sandbox Table
   Prepare for Execute: 50% complete
End Progress
Progress: 2013-03-19 14:36:26.27
   Source: Load Sandbox Table
   Prepare for Execute: 62% complete
End Progress
Progress: 2013-03-19 14:36:26.27
   Source: Load Sandbox Table
   Prepare for Execute: 75% complete
End Progress
Progress: 2013-03-19 14:36:26.27
   Source: Load Sandbox Table
   Prepare for Execute: 87% complete
End Progress
Progress: 2013-03-19 14:36:26.27
   Source: Load Sandbox Table
   Prepare for Execute: 100% complete
End Progress
Progress: 2013-03-19 14:36:26.31
   Source: Load Sandbox Table
   Pre-Execute: 0% complete
End Progress
Progress: 2013-03-19 14:36:26.31
   Source: Load Sandbox Table
   Pre-Execute: 12% complete
End Progress
Progress: 2013-03-19 14:36:26.31
   Source: Load Sandbox Table
   Pre-Execute: 25% complete
End Progress
Progress: 2013-03-19 14:36:26.34
   Source: Load Sandbox Table
   Pre-Execute: 37% complete
End Progress
Progress: 2013-03-19 14:36:45.69
   Source: Load Sandbox Table
   Pre-Execute: 50% complete
End Progress

然后再次冻结。

解决方案 （将其发布在这里是因为我无法回答自己的问题再过5个小时，我会在允许的时候这样做。）

我终于明白了。

事实证明，验证存在问题，但不仅是SSIS元素都要通过验证，如问题的第四个失败解决方案所述。

CONNECTIONS也得到验证已验证，并具有自己的延迟验证属性，该属性需要设置为true。

之后，执行时间从整个过程的40分钟以上或不到一分钟缩短到不到一分钟（这只是更大的过程中的一个步骤）。

我希望遇到相同问题的人可以轻松找到此解决方案，因为有很多人遇到此问题，几乎没有在线发布解决方案。

SOLUTION (Posting this here because I can't answer my own question for another 5 hours, I'll do it when I'm allowed to.)
I finally got it.
It turns out there is an issue with the validation, but not only SSIS elements go through that validation, as stated in the fourth failed solution of the question.
The CONNECTIONS also get validated and have their own Delay Validation property, which needs to be set to true.
After that the excecution time went from 40+ mins or no running to less than a minute for the full process (This is just one step of a much bigger process)
I hope people with this same problem can find this solution easily because there's a lot of people running into this problem and nearly no solutions posted online.

简而言之：检查任务中涉及的所有元素，包括数据库连接的 都将Delay Validation属性设置为True。

In a nutshell: Check that all your elements involved in the task, including the DB connections have Delay Validation Property set to True.

推荐答案

我终于明白了。
事实证明，验证存在问题，但不仅是SSIS元素都要通过验证，如问题的第四个失败解决方案所述。
CONNECTIONS也将得到验证，并具有自己的延迟验证属性，需要将其设置为true。
之后，执行时间从整个过程的40分钟以上或不到40分钟减少到不到一分钟（这只是更大过程的一个步骤）
我希望遇到同样问题的人可以容易找到此解决方案，因为有很多人遇到此问题，几乎没有在线发布解决方案。

I finally got it. It turns out there is an issue with the validation, but not only SSIS elements go through that validation, as stated in the fourth failed solution of the question. The CONNECTIONS also get validated and have their own Delay Validation property, which needs to be set to true. After that the excecution time went from 40+ mins or no running to less than a minute for the full process (This is just one step of a much bigger process) I hope people with this same problem can find this solution easily because there's a lot of people running into this problem and nearly no solutions posted online.

简而言之：您参与该任务的元素（包括数据库连接）的延迟验证属性设置为True。

In a nutshell: Check that all your elements involved in the task, including the DB connections have Delay Verification Property set to True.

这篇关于SSIS数据流任务挂在执行前阶段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！