Hi We have the following scenario for FileWatcher Implementation. We have a folder in share drive lets say (\\ussby302\MABCD\).An UI application lets say a windows or asp.net application place .txt,.csv or .xls file in the share folder \\ussby302\MABCD\ at any point of time. We need to develop a SSIS Package which needs to process the .txt file which has been placed in the folder using a temp table in Sql Server before processing the file the ssis package will have to truncate the table and then it will start the processing. Similarlly we need to develop a SSIS Package which needs to process the .csv file which has been placed in the folder using a temp table in Sql Server before processing the file the ssis package will have to truncate the table and then it will start the processing. Similarlly we need to develop a SSIS Package which needs to process the .xls file which has been placed in the folder using a temp table in Sql Server before processing the file the ssis package will have to truncate the table and then it will start the processing. the problem here is while the package which processes the .txt file is processing using the temp table in that time if an .csv got placed by the ui appliation in the share folder then package which corresponds to .csv will start running so it may truncate the temp table while it been used by the .txt package. So we may end up in losing data.because both the package will try to use the temp table So we need to find out any sort of queue mechanism to resolve this.. Like if any package uses the temp table other package has to wait ...and it has to start working after that... how can we do that...ilikemicrosoft

Instead of trying to apply a pattern where you are waiting for one of the processes to complete, can you not create copies of this temp table i.e remove the contention for the temp table completely. Try not to overcomplicate the solution with too many points of failure.http://btsbee.wordpress.com/

create one more table , keep some flag..(boolean) In packages add a script something like.. before executing Dataflow task, Check the flag.. if any package is running already just put thread.sleep(1000) after this again check the flag..

Is it some constraint as to why are you using the same table for insertion cant you use 2 diff temporary tables. If there is a constraint check for existense of temp table if already present append data onto table rather then deleting, towards the end of process, put a constraint as to when both the jobs .csv and .txt have finsihed delete all the contents on the tableAbhinav http://bishtabhinav.wordpress.com/

1) why not adding an extra column to the temp table that indicates if it's a txt, csv or xls. And instead of truncating the whole table, just delete everything where the extra column is txt, csv or xls. 2) why not use different temp tables 3) and the most complicated: set a value in a database table indicating that you're processing and add a loop after all wmi events that waits untill that value in the database get the right value. But this makes is very complicated and if you still want to use it you should test it very good with multiple files at a time.Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

If you have developed 3 different packages and you want to run them sequentially, It should not be a problem if you configure those 3 packages as a single JOB in SQL server setting each package as a single step, i.e. 3 different steps. you are good to go already :). On the other note if you really want to implement locking then you should implement semaphores here: What you have to do is, you should not watch for the CSV or XLS files rather create lock files and watch them instead. For example for your package P1, P2, P3 .... P1 should create 2 Lock files "csvlock.lck" and "xlslocl.lck" before it starts watching for txt file. P2 and P3 watch for these 2 lock files respectively and as long as these files are there P2 and P3 should not proceed. At the end of P1, remove lock file "csvlock.lck" which will tell P2 to start working. similarly at the end of P2 remove lock file "xlslocl.lck" and P3 will start processing. Using database or any other system for locks is added dependency which is not recommened. for details about semaphore you can start with: http://en.wikipedia.org/wiki/Semaphore_(programming) Hope this helps.