Hi everyone, I am after ideas/suggestions!! Any suggestions greatly appreciated. We are considering creating SSIS packages to load data into a web facing database however the question remains: Do we rebuild this every evening? Do we somehow update the destination database each evening making the required changes The first point is of course simple to implement but does anyone know a best practice/efficient way of implementing the second idea? The problem of course is that the sources in SSIS might be SQL Server, CSV, Sybase etc but the destination will be a single SQL Server database. Phil

Do we somehow update the destination database each evening making the required changes The first point is of course simple to implement but does anyone know a best practice/efficient way of implementing the second idea? The problem of course is that the sources in SSIS might be SQL Server, CSV, Sybase etc but the destination will be a single SQL Server database. Phil Hello Phil, We know nothing about what you are trying to achieve, could you elaborate? No idea how the target database looks like either.Arthur My Blog

The destination database is a relational database in SQL Server. So for example there is a table containing employees. These will be transformed/unioned from several sources and populated in SQL Server each evening. Rather than truncating and repopulating the table every day we want to to insert new employees, update those that have changed, and delete those that are now missing. How would I go about doing that in SSIS?

Read the FAQ in this forum. This is an extremely common question. Talk to me now on

(I think this thread should belong to the SQL Server Data Warehousing forum.) Hi Phil, First, look for any possibilities in the sources to flag the record-level changes (new/modified/soft-deleted, and when). It would minimize the amount of data needed to copy, since you only have to compare the last load's date to the records' create/change date, in the best case (when no data had been changed), you won't have to copy anything at all. If this flagging not available, or hard-delete is used in the source databases, then I would recommend implementing a staging area on your destination SQL Server, where you can create and hold a copy of the last load's state of these. Then you can create SSIS packages for loading the current state, and comparing it to the last state. If the source is at least SQL Server 2008 Enterprise Edition, then you could use its Change Data Capture feature as well. Kind regards, Zoli -- Zoltán Horváth -- MCITP SQL Server Business Intelligence Developer 2005, 2008 -- Please mark posts as answered or helpful where appropriate.

SQL Server 2005 T-SQL offers a Merge operator, this is probably the best way. SSIS would do, too. Merge join or if the number of employees is not large (not in thousands) and your SLA allows plenty of time to process then it is possible to implement a Fuzzy Lookup Transformation as per this article: http://www.sqlteam.com/article/using-fuzzy-lookup-transformations-in-sql-server-integration-servicesArthur My Blog

Thanks Zoltan, We have used a similar staging approach in the past where we compare a hash value of the old and new fields to see if data in individual columns has changed. I was wondering if there was a nice easy way in SSIS that I was missing!

Read the FAQ in this forum. This is an extremely common question. Talk to me now on Hi, I did have a quick look but couldn't see anything in the FAQ?

(I think this thread should belong to the SQL Server Data Warehousing forum.) Hi Phil, First, look for any possibilities in the sources to flag the record-level changes (new/modified/soft-deleted, and when). It would minimize the amount of data needed to copy, since you only have to compare the last load's date to the records' create/change date, in the best case (when no data had been changed), you won't have to copy anything at all. If this flagging not available, or hard-delete is used in the source databases, then I would recommend implementing a staging area on your destination SQL Server, where you can create and hold a copy of the last load's state of these. Then you can create SSIS packages for loading the current state, and comparing it to the last state. If the source is at least SQL Server 2008 Enterprise Edition, then you could use its Change Data Capture feature as well. Kind regards, Zoli -- Zoltán Horváth -- MCITP SQL Server Business Intelligence Developer 2005, 2008 -- Please mark posts as answered or helpful where appropriate. It does become much more difficult when the sources are of different database types and when the package contains complex transformation bringing many data sets together. The original flags indicating a change in the source databases might be difficult to 'carry through' to the destination, especially if the database is being denormalised by the package.

Have a better look. It's there. I'd like to see a little more effort from you on this topic. No - it's not easy, especially if you're sourcing from different places. But it's definitely doable. There is no magic bullet for this, you have to generate the logic for yourself and build it yourself. Talk to me now on

Hi, Yes, it's more difficult. And it won't really change if the extraction of data from the source and the transformation of that (for example, denormalisation) is executed in a single step. I would recommend avoiding it. It's a single, but not a simple step. Changing of these packages would be extremely difficult as well later. The staging area should contain the data in its original structure. Please be aware: if you are applying transformations on the source data while you're still extracting it, you may lock resources on the source database unnecessarily. This lock's window can be minimized with a single read-and.write operation. Kind regards, Zoli-- Zoltn Horvth -- MCITP SQL Server Business Intelligence Developer 2005, 2008 -- Please mark posts as answered or helpful where appropriate.

Todd, what I meant was that I did skim the FAQ before posting, my apologies if I missed it. I was also checking that I had not missed something obvious regards how to approach this problem, sometimes there is a silver bullet but if you don't ask/check then you won't learn surely? Thanks everyone for confirming my original fears! Regards Phil