文件到达文件夹时如何执行SSIS包 [英] How to execute SSIS package when a file is arrived at folder

问题描述

要求是执行SSIS包，当文件到达文件夹时，我不想手动启动包.

The requirement is to execute SSIS package, when a file is arrived at a folder,i do not want to start the package manually .

不确定文件到达时间，文件也可以多次到达.当文件到达时，这必须加载到表中.我认为，一些解决方案，如文件观察器任务，仍然希望启动包

It is not sure about the file arrival timing ,also the files can arrive multiple times .When ever the files arrived this has to load into a table.I think, some solution like file watcher task ,still expect to start the package

推荐答案

我过去的做法是使用从 SQL Server Agent 调用的无限循环包；

The way I have done this in the past is with an infinite loop package called from SQL Server Agent, for example;

这是我的无限循环包:

设置 3 个变量:

IsFileExists - 布尔值 - 0

IsFileExists - Boolean - 0

FolderLocation - 字符串 - C:\放置文件的位置\

FolderLocation - String - C:\Where the file is to be put in\

IsFileExists 布尔值 - 0

IsFileExists Boolean - 0

对于 For 循环容器:

For the For Loop container:

如上设置 IsFileExists 变量.

使用 ReadOnlyVariable 作为 User::FolderLocation 设置 C# 脚本任务并具有以下内容:

Setup a C# script task with the ReadOnlyVariable as User::FolderLocation and have the following:

 public void Main()
    {
        int fileCount = 0;
        string[] FilesToProcess;
        while (fileCount == 0)
        {
            try
            {

                System.Threading.Thread.Sleep(10000);
                FilesToProcess = System.IO.Directory.GetFiles(Dts.Variables["FolderLocation"].Value.ToString(), "*.txt");
                fileCount = FilesToProcess.Length;

                if (fileCount != 0)
                {
                    for (int i = 0; i < fileCount; i++)
                    {
                        try
                        {

                            System.IO.FileStream fs = new System.IO.FileStream(FilesToProcess[i], System.IO.FileMode.Open);
                            fs.Close();

                        }
                        catch (System.IO.IOException ex)
                        {
                            fileCount = 0;
                            continue;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // TODO: Add your code here
        Dts.TaskResult = (int)ScriptResults.Success;
    }
}
}

这样做本质上是关注 .txt 文件的文件夹位置，如果文件不存在，它将休眠 10 秒(如果需要，您可以增加此值).如果文件确实存在，它将完成，然后包将执行加载包.但是它会继续运行，所以下一次放入文件时它会再次执行加载包.

What this will do is essentially keep an eye on the folder location for a .txt file, if the file is not there it will sleep for 10 seconds (you can increase this if you want). If the file does exist it will complete and the package will then execute the load package. However it will continue to run, so the next time a file is dropped in it will execute the load package again.

确保将这个永久循环包作为 sql server 代理作业运行，以便它一直运行，我们有一个类似的包在运行，它从未引起任何问题.

Make sure to run this forever loop package as a sql server agent job so it will run all the time, we have a similar package running and it has never caused any problems.

此外，请确保您的输入包将文件从放置文件夹位置移动/存档.

Also, make sure your input package moves/archives the file away from the drop folder location.

这篇关于文件到达文件夹时如何执行SSIS包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！