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: Thursday, November 20, 2014 10:49 AM
Points: 39, Visits: 192
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: Yesterday @ 2:52 PM
Points: 5,317, Visits: 12,353
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
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: Thursday, November 20, 2014 10:49 AM
Points: 39, Visits: 192
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
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 12:16 AM
Points: 461, Visits: 693
sounds like a job for slowly changing dimensions
Post #1469540
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse