SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best way to flag an already loaded record using SSIS


Best way to flag an already loaded record using SSIS

Author
Message
RenzoSQL
RenzoSQL
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 241
Hi there!

Creating an alert dashboard that updates every 15 mins as a front end. As the database back end
I have a task of reading 8 different sources (different locations) but with the same exact type of data into a staging table every 15 mins and then scraping that staging table and only load the records that create 'alerts' (within my threshold values) into a Notification table.

My goal is to keep the tables very light so we don't have to go through so many records everytime. The staging table will get unloaded on a daily basis into a historical table. My plan is to flag those records that have been already scraped on the previous run (15 mins ago) with a certain value on a "Flag" field that I will create.

Next time (15 mins later) I have to load more records into this table from the source, I will only read those records that have flag = 0 and update notification table with those that have not been already added there.

what would be the best approach to accomplish that utilizing SSIS. Any ideas that could make this better and/or faster?

The staging and notification tables will have no more than 2-3 tables and each table no more than 10 fields.

Thanks!

R.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51679 Visits: 21169
Does that mean that you wish to read only those rows which have been inserted or updated in 'the last fifteen minutes' every time the job runs?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
RenzoSQL
RenzoSQL
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 241
Yes, I basically would like to just read those NEW records that have recently been inserted into the "staging" database from all different sources. From there I will have jobs scraping that staging database to look for those records that have values that generate alerts based on the thresholds.

The "alerts" database records will also get flagged as they are inserted, so the front end dashboard will only read new records that have not been flagged. Once read they will get flagged by either the front end or another SSIS job.

That is exactly what I am trying to figure out. I wanna know the best way to architect this for fastest performance and reliability.

Thanks!

R.
aaron.reese
aaron.reese
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2423 Visits: 907
sounds like a job for slowly changing dimensions
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search