Geek Answers Handbook

SSIS - Folder for polling files and triggers for ETL packages

Need help, see below script

We have developed 1 ETL package that can process 5 files at a time (which usually takes 1 minute). We expect almost 100 files to appear in the PROCESSING folder in the morning (which means between 6:00 and 7:00).

I'm struggling to devise / develop some kind of polling mechanism that continues to search for files, and if they arrive, it will trigger an ETL package (running through SQL Server Agent).

How to do a survey and then run this ETL package - a service broker will help

I am new to SSIS, so I need some advice. You need to find a solution in SQL Server, rather than developing a .net application for polling folders, and then running jobs

Hi

0
sql-server-2008 ssis
source share
3 answers

You have 2 options:

  • Configure the folder watcher to call the package from the thank you string, for example, MyTrigger download it from CNET .
  • As you say, if you want the SQL agent to execute it, another option that is probably more efficient is to set up the file table and create a trigger in the insert table that should execute the agent job using the sp_start_job stored procedure.
0
source share

How about the SSIS File Watcher Task ?

0
source share

I am creating an agent to run a scheduled SSIS package. The first thing I do in SSIS is to determine if there are any files to process. I prefer having a small script task for this for various reasons. If the files are present, I set the variable so that my restriction after the script task can move on to the next task or if the files are not present, then complete the successful message of the package.

If you do not have any additional needs, such as logging, you can simply use for each file enumerator that will successfully complete the package if no files are found.

ps If you set a schedule to say one minute, and the package takes longer to run (import multiple files), then the following schedule will not work until the completion of the original process. Therefore, you do not need to worry that several processes interfere with each other.

0
source share

More articles:


All Articles