September 28, 2009 at 9:10 am
Here's the scenario:
I'm pulling records off one table in a remote DB call it RemoteSourceTB and populating a Staging TB in our DB call it StageTB, from which I run a Lookup transform to catch new/updated records from TargetTB (our target). All this works fine..
Now as per my boss' ever growing requirements, he wants me to tell him what changed when I do an update. As in, for example, if we had to update the TB because Received_Qty was different, he wants me to record that Recieved_Qty had changed and somehow inform him via email that it did. Now there are a good 20 fields in this TB, and I can't figure out how I'm going to be able to do an update and make note of why that particular record was updated (what changed).
I need a beer... or 10...
September 28, 2009 at 9:29 am
This starts to fall under the auditing area. You might search for a few items there. You can then produce a report of what changed via the audit logs.
If this is in an ETL, is the flow not documented? Can you not just record what the flow is and send that as a note? Or add some logging that records which items execute? Add steps in your package that either logs this or inserts data in a table that helps you determine what changed.
September 28, 2009 at 12:01 pm
If you have the before and after data, there's a trick you can do with it that will give you the fields that changed.
create table #Before (
ID int identity primary key,
Col1 int,
Col2 int);
create table #After (
ID int primary key,
Col1 int,
Col2 int);
insert into #Before (Col1, Col2)
select 1, 1 union all
select 3, 5 union all
select 2, 4;
insert into #After (ID, Col1, Col2)
select ID, Col1, Col2
from #Before;
update #After
set Col2 = 6
where ID = 2;
select B.ID,
nullif(B.Col1, A.Col1) as Col1Change,
nullif(B.Col2, A.Col2) as Col2Change
from #Before B
inner join #After A
on B.ID = A.ID
where nullif(B.Col1, A.Col1) is not null
or nullif(B.Col2, A.Col2) is not null;
You can even take it one step further if you modify the final query by adding "for XML raw" at the end. Because For XML doesn't keep null columns (by default, you can override that), you end up with just the rows that had a modification and just the columns that were actually changed. Some clever string manipulation, and you can turn the XML into the body of the e-mail summary.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply