What are the incremental loading techniques we have in Sql and SSIS 2008 ? What are advantages and disadvantages of the techniques over each other .arun

Hi Arun, you can use Change Data Caputre (CDC), in-directly you can use SCD for incremental upload with type 1. Thanks, ayyappan http://sqlserverrider.blogspot.com

Lookup Data Transformation is the technique. How to use: http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/8fe33aca-0a4c-45b2-b076-aee80a5453f2 Another related post with links and ideas: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/567f9ccb-9f41-4beb-ad99-036f8fd1adf9Arthur My Blog

I would suggest you to look into CDC in SQL 2008. here is sample for incremental feeds http://www.mssqltips.com/tip.asp?tip=1755http://uk.linkedin.com/in/ramjaddu

To detect the changes: CDC or Change Tracking (I prefer CDC). If that is not an option, you can use a LEFT OUTER JOIN in TSQL or a Lookup component in the dataflow to seperate updates from inserts. If the datasets are very large, you could consider a MERGE JOIN in the dataflow, so you can sort of 'stream' the data to a join. Once you know what the updates and the inserts are, there are several options: * use the TSQL MERGE statement (easy to use once you know the syntax) * use a conditional split in the dataflow. Send the inserts directly to the destination and the updates to a staging table. After the dataflow, do a set-based TSQL update using that staging table and the dataflow * avoid the SSIS SCD component. At all costs... * you can also use the open-source Dimension Merge component: http://dimensionmergescd.codeplex.com/ To avoid too many costly updates, you can use a checksum to determine if a row is actually updated, or if it is just the same row.MCTS, MCITP - Please mark posts as answered where appropriate.

Does Change data capture work on views?arun

Tables are getting updated even though through the means of view, so indirectly, yes.Arthur My Blog

If there is timestamp field like modified date in the table then we don't need CDC correct?arun

If there is timestamp field like modified date in the table then we don't need CDC correct? arun Correct. But you are responsible yourself for the implementation and maintenance of the change detection. You musn't forget to add those columns to every new table that you create.MCTS, MCITP - Please mark posts as answered where appropriate.

To avoid too many costly updates, you can use a checksum to determine if a row is actually updated, or if it is just the same row. Actually rowversion(timestamp) column in the table is the sure way of change tracking. checksum is a good approximation. Related article: http://www.sqlusa.com/articles2005/rowversion/ Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM