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

Best way to flag an already loaded record using SSIS Expand / Collapse
Author
Message
Posted Wednesday, June 26, 2013 3:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 8, 2013 3:25 PM
Points: 33, Visits: 177
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.

Post #1467892
Posted Wednesday, June 26, 2013 10:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 5,045, Visits: 11,794
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1467946
Posted Thursday, June 27, 2013 9:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 8, 2013 3:25 PM
Points: 33, Visits: 177
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.
Post #1468238
Posted Tuesday, July 2, 2013 8:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
sounds like a job for slowly changing dimensions
Post #1469540
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse