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

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

Solution 2

As others have already suggested, using either WMI task or an infinite loop are two options to achieve this, but IMO SSIS is resource intensive. If you let a package constantly run in the background, it could eat up a lot of memory, cpu and cause performance issues with other packages depending on how many other packages you've running. So other option you may want to consider is schedule an Agent job every 5 minutes or 10 minutes or something and call your package in the job. Configure the package to continue only when a file is there or quit otherwise.

Solution 3

You can create a Windows service that uses WMI to detect file arrival and launch packages. Details on how to are located here: http://msbimentalist.wordpress.com/2012/04/27/trigger-ssis-package-when-files-available-in-a-folder-part2/?relatedposts_exclude=330

Solution 4

What about the SSIS File Watcher Task?

View more solutions

Comments

[user1254579]

It seems i need some knowledge in .net to do that.Is there any way in ssis to chieve this?

[Peter_R]

@user1254579 You can run a forever loop package with a script task that looks for a file, then executes another package. Will your file always be called the same thing? i.e. customer.txt or will it be customer1.txt then customer2.txt

[TTeeple]

Initial googlings show you can use the FileWatcher task but memory leak warnings would cause me to stray from that. It looks like he shows exactly what you need to do.

[user1254579]

The thing is we have sql 2000 as target database(dwh).and an installation of ssis 2008 on the local machine.Is it possible to schedule the sql agent to run the package ?

[user1254579]

Thanks for the solution.The thing is we have sql 2000 as target database(dwh).and an installation of ssis 2008 on the local machine.Is it possible to schedule the sql agent to run the package ?

[Peter_R]

I imagine you could run the package with DTEXEC on the local machine, set it up with a Windows Task Schedule to run when the computer restarts, just watch the options to stop the task if it has run for a certain period of time. Though you will need Integration Services installed. Other than that I'm not quite sure.

[Samuel Vanga]

I don't know about SQL 2000 job running 2008 packages for sure.

[Nick.McDermaid]

YOU can run the pacakge anywhere the SSIS runtime is installed (i.e. on your SQL 2008 install). You need to clarify where the file is, and where you need to run the package.

[Alistair McMillan]

@Peter_R "Set 3 Variables"? But then the variables are called "IsFileExists", "FolderLocation" and "IsFileExists" again?