SSIS Incremental Load....help!

Hi, forum - need help on this: I am building an SSIS package that gets feeds from other db servers, and I want to capture ONLY NEW ROWS to move over from A to B. I looked at this article on incremental loads which was very helpful : http://www.sqlservercentral.com/articles/SSIS/62063/ . This used a lookup to compare rows in source and destination. Seems to work like a charm for a small table, however, the table(s) I'm dealing with has 13+ MILLION Rows. Therefore, this method takes WAY TOO LONG. Any ideas how to grab the NEW rows in less time? (Will have a follow-up Q, but want to answer this first) Many Thanks!

1. If you have any identity column in the table you can just compare the Max ID from both databases and findout.
2. Otherwise you need to develop some sort of auditing mecahnism (trigger could be used.) to get the new rows.
I had a differnt problem where I couldn't touch the database much for logging. As there was an existing publication, I created a new subscription ( where I deleted the update and delete procedures). After the completion of SSIS package I deleted the data.
Hope this helps

Ok, thanks for reply. More specifically, then how can I effectively do
Select * from SourceDB.dbo.table s where s.maxid not in (select d.maxid from DestDB.dbo.table d)?
How could I reference the destination? - syntax please. Without using linked servers. THX

In your case, you don't have to do that way; Run
SELECT MAX(ID) FROM dbo.table
from to destination server and you can assign that to a variable in the SSIS package.
Then you can select all greater than the variable from the source database

Thx again. That sounds like a good idea.
But I need to add this into the mix. I have three servers with identical schemas, and the ID column is unique to each server, BUT, may exist on ALL servers. However, the ID + SNID is unique accross all Servers. So, could I still get maxID, where NOT EXIST (ID and SNID). Help me out please!

hi, satya:
I was trying to avoid using Linked Servers for policy/security reasons, and will try preethi's suggestion. If that ends up being too slow, I may have no choice. THX

Ok, I still need help with this! How can I pass the results of the query to a variable. can you give me some steps? I'm not having any luck. THX!!!
NEVER MIND THIS LINK HELPED ME OUT! http://www.sqlis.com/58.aspx

Hi,
In order to pass the results of the query to a varibale, kindly follow the steps.
1. Create a variable with OBJECT datatype, so that it will hold the results of the query.
2. Assign the results of the query to the variable, when you are firing the query like as shown.
@variable = select * from employee;
3. Now the results got stored in the variable, so you can use this variable accordingly .
Hope this will help you.