var05 (11/19/2012)
Hi All,I have to load data from one server to another server on daily basis using SSIS Package.
I have only one table to be populated from Source ->destination. But the source contains millions and millions of records.. I need to set up a job nightly, so that data is picked up from Source and loaded into the destination server.
Say if there are 100 new data coming in today into the source,then I wanted to look up the destinationand load only the newly inserted data....Is SSIS look up is the best option to look up the existing records in destination table and load the new ones from the source table???
Any best practices(performance wise) to implement the SSIS package to load data from Source->destination by looking up millions of records and load the new ones???
The Lookup transofrmation will work fine. I'm assuming you have a unique business key that you can verify if a record is present or not with. Multiple millions of rows isn't all that many -- of course this is a generalization and depends upon your specific load window, network performance, ect....
Do you have a way to identify new and/or updated records on the source system? Something like a last updated and create date columns? If so, then you only need to pull records from the source that have changed in the last day.
HTH,
Rob