SSIS - 轮询文件文件夹和触发 ETL 包 [英] SSIS - Polling Files Folder and Trigger ETL Packages

问题描述

需要建议，见下图

我们开发了 1 个 ETL 包，一次可以处理 5 个文件(通常需要 1 分钟).我们预计在早上(也就是说在早上 6 点到早上 7 点之间)可以有近 100 多个文件进入 PROCESSING 文件夹.

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).

我正在努力设计/开发某种轮询机制，该机制不断寻找文件，如果文件到达，它将触发 ETL 包(通过 SQL Server 代理作业触发).

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).

如何进行轮询然后触发该 ETL 包 - 服务代理是否会有所帮助

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

我是 SSIS 的新手，所以需要建议.需要在 SQL Server 中找到解决方案，而不是开发 .net 应用程序来轮询文件夹然后触发作业

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

问候

推荐答案

您有 2 个选择:

1. 设置一个文件夹观察器，它应该从像 MyTrigger 这样的推荐行调用包 download它来自 CNET.
2. 正如您所说，您希望 SQL Agent 执行它，另一个可能更有效的选项是设置一个文件表并在表上创建一个触发器以进行插入，这应该使用 sp_start_job 系统存储过程.

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.

这篇关于SSIS - 轮询文件文件夹和触发 ETL 包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！