How to get notified if new Email gets added to existing Table in SSIS / SQL?

Question

How to get notified if new Email gets added to existing Table in SSIS / SQL?

MSBI Learner

Ten Centuries

Points: 1192
More actions
April 3, 2012 at 1:54 am

#259360

I have a scenario and that has to be achieved either in SQL or SSIS. Hope someone can help me out here:
Scenario: We get EmailNames from our Source Systems and adding them in a table at Staging. Now whenever any new email gets added to the existing emails, I should be notified.
Request you to please help me on how to achieve the same.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

MSBI Learner (4/3/2012)
Scenario: We get EmailNames from our Source Systems and adding them in a table at Staging. Now whenever any new email gets added to the existing emails, I should be notified.

What is adding data to the staging table? Whether that is a job, a process, a person or something else, can you modify it so it sends you an email when new data is loaded?

Or is that the issue, can you not modify that process and you want to be notified by some other independent means?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

MSBI Learner Ten Centuries Points: 1192 More actions · Answer 2

My apologies, I should've been more specific about my problem.

I have SQL Job which loads the data into Staging from Source Flat Files using an SSIS Package. I cannot modify the data at Staging as I would require the same data for Production.

The data loading for Staging table is Truncate and Load. I have an idea which is I would copy my entire table into another table called Archive and will compare the distinct Mails in current Staging and Archive table so that I would get newly added Mails and this process will be automated thru some Script or SSIS Package.

However, I am thinking of any other approach apart from this. Any thoughts please

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 3

Do you permissions to run DDL and DML comnands on the table. If yes, have you considered adding a trigger?

Also, is the load incremental or full?

Raunak J

MSBI Learner Ten Centuries Points: 1192 More actions · Answer 4

Yes, I do have full permissions. It's FULL Load that's why I didn't consider Triggers

p.govindaraj SSC Enthusiast Points: 193 More actions · Answer 5

If you are truncating and loading the tables then how will you find the new emails?, you have to have a archive table there you can load distinct values then you compare with the new values. once completed you can load new values again.;-)

MSBI Learner Ten Centuries Points: 1192 More actions · Answer 6

Well, If you can check the entire thread - that's what my initial plan is however I am just curious if anyone can give me a better idea than having Archive table and comparing the values.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

It sounds to me as if your proposed archive-table will, after the each set of staging data has been added to it, will be the superset of all emails that have ever been in production. That seems like the best way to determine if an email in the staging table is "new" to your system. A simple LEFT JOIN or NOT EXISTS from your staging table to your archive table should do fine.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

MSBI Learner Ten Centuries Points: 1192 More actions · Answer 8

Thanks much, that sounds like a best bet indeed 🙂