thanks for this great and detailed article. I've just started to get my head around SSIS and since I have a quite similar task to deal with, I'll take the liberty to ask for some advice in this thread. I hope it's not too off-topic.
In my scenario a bunch of excel files could be placed into the "ExcelDrop"-folder at the "same" time. So I figured I need a for-each loop. However, I will have to split that one package into one queue and job package(s).
The queue will monitor the folder, if an excel file is placed into the folder I then need to compare the filename with the filename in a database table and look for the value of a "process"-column in the same table. Depending on the value of this process-column an according job-package should be triggered. So if the value of that column is "ABC", job-package "abc" should be executed. In total I will have six of these job-"trigger-dependencies". I'm thinking about switch/case statement in script-task but you'll probably have a better idea.
For the queue I was thinking about this structure:
2) For-Each Loop
3) Check file name
4) trigger child package based on file name
5) depending on the return code move the file to a success or error folder and (maybe) write some logs to a file or a database.
- do I need the FileWatcher inside the for-each loop?
I'm not clear how to set up the job-package in terms of processing.
- I believe I need a dataflow task (Excel source, DB destination) in here and not in the queue-package, right?
- how would I pass the variables?
I would really appreciate some help on how to structure the queue-/job package setup.
- Can all of this be done with the SSIS-toolbox components or do I need script tasks for this?
- Instead of having to run the package all the time could I also get it to work with the sql server alert, when I need to monitor a unc-server path? I believe I could set the server path to a shared drive and monitor that drive with its folder. If I can't, how do I set-up the package to run 24/7 without having to use Visual Studio?
- in this article you only process .xlsx files but I would also need it work for .xls and .xlsm files. Since this folder will only receive Excel-files could I just set it to the Filename-prefix and the extension like this * ?
that's it for now 🙂
I'd be happy to receive some tips/instructions.
Thanks for reading.