February 8, 2011 at 1:24 pm
I have a machine that runs a part then the outputs the results to a file on a server, I will need to setup a job to go out and look every minute to see if the file has been updated, what would be the best route to go? Do I create a table to hold the date of the file when last modified?
Also the results append the file, do I upload the whole file to a temp table to find what has been added then append the new data to the perm table and delete contents of the temp table?
Any direction would be great.
Thanks.
February 9, 2011 at 10:24 am
smac (2/8/2011)
I have a machine that runs a part then the outputs the results to a file on a server, I will need to setup a job to go out and look every minute to see if the file has been updated, what would be the best route to go? Do I create a table to hold the date of the file when last modified?
Wow, this is... ugly at best. I'm assuming the results cannot be redirected to a table or a messaging service to make life easier. I would personally create a job with a waitfor loop, store the 'lastupdatedate' in a variable, and then check that against the file's information in the filesystem. On change, run the data loader.
Also the results append the file, do I upload the whole file to a temp table to find what has been added then append the new data to the perm table and delete contents of the temp table?
Without some severe row level control in SSIS using conditional splitting... yes. Without knowing your data I don't know the feasibility of doing that at the SSIS level, but if there's some kind of timestamp you should be able to only process the records that occurred after the last filechange stamp on the file itself.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 10, 2011 at 11:48 am
I know this is SQL Server Central but another solution would be a script of some sort that runs on the target machine that monitors the file to see if it changes. I know there is monitor process in .Net that will do that. Then if the file is found to be changed it can call a stored procedure to do the import. If there is a time stamp field in the records you could record, in a separate table, the last time stamp and then pass that as a parameter to the sproc so it only loads the records with a time stamp after the last recorded one.
The process would look like this
monitor detects file changed
monitor gets last update time from separate table (could even get this from the target table)
monitor calls a sproc and passes it the last update time
the stored procedure updates the target with all the new records and then updates the last update time in the other table.
Since the monitor will use less processing cycles, this should be less resource intensive than running a job on the SQL server every minute.
hope that helps
February 10, 2011 at 12:03 pm
Sometimes dates are a bit messy for checking which records need to be updated somewhere else. The 3 ms. gap in the DATETIME data type can mess you up. Also, you would have to index it in order to get any decent kind of performance in determining which records were updated. You may not want to index the last update time if this is the only scenario that you need it for.
You could have as part of the update procedure on the main table, dumping the Primary Key or some other unique pointer (indexed, of course) of that record into another table that has an identity column in it - call it something like UpdID. Identities are guaranteed to be ever increasing. This could also be done in an UPDATE/INSERT trigger. You create another table with a single row that has the identity value of the last one you processed - call it LastUpdID. The SQL Agent job maintains this.
The SQL Agent job fires up and reads this single row table for the LastUpdID. It then gets the MAX(UpdID) from the dump table. You now know that everything between LastUpdID + 1 and MAX(UpdID) are the changed records in the main table. Get the DISTINCT PK or other unique values from the dump table and that's what you send. Then update the single row table with the MAX UpdID.
This avoids any problems with concurrency.
I've used both DATETIME based and IDENTITY based synchronization techniques, but I find that the IDENTITY method is more rock solid.
Todd Fifield
February 10, 2011 at 2:19 pm
You can still use SSIS i believe. There is a file system watcher task that works like the .net code but you don't need to be coder to use. Link to get it is http://www.sqlis.com/post/file-watcher-task.aspx.
Not knowing exactly the type of data in the file I would suggest importing it into a temporary table. You can then do something like
Select column1, column2, column3
from Temptable
except
Select column1, column2, column3
from destination table
This will import in only new reocords and is how i do ETL work for my DataWharehouse. Make sure you clear out your temporary table if you leave it as a permament work table or drop your #table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply