Not very elegant, but:

WITH myheads AS (select header, 0 AS orderfield from table_1 group by header)

, myfeet AS (select header,CAST ('' AS VARCHAR) AS citation ,2 AS orderfield FROM myheads)

,...

I'm a bit confused as to why you need the hash value. Why not select those 5 records in your lookup, and test for changes to any of them...

edit: nm, the answer was in your original post

You're right, I thought of how to do it in 2012 (parameters), then tried to figure how to do parameters in 2008, but in your problem it would be the...

this question doesn't make sense on multiple fronts:

First, you provided no DDL, making things harder. We don't know the data type of amount

Second, your query does not even run with...

My first pass:

CREATE TABLE #EmergencyPhoneNumber(PersonPhoneId int Primary Key,

PersonId uniqueidentifier,

PhoneId int,

IsPrimaryNumber bit,

PhoneExtension varchar(10),

Comments varchar(255))

INSERT INTO #EmergencyPhoneNumber VALUES(1,'4EE56555-91C6-4755-AC8E-0099D297445A',2771,0,'1111','this is phone1')

INSERT INTO #EmergencyPhoneNumber VALUES(2,'4EE56555-91C6-4755-AC8E-0099D297445A',2771,1,'','this is phone2')

INSERT INTO #EmergencyPhoneNumber VALUES(3,'4EE56555-91C6-4755-AC8E-0099D297445A',2772,0,1111,'this is phone3')

;

WITH numbertelns AS...

A bit too guessable. If it was going to be select 3, there should have been a few more options. Otherwise its 'one of these things is not...

Maybe change the statement to select db_name() and make sure you have a connection and its pointing to the right db.

Did you check your the link I provided? I thought that would do it for you.

I would recommend looking into BIML. There is a Stairway for it.

Doesn't do exactly what you are asking, but probably does what you want in a better way.

You could try playing with the defaultbuffermaxrows and defaultbuffersize settings.

I suspect what is happening is this.

with a stored procedure as data source, the procedure must finish executing, then it dumps...

Yes. How else would it do it?

Edit: just realized you already proposed most of the answer.

Yes, its because the logic of a not in is to check <> AND for each value.

In the meantime, the IN...

What version of SSIS is this? If its 2012 you can pass a parameter from the master to the child packages, so you could definitely do this.

If its 2008,...

How big is the lookup table? How do you cache it?

Where are the rows you select being written to, and what are the settings on your destination?

Are there...