Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Logic for traversing thru different columns in one row Expand / Collapse
Author
Message
Posted Wednesday, October 29, 2008 9:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:45 AM
Points: 336, Visits: 867
Hi all,


I have a requirement to loop through a row for different columns, Can anyone give me some inputs for how can I do this in SSIS.

The question is like this:

I am tracking changes to certain columns, now I want to keep record in a fact table ,for changed columns in one row ...

for ex:

If I have these columns from my source:-

empno ename address city state phone


now the columns which are critical and i need to maintain changes are only address, city , state
so for any change in address create a record in fact with changes done ..
but the question here is suppose if 2 critical attributes changes at the same time and come to me together...now it will create record for first one that it looked up and will not lookup again to the same row for other changed column...but i want to loop and lookup again for other critical attributes that may have changed...

Actually, I am not getting any logic for this Can any one provide me sum help on this?



Thanks in Advance
Ruchika



---------------------------------------------------

Thanks
Post #593727
Posted Thursday, October 30, 2008 8:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:45 AM
Points: 336, Visits: 867
any help plzzzzzz


---------------------------------------------------

Thanks
Post #594362
Posted Thursday, October 30, 2008 8:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:03 PM
Points: 12,963, Visits: 32,512
you could easily do this automatically in a trigger, especially by using the new COLUMNS_UPDATED() function that exists for triggers in SQL2005; if a change occurs, you could have a trigger automatically log the changes to an audit table...
Are you sure you need to do this in SSIS, or just 'get er done'?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #594371
Posted Friday, October 31, 2008 8:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:45 AM
Points: 336, Visits: 867
Thanks......I don't know how trigger will help me to track every column...

will it insert a new row for every column changed in a row?

I mena if address and location both changes, will it give me two rows in the transaction table?

Thanks



---------------------------------------------------

Thanks
Post #595054
Posted Friday, October 31, 2008 10:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:03 PM
Points: 12,963, Visits: 32,512
ok here's an example for you to play with to understand the concept:
create the two tables, and select from the two tables after every step;
this example says if columns 3,4,5,6,7 or 8 are modified, logit to an audit table; changing column 1,2 or 9 does not cause an audit.

updating a value to the same value still triggers an audit..so if you set ADDR1 = to the same value it had previously, it still fires the trigger.

CREATE TABLE MYADDRESSES(
EMPNO INT IDENTITY(1,1) PRIMARY KEY,
ENAME     VARCHAR(100),
ADDR1     VARCHAR(100),
ADDR2     VARCHAR(100),
CITY      VARCHAR(100),
STATECODE VARCHAR(2),
ZIPCODE   VARCHAR(100),
PHONE     VARCHAR(20),
MOREDATA  VARCHAR(100)) --column 9 will not track changes

CREATE TABLE ADDRESSCHANGES(
EMPNO            INT ,
COLCHANGEDBITMASK   INT,       --example: saved so you can audit which cols actually changed, but they are both here anyway!
OLDENAME            VARCHAR(100),
OLDADDR1            VARCHAR(100),
OLDADDR2            VARCHAR(100),
OLDCITY             VARCHAR(100),
OLDSTATECODE        VARCHAR(2),
OLDZIPCODE          VARCHAR(100),
OLDPHONE            VARCHAR(20),
NEWENAME            VARCHAR(100),
NEWADDR1            VARCHAR(100),
NEWADDR2            VARCHAR(100),
NEWCITY             VARCHAR(100),
NEWSTATECODE        VARCHAR(2),
NEWZIPCODE          VARCHAR(100),
NEWPHONE            VARCHAR(20))
GO
--modified from http://www.sqlservercentral.com/Forums/Topic593727-148-1.aspx#bm595054
CREATE TRIGGER TR_MYADDRESSES
ON MYADDRESSES
AFTER
UPDATE AS
/*Check whether columns 3 thru 8 have been updated. If any or all
columns 2, 3 or 4 have been changed, create an audit record. The
bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To test
whether all columns 2, 3, and 4 are updated, use = 14 instead of > 0
(below).
this one specifically is for 32 thru 8 is:
select  power(2,(3-1))
     + power(2,(4-1))
     + power(2,(5-1))
     + power(2,(6-1))
     + power(2,(7-1))
     + power(2,(8-1)) = 252*/
--IF (COLUMNS_UPDATED()) <> 0
IF (SUBSTRING(COLUMNS_UPDATED(),1,1) &amp; 252 )>0
BEGIN

--inside a trigger, two special tables exist for the duration of the trigger:
--the table INSERTED and the table DELETED
--an UPDATE would have data in both tables...the value WAS DELETED to be replaced with the value from INSERTED

  
INSERT INTO ADDRESSCHANGES(EMPNO, COLCHANGEDBITMASK, OLDENAME, OLDADDR1, OLDADDR2,
OLDCITY, OLDSTATECODE, OLDZIPCODE, OLDPHONE,
NEWENAME, NEWADDR1, NEWADDR2,
NEWCITY, NEWSTATECODE, NEWZIPCODE, NEWPHONE)
  
SELECT
    
DELETED.EMPNO,
    
COLUMNS_UPDATED(),
    
DELETED.ENAME,
    
DELETED.ADDR1,
    
DELETED.ADDR2,
    
DELETED.CITY,
    
DELETED.STATECODE,
    
DELETED.ZIPCODE,
    
DELETED.PHONE,
    
INSERTED.ENAME,
    
INSERTED.ADDR1,
    
INSERTED.ADDR2,
    
INSERTED.CITY,
    
INSERTED.STATECODE,
    
INSERTED.ZIPCODE,
    
INSERTED.PHONE      
  
FROM DELETED
    
INNER JOIN INSERTED
      
ON DELETED.EMPNO = DELETED.EMPNO
  
END;
GO
--insert some test data
INSERT INTO MYADDRESSES( ENAME, ADDR1, ADDR2, CITY, STATECODE, ZIPCODE, PHONE, MOREDATA)
SELECT 'Kalvin','123 My Imagination St','','Miami','FL','33024','555-1212','likes snowmen'
UNION
SELECT
'Hobbes','123 My Imagination St','','Miami','FL','33024','555-1222','likes to tease calvin'

--renaming Calvin doesn't raise the trigger
UPDATE MYADDRESSES SET ENAME='Calvin' WHERE ENAME='Kalvin'
--changing the Address DOES:
UPDATE MYADDRESSES SET ADDR1='123 G.R.O.S.S. Clubhouse' WHERE ENAME='Calvin'
--changing multiple rows at the same time does trigger
UPDATE MYADDRESSES SET STATECODE='NY'
--setting the value to the same value  repeating a previous insert:
UPDATE MYADDRESSES SET ADDR1='123 G.R.O.S.S. Clubhouse' WHERE ENAME='Calvin'

SELECT * FROM MYADDRESSES
SELECT * FROM ADDRESSCHANGES





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #595106
Posted Friday, October 31, 2008 10:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:45 AM
Points: 336, Visits: 867
Thanks man for the prompt response...let me try dis...


---------------------------------------------------

Thanks
Post #595109
Posted Friday, October 31, 2008 2:16 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:45 AM
Points: 336, Visits: 867
Triggers been expensive...Can anyone tell me any other way to do this..I got from some forum that we can use CHECKSUM function or CHECKSUM transformation for this...

Have anyone got any idea abt how to use the checksum TSQL or transformation?

I never used it so have no idea at all....
Thanks



---------------------------------------------------

Thanks
Post #595278
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse