Truly bizarre. I don't believe the problems I was having have anything to do with lack of memory (except maybe my own).

The OLEDB source I had was using a SQL statement to check against the OLEDB destination table to see if rows already existed on that table. Only rows that didn't exist on the destination table were returned. The code was functionally similiar to this:

SELECT *

FROM sourcetable

WHERE recordkey NOT IN (

SELECT DISTINCT recordkey

FROM destinationtable)

If I changed the SQL statement to use a different table than the eventual destination table, the package worked like a charm.

If I changed the SQL statement to return the top something rows, even if the top value exceeded the number of rows in the source table, and kept the check for existing records pointing to the destination table, the package worked like a charm.

But if I kept everything the same as in the SQL example, and the number of rows exceeded something like 1,000, strange things happened. It was almost as if there was a loop. The data flow never completed.

Maybe it does have to do with cache or memory. Maybe some records were obtained from the source, written to the dataflow pipe, then maybe it recognized the key already being "written" to the destination table and it got confused. I just don't know.

I changed the SQL, followed the OLEDB source with a lookup task, looked up the record key in the destination table, conditionally passed records that didn't already exist from the lookup, then wrote it to the destination table. And ... the package worked like a charm. Something like 50,000 rows were processed.

I still don't quite understand what was happening. All I know is that, after changing things around a bit, it all works now.

Chalk it up to problematic SSIS error and warning messages leading me down errant paths.