2012년 7월 19일 목요일 오후 9:21
I have a table with following Columns(at the Server A on which i have only read access) in which GUID is a unique identifier or an identity column.
i need to implement incremental logic on this table and load changes to Server B ,but the issue is i don't have an update date on the source table to grab changes.
The size of the table is huge with 99 million rows so i dropped the option of doing lookup with SSIS,as it was taking long time.
Is there any way of Incremental loading.
But please know that i have only read access to the source table.
Any idea is really appreciated. Thanks.
2012년 7월 20일 금요일 오전 6:28
- You can try to use merge. Does it performs better than Lookup?
- Another option is CDC (Change Data Capture)
- The last option is to truncate the table, and re-load all data (using SSIS OLEDB Destination Fast Load)
- 답변으로 제안됨 PChiragS 2012년 8월 7일 화요일 오후 6:07
2012년 7월 23일 월요일 오후 1:53
Thanks for your reply Irusul.
1. As i don't have update to get the Delta transaction and then do Merge onto Destination table. If you mean to use merge is to use complete source table and then do merge to destination its performing same as Lookup eating up all my memory.
2. For CDC option i have only read access onto source server and moreover its a replicated server.
3. Currently i am stick to close to last option, what i am doing is truncating the table and reading split data(reading data from source and splitting into multiple tables on specific condition at least i am gaining read speed here) and then doing bulk insert from all these tables to the destination table.
I really appreciate your effort. Thanks a lot.
2012년 7월 24일 화요일 오전 8:02
do you want to keep historical changes ? I've got a mad idea here but we need to try it out on reality incase of performance
1- dump the table into a staging area ( using SSIS ). keep always last version only (let's assume yesterday data)
2- apply ColumnStore Index on all columns to help out with performance here.
3- Dump today's data, apply Columnstore index as well. then do a left join where all columns equals. the changed records or added ones will remain to update to your destination keeping track of time if you want.
4- to get the deleted records reverse the left join equaling only the GUID.
The Key here is the ColumnStore index of SQL Server 2012, you can never do it without it. it'll seem like a lunatic action.
Wagdy Ishac www.sqldair.com
- 답변으로 제안됨 Eileen ZhaoMicrosoft Contingent Staff, Moderator 2012년 8월 1일 수요일 오전 8:45
2012년 7월 24일 화요일 오전 10:46IF CDC is not an option then full load (truncate and insert) is only option
Database Consultants NY