Run SSIS only when complete file is present in the folder

Question

I have developed a ETL which is consuming flat files. The size of flat files varies from 250 MB - 300 MB. It is working absoultely fine when file present in the folder. But it fails when the file is in generation mode.

Ex: This ETL package runs from 8 AM to 10 AM to check whether the file is present in the folder or not. Now, at any instance(let say 9 AM) if the file is starting generated and till now it is 10 MB. ETL start processing the file and just hang and fail after 4-5 min ( hang at script task which is reading that the file is present in the folder or not).

What is the best way to trigger SSIS package only when the file generation is completely done?

Note: I have no control over the file generation.

You can use the 3rd party [File Properties Task](http://filepropertiestask.codeplex.com/) and test for `FileIsReadable ` — Ocaso Protal, Mar 20 '13 at 07:44
@OcasoProtal Thanks for the link but my client will not allow us to use any third party tool. Because they have other projects/jobs also running on prod and that will be a huge terror for them. We already tried to suggest him other third party tool but he just want us to develop the same functionality. :) — Zerotoinfinity, Mar 20 '13 at 07:49

score 1 · Accepted Answer · edited May 23 '17 at 11:50

Add a For Loop Container with a Boolean variable bFileAccessible:

The Init expression is @bFileAccessible=False
The Eval expression is @bFileAccessible==False

Inside the For Loop Container add a Script Task with a ReadWriteVariable User::bFileAccessible and the following C# script (showing only the Main() method):

    public void Main()
    {
        try
        {
            using (Stream stream = new FileStream("Path\to\your\file", FileMode.Open))
            {
                Dts.Variables["bFileAccessible"].Value = true;
            }
        }
        catch
        {
            Dts.Variables["bFileAccessible"].Value = false;
        }

        Dts.TaskResult = (int)ScriptResults.Success;
    }

You should also use a variable for the filename and maybe a little wait interval. For more information about the script see here.

score 1 · Answer 2 · edited Mar 20 '13 at 10:11

1

Check the FIle modified time everytime and comapre the same with previous one.... it's not good logic but a good idea if no perfect alternative

edited Mar 20 '13 at 10:11

Ocaso Protal

19,362
8
76
83

answered Mar 20 '13 at 10:07

Sam

392
1
6
18

1

This will not work when the generation process is stalled for some time. – Ocaso Protal Mar 20 '13 at 12:00

Run SSIS only when complete file is present in the folder

2 Answers2