Updating and Inserting to 2 different tables from the Same package

Question

I have 2 tables called Customer and ChangeLog. having the following structure

Customer table

ChangeLog Table

My Requirement is that

I need an SSIS Package that will read the record from another table with the same structure as CustomerTable and then compare the rows on both tables. If a change in any record is found it updates the records in the customer table as well as put an entry in the ChangeLog saying which column was updated.

So when a change is found in any of the columns I need to do the following

1. Update the Coresposing record in the Customer Table
2. Insert a new row into the ChangeLog

There won't be an Insert to the Customer Table. There will be only updates

Is there any single Task in SSIS that I can use to do both the update as well as an insert to these different tables ? or else what is the quickest and efficient way to achieve this in SSIS?

Any help is much appreciated

Answer 1

No there is no single SSIS task made to do this. I wouldn't use SSIS for this at all. Put the logic in either a stored procedure or trigger. If you have to use SSIS for some reason, then have SSIS call the stored procedure, or UPDATE the table and let the trigger fire.

Answer 2

This here is better than a SSIS packages since you can use a trigger to detect your row changes, and even the values.

Try my example you can just C/P into management studio. When you update on Sample_Table you will have changes rows and which column in your table.

1. So what you can do is. Keep your lookup logic in SSIS (if you want something in SSIS) - Updated the Table based on matches in lookup

2. When these updates happend your trigger will be fired and update the rows that have changed.

3. Alternative you can create your lookup in a t-sql script and do an ordinary update when custid=custid instead its just as easy. But thats up to you.

EDITED

     -- -------------------- Setup tables and some initial data --------------------
 CREATE TABLE dbo.Sample_Table (ContactID int, Forename varchar(100), Surname varchar(100), Extn varchar(16), Email varchar(100), Age int );
 INSERT INTO Sample_Table VALUES (1,'Bob','Smith','2295','bs@example.com',24);
 INSERT INTO Sample_Table VALUES (2,'Alice','Brown','2255','ab@example.com',32);
 INSERT INTO Sample_Table VALUES (3,'Reg','Jones','2280','rj@example.com',19);
 INSERT INTO Sample_Table VALUES (4,'Mary','Doe','2216','md@example.com',28);
 INSERT INTO Sample_Table VALUES (5,'Peter','Nash','2214','pn@example.com',25);
 
 CREATE TABLE dbo.Sample_Table_Changes (ContactID int, FieldName sysname, FieldValueWas sql_variant, FieldValueIs sql_variant, modified datetime default (GETDATE()));
 
 GO
 
 -- -------------------- Create trigger --------------------
 CREATE TRIGGER TriggerName ON dbo.Sample_Table FOR DELETE, INSERT, UPDATE AS
 BEGIN
     SET NOCOUNT ON;
     --Unpivot deleted
     WITH deleted_unpvt AS (
         SELECT ContactID, FieldName, FieldValue
         FROM 
            (SELECT ContactID
                 , cast(Forename as sql_variant) Forename
                 , cast(Surname as sql_variant) Surname
                 , cast(Extn as sql_variant) Extn
                 , cast(Email as sql_variant) Email
                 , cast(Age as sql_variant) Age
            FROM deleted) p
         UNPIVOT
            (FieldValue FOR FieldName IN 
               (Forename, Surname, Extn, Email, Age)
         ) AS deleted_unpvt
     ),
     --Unpivot inserted
     inserted_unpvt AS (
         SELECT ContactID, FieldName, FieldValue
         FROM 
            (SELECT ContactID
                 , cast(Forename as sql_variant) Forename
                 , cast(Surname as sql_variant) Surname
                 , cast(Extn as sql_variant) Extn
                 , cast(Email as sql_variant) Email
                 , cast(Age as sql_variant) Age
            FROM inserted) p
         UNPIVOT
            (FieldValue FOR FieldName IN 
               (Forename, Surname, Extn, Email, Age)
         ) AS inserted_unpvt
     )
 
     --Join them together and show what's changed
     INSERT INTO Sample_Table_Changes (ContactID, FieldName, FieldValueWas, FieldValueIs)
     SELECT Coalesce (D.ContactID, I.ContactID) ContactID
         , Coalesce (D.FieldName, I.FieldName) FieldName
         , D.FieldValue as FieldValueWas
         , I.FieldValue AS FieldValueIs 
     FROM 
         deleted_unpvt d
 
             FULL OUTER JOIN 
         inserted_unpvt i
             on      D.ContactID = I.ContactID 
                 AND D.FieldName = I.FieldName
     WHERE
          D.FieldValue <> I.FieldValue --Changes
         OR (D.FieldValue IS NOT NULL AND I.FieldValue IS NULL) -- Deletions
         OR (D.FieldValue IS NULL AND I.FieldValue IS NOT NULL) -- Insertions
 END
 GO
 -- -------------------- Try some changes --------------------
 UPDATE Sample_Table SET age = age+1;
 /*UPDATE Sample_Table SET Extn = '5'+Extn where Extn Like '221_';
 
 DELETE FROM Sample_Table WHERE ContactID = 3;
 
 INSERT INTO Sample_Table VALUES (6,'Stephen','Turner','2299','st@example.com',25);
 
 UPDATE Sample_Table SET ContactID = 7 where ContactID = 4; --this will be shown as a delete and an insert
 -- -------------------- See the results --------------------
 SELECT *, SQL_VARIANT_PROPERTY(FieldValueWas, 'BaseType') FieldBaseType, SQL_VARIANT_PROPERTY(FieldValueWas, 'MaxLength') FieldMaxLength from Sample_Table_Changes;
 
 -- -------------------- Cleanup --------------------
 DROP TABLE dbo.Sample_Table; DROP TABLE dbo.Sample_Table_Changes;*/
 
 select * from dbo.sample_table_changes