SSIS/SQL 自动化 [英] SSIS/SQL Automation

问题描述

我的公司是一家直邮公司，随着我们通过 SSIS、SQL 和 Excel 的自动化不断发展，我们正在寻找改进工作流程的方法.

My company is a direct mail company, and we are looking for ways to improve workflow as we continue to grow via automation with SSIS, SQL and Excel.

基本上，我们想要做的是能够让我们的流程在我们不在办公室时自动运行.IE.一个文件进来了，根据文件的放置位置，进程将开始，我们需要完成的任务将开始.

Basically, what we want to do is to be able for our processes to run automatically when we're not in the office. I.e. A file comes in, and, based on where the file is placed, the processes will begin and the tasks we need done will start.

我们知道我们想要它做什么，我们只是在纠结从哪里开始，或者更具体地说，我们的服务自动化的最佳方法是什么?

We know what we want it to do, we just are struggling on where to begin or, more specifically, what is the best way to go about automating our services like that?

推荐答案

您有两种通用方法:事件驱动过程或轮询过程.前者在事件发生时运行(文件位于文件夹中)，而后者定期运行(每 5 分钟检查一次文件是否存在).

You have two general approaches: an event driven process or a polling process. The former runs when an event happens (file lands in a folder) while the latter runs on a periodic basis (every 5 minutes it looks to see if a file exists).

根据我的经验，事件驱动模型在实践中听起来确实不错，但在实施中却很糟糕.我们使用了各种现成的软件和自产的文件观察者"任务来处理数据.不可避免地，某些事情会发生并且它没有注册事件，因此没有处理任何数据.解决方法通常很简单，重新启动该过程，然后将文件移出并再次移回.除非我们在受监管的环境中工作，在那里我们无权移动文件，因此需要不同的人来重置队列，而不是可以移动文件的人.

It is my experience that the event driven model sounds really good in practice but is horrible in implementation. We used a variety of off the shelf software and homegrown "file watcher" tasks to process data. Inevitably, something would happen and it wasn't registering the event so no data was being processed. The resolution was usually simple, restart the process and then move the files out and back again. Except we worked in a regulated environment where we didn't have access to move the files so that would take a different person to reset the queue versus who could move files about.

轮询过程要容易得多.如果有工作要做，它就会这样做.否则它会回到睡眠状态.Windows 任务计划程序可以很好地做到这一点.SQL Server 有自己的作业调度系统，称为 SQL Agent.它也可以以不同的时间间隔运行包.

A polling process is much easier. If there's work to be done, it does so. Otherwise it goes back to sleep. Windows Task Scheduler can do this just fine. SQL Server has it's own job scheduling system called SQL Agent. It too can run packages at various intervals.

让 SSIS 在无限循环中运行检查文件或使用 WMI 事件来尝试捕获正在放置的文件，但不要执行其中任何一个操作，这可能很诱人.SSIS 并非设计为一直运行.相反，使用经过验证的产品来处理它们的用途.

It might be tempting to have SSIS run in a infinite loop checking for files or using WMI events to try to catch a file being placed but don't do either of those. SSIS is not designed to be running all the time. Instead, use proven products to handle the stuff they were meant for.

这篇关于SSIS/SQL 自动化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！