I need to write an SSIS package that responds to data modifications in the database (triggers are not allowed in this project). How can I find data that have been inserted, updated, or deleted in the SQL Server logs? I'm assuming this information would be in the transaction logs. Assuming that's correct, I'm looking for a way to read the transaction log and find the information I want in there while in an SSIS package. Thanks!

In SSIS 2012 CDC components will be available, until then you cannot, the log is not something you or whoever is in charge for SQL Server want it to touch. There is a possibility to enable CDC in SQL Server all editions from 2008 and up, but then you need to take a C# / VB .net route not SSIS. Arthur My Blog

Arthur, I'm not sure why you're saying CDC in 2005-2008R2 can't be used by SSIS. CDC sets up "change tables" (queries) that contain the changes from certain points in time. It's quite useful in SSIS to use that capability if you have it. So Peter - do stay away from trying to read the transaction logs. Yes, they do contain the information you're looking for - but it's in a non-public format that is extremely complex and subject to change at any time. CDC is a technology you should investigate... Talk to me now on

Arthur, I'm not sure why you're saying CDC in 2005-2008R2 can't be used by SSIS. CDC sets up "change tables" (queries) that contain the changes from certain points in time. It's quite useful in SSIS to use that capability if you have it. Talk to me now on I see, the 2005 version of SQL Server does not have CDC. It is in 2008. But there are 3rd party controls for CDC I can see by Attunity.Arthur My Blog

Just found a good example involving SQL Server 2008 CDC coupled with SSIS: http://msdn.microsoft.com/en-us/library/bb895315.aspxArthur My Blog

Thanks for these replies. Arthur, your link looks very promising. I'll give more feedback when I've had a chance to try it. Thanks again.

Just found a good example involving SQL Server 2008 CDC coupled with SSIS: http://msdn.microsoft.com/en-us/library/bb895315.aspxArthur My Blog