May 12, 2008 at 8:54 am
Hi there...brand new to SSIS and SQL Server 2005, so forgive me if this is a very basic thing!
I have an existing temp/staging table (Oracle) that receives an update of data from the previous day - this usually occurs by 10AM or so the following day. So for example, the data from yesterday, 5/11/08 will get populated into the table by 10AM today 5/12/08. Then tomorrow on 5/13, the 5/12 data will be posted and overwrite the 5/11 data. This data is also populated to a different table that contains about 18-months of historical daily data.
What I'm needing to do is to look at the date of the data (the tables both have a datetime field populated) and then if it is equal to the previous day, continue with the query and append the selected data to my own historical table. If the date is not equal to the previous day, I want to stop the query/package.
Hope that makes sense, any help is very appreciated. Thanks!
May 12, 2008 at 9:01 am
Wouldn't it make sense to exclude data in your source query instead of loading it and then checking it in ssis? Select x from tble where date = today-1.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 12, 2008 at 9:22 am
Yes it would, but I'm also wanting to add some kind of notification that the data has not been updated in the source table, and have the query try again every hour or so until the data verifies as being updated.
In an ideal solution, I'd like to check the max date of my destination table, compare it to the max date of the source table (the one that contains the 18-months of historical data), and pull all records back that are greater than my destination table (then append them to it of course).
This method would allow me to capture all the records that have been added to the 18-mo historical table in the event that there is a system issue that ends up populating more than 1 day of data (i.e., data issue results in delay of posting and therefore 3 days get posted on Monday instead of just Sunday data). Using this logic, I would ignore the 'temp/previous day' table and focus only on the 18-month historical table and my destination table.
May 12, 2008 at 9:59 am
You could have a "Check for data step" that sends an email when there are no records returned. Or, I don't know how Oracle would work, but in SQL Server I could write a stored procedure that calls sp_send_dbmail when there are no records. You could have the date and send to address as parameters.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply