SSIS - Polling Files Folder and Trigger ETL Packag

问题:

Need advice, see below scenario

We have developed 1 ETL Package which can process 5 files at one time (which usually takes 1 mins). We expect that nearly 100+ files can come to the PROCESSING folder during morningtime (means lets say between 6 am and 7am).

I'm struggling to design/develop some sort of polling mechanism, which keeps looking for files and if they arrive it will trigger ETL package (trigger through SQL Server Agent job).

How to do polling and then triggering that ETL Package - is Service Broker will help

I'm new to SSIS so need advise. Need to find solution within SQL Server rather than developing .net app to poll for folders and then trigger jobs

Regards

回答1:

You have 2 options:

1. Set up a folder watcher that should call the package from commend line like MyTrigger download it from CNET.
2. As you say u want SQL Agent to execute it, the other option which is probably more efficient, is to set up a File Table and create a trigger on the table for insert, that should execute the agent job with the sp_start_job system stored procedure.

回答2:

What about the SSIS File Watcher Task?

回答3:

What I do is set up an agent to fire the SSIS package on a schedule. The first thing I do in SSIS is determine if there are files to process. I prefer to have a small script task to this for various reasons. If there are files present I set a variable so that my constraint following the script task can go to the next task or if no files are present then end the package reporting success.

If you do not have any ancillary needs like logging, then you can just use a for each file enumerator that will end the package successfully if no files are found.

ps If you set the schedule to say one minute and the package takes longer to run (importing multiple files) then the next schedule will not fire until the original process finishes. So you do not have to worry about multiple processes interfering with each other.