I have a package that executes 5 separate selects from an oledb source (which
is a SQL Server), sorts each select, and then merge joins them one by one
(select 1 and 2 merge, the output of which merges with select 3, the output
of which merges with select 4, the output of which merges with select 5). At
the end, it should insert into a oledb destination (which is another table in
a different database on the same server that is the source).

Each select returns about 250,000 rows, each row has anywhere between 30-75
columns. The sorts are each on 11 rows as are the merges.

So, it runs up a lot of memory usage. I have a BufferTempStoragePath set
up, and I've tried throttling the thing off the defaults by limiting the
maximum threads of each sort, and the total number of engine threads.

But, it never finishes execution. It always makes it through the selects,
and it makes it through 4 of the 5 sorts. However, while finishing the first
sort (which then sends rows down to merge 1, and through that merge to the
one that merges with sort 3), it stops with the same counts listed. It
doesn't fail or throw an error, it simply stops.

At this point, it will do nothing else. If I then stop executing the
package, it will throw a DtsDebugHost application error, but I can't attach
to the process to see what is going on.

I have logging turned on, but nothing is listed that I can see of use.

Any ideas on how to figure out what is happening? I've sorted the selects
in query analyzer to try and see if there's something strange with the data,
but nothing looks obvious.

TIA
jdn

Well, I 'fixed' it in an unsatisfactory way. I created a new SSIS project
and recreated the steps, and it functions fine.

If I ever figure out a difference between the two projects, I'll post it.

jdn

"jdn" wrote:

Unfortunately, the problem has returned. It will simply stop at various
places. No error. If I stop debugging, it stops without throwing an
exception.

Apparently, this thing just isn't reliable?

"jdn" wrote: