Hi I have been tasked with researching the capabilities of SSIS. We have a fully normalized SQL Server database that we need to denormalize to provide integration into 3rd party reporting software. I understand that creating an SSIS package can achieve this however there is one small issue nagging at the back of my mind. If we have a table in the normalized database that continually has new records added to it, can the SSIS package automatically detect which rows it hasn't yet "picked up"/fetched on the next run or does this sort of functionality need to be implemented by hand ie: a data change flag that gets checked, an ex table or something of that sort. If this is functionality that needs to be implemented by hand, what is the best practice or suggested method of doing such a thing? the said table will have many records with new records being added daily Kind Regards Ross Steytler

You might consider using the slowly changing dimension component to address your requirement (Refer this for more information). But much has been said about the performance of this component for large amounts of data and a few alternatives are out there which are considered to be faster than SCD. Check this which gives an overview of these alternatives.http://btsbee.wordpress.com/

A good alternative to the suggestions of btsbee is CDC (change data capture). It allows you to track all the changes in the source table. If you're using SSIS 2012, there are special adaptors for CDC which make SSIS package development really easy. More info: http://msdn.microsoft.com/en-us/library/bb522489(v=sql.105).aspxMCTS, MCITP - Please mark posts as answered where appropriate.

Thanks btsbee and Koen for your feedback. The slowly changing dimension component is useful for the dimension tables as they won't be storing many records - I have this up and running thanks. As for the fact table (sales transaction source), am I right in assuming a custom method or CDC (Koen's suggestion) has to be used? Just wanted to know if there was some magic "do-it-all" solution out there that I was unaware of :) Thanks again