How to find updated records in transaction log?
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!
March 6th, 2012 2:23pm
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
Free Windows Admin Tool Kit Click here and download it now
March 6th, 2012 2:31pm
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
March 6th, 2012 2:46pm
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
Free Windows Admin Tool Kit Click here and download it now
March 6th, 2012 2:56pm
Just found a good example involving SQL Server 2008 CDC coupled with SSIS: http://msdn.microsoft.com/en-us/library/bb895315.aspxArthur My Blog
March 6th, 2012 2:57pm
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.
Free Windows Admin Tool Kit Click here and download it now
March 6th, 2012 4:19pm
Just found a good example involving SQL Server 2008 CDC coupled with SSIS: http://msdn.microsoft.com/en-us/library/bb895315.aspxArthur My Blog
March 6th, 2012 10:54pm