Hi, I need to create a SSIS package which should upload every file in the folder to the FTP server and move it to the archive folder. The files will be dropped randomly into the folder. I'm in dilemma to follow one of the following two options that best fit for my task . 1. Using a Foreach Loop task. I'm thinking to schedule a job to run every 15mins. 2. Using WMI Event Watcher Task. a. http://microsoft-ssis.blogspot.com/2010/12/continuously-watching-files-with-wmi.html b. http://microsoft-ssis.blogspot.com/2010/12/continuously-watching-files-with-wmi_25.html Which one is the best option for me? Also, if there is any other best way to do this task please let me know. Thanks for your help.

Why not load all the files to FTP server at one go? Please vote as helpful or mark as answer, if it helps Cheers, Raunak | t: @raunakjhawar | My Blog

Sorry, I forgot to mention that "The files will be dropped randomly into the folder".

Still, you can transfer all the files at the end of day. Is this possible?Please vote as helpful or mark as answer, if it helps Cheers, Raunak | t: @raunakjhawar | My Blog

Requirement is... the file should upload to FTP server ASAP it is available in the folder.

With #1 the caveat you may face is the timing interval. If SSIS is trying to grab a file that is being written to, then you will get an error. Thus, I suggest using the 2nd approach only.Arthur My Blog

With option 1...You mean if the earlier job will take more than 15 mins to complete then would that be a problem ? But with options 2a and 2b still I need to schedule a package to run continuously right ?

This and that if a file is still in transit (even though appearing in the file system, but the data stream pumping the data into it is till open, this besides, is especially of concern with very large files) the package will stop because it will "see" the file, but will not be able to get its data. As an aside, in software development, try to never impose any hardcoded limits to anything e.g. fixed time intervals. Thiis is because what if you have a handful of files today and myriad of them tomorrow? Or what if the file(s) size grows up suddenly?Arthur My Blog

I suggest you use the FileWatcher (free) task or better yet a commercial component, then just make the package running and the task will "sniff" the file arrivals. It is better because the task uses the Windows API that ensures the file write operation is complete. Arthur My Blog

Actually I don't need to push the data into database. Hence the file will never be in READ/WRITE status. All I'm doing is just uploading the file to FTP site.

As the files arrive they will be, each time a file gets created a data stream writes to it, the situation becomes even more intricate when SAN is involved, such a file appears complete to the OS whereas in fact it has not even reached the disk yet (remains in SAN's cache).Arthur My Blog

How to make the package running? Using the daily frequency in the Job Schedule Properties ? Should I set up to Occurs every 1 minute?

With the FileWatcher you just start itArthur My Blog

Hi, I need to create a SSIS package which should upload every file in the folder to the FTP server and move it to the archive folder. The files will be dropped randomly into the folder. I'm in dilemma to follow one of the following two options that best fit for my task . 1. Using a Foreach Loop task. I'm thinking to schedule a job to run every 15mins. 2. Using WMI Event Watcher Task. a. http://microsoft-ssis.blogspot.com/2010/12/continuously-watching-files-with-wmi.html b. http://microsoft-ssis.blogspot.com/2010/12/continuously-watching-files-with-wmi_25.html Which one is the best option for me? Also, if there is any other best way to do this task please let me know. Thanks for your help. You can combine both. The standard ssis file watcher or one of the custom file watcher tasks will wait untill the a file is created. After that it will continue the control flow. You can add a foreach loop file enumerator behind that to do the actual FTP Task. And if you put both of them in a loop then the process starts all over... waiting for the next task. Something like this, but a FTP task instead of the dataflow task. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

@ArthurZ....I have used the File Watcher Task as you said. Which is working fine when I connected it to the Ftp Task. However, I wanted to connect it to Foreach Loop Container. The File Watcher Task is allowing only String type variables for "Output Variable Name". How can I connect it to Foreach Loop.

Why do you need to the ForEach? With the FileWatcher you collect a file as it arrives.Arthur My Blog

I would like to implement in the way in this thread http://microsoft-ssis.blogspot.com/2010/12/continuously-watching-files-with-wmi_25.html I don't want the package to run all the time.

I think this defeats the purpose of the FileWatcher, if you interrupt it then you are going to miss files, correct?Arthur My Blog

Answer to your question " if you interrupt it then you are going to miss files, correct? " No I'll run my package from morning 6am to 12am daily. None of the files will be dropped into the folder in between 12am - 6am.

Try to create a windows service and for watching folder... I think it will work for you...