sql server - How to execute SSIS package when a file is arrived at folder

Question

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

Answer 1

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

This is my infinite loop package:

Set 3 Variables:

IsFileExists - Boolean - 0

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

IsFileExists Boolean - 0

For the For Loop container:

Set the IsFileExists variables as above.

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;
    }
}
}

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.

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.