0

In SSIS In a folder there are many flat files and by using for each loop container we are processing it one by one. If any new file is placed in the folder and it is still in copying mode. Then, We should not take it for continue process. We should process Only fully copied file alone to our next process.

How can we achieve this? Please give your suggestions.

Sankar M
  • 71
  • 6
  • Use GetDate() in Execute SQL task to store the datetime at the start of the run. If any files being read have a create datetime greater than your stored value then do not process them. You could do this using either a conditional split in a data flow or a script element in the control flow – MiguelH Sep 15 '15 at 11:47
  • Thank you MiguelH. But How can we get the create datetime of a Text file. any idea ? – Sankar M Sep 15 '15 at 12:20
  • Here's a helpful article I've used before to get update date from flat files [link]http://microsoft-ssis.blogspot.co.uk/2011/03/get-file-properties-with-ssis.html – MiguelH Sep 15 '15 at 15:06
  • Possibly more connected with your problem is this [link]http://www.sqlservercentral.com/blogs/tim_mitchell/2010/8/23/ssis-conditional-file-processing-in-a-foreach-loop/ – MiguelH Sep 15 '15 at 15:21

1 Answers1

2

Best way I have done this in the past is to use a C# Script Task and try to open the file - If the file is still being copied you will get an error (which you Catch). Then you can set a boolean variable to conditionally process the file if the Open worked.

EG: Boolean b = true;

        FileStream f;

        try
        {
            f = new FileStream("C:\\Test\\Test.txt", FileMode.Open, FileAccess.ReadWrite, FileShare.None);
        }
        catch (IOException e)
        {
            if (e.Message == "hello")
            {
                b = false;
            }
        }
john McTighe
  • 1,181
  • 6
  • 8