none
Using GetFiles(directoryPath, [FileName]) -- missing upper case files RRS feed

  • Question

  • Hi All,

    I wonder if anyone can help me...I have an ssis package which iterates through all files in a location and deletes files older than 14 days...Issue is that the files can be called either .bak or .BAK, .Bak etc....and am wondering how to pass this through to 

    System.IO.Directory.GetFiles(directoryPath, "*.Bak*");

    Full code is attached

    Thanks for any help

    Best Regards


    Aidan

        int RetentionPeriod = Convert.ToInt32(Dts.Variables["User::NumberOfDays"].Value.ToString());
                string directoryPath = Dts.Variables["User::FolderToDeleteFrom"].Value.ToString();
                string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.bak*");
               


    • Edited by aidan1972 Monday, April 16, 2018 2:56 PM
    Monday, April 16, 2018 2:55 PM

Answers

  • Aidan,

    If I read your question 20 times, I'm not sure about it:

    1. it is a problem
    2. you think it can be a problem

    What is the case?

    As some others wrote, it cannot be a problem, but if it is the case for you. More persons are of course interested to know.

    https://msdn.microsoft.com/en-us/library/07wt70x2(v=vs.110).aspx


    Success
    Cor

    • Marked as answer by aidan1972 Wednesday, April 18, 2018 1:47 PM
    Tuesday, April 17, 2018 5:05 PM
  • Shouldn't be any problem. Because Windows file names are case-insensitive. So, the search pattern "*.bak" should return all the files in your path ending on bak, BAK, Bak etc. .

    wizend

    • Marked as answer by aidan1972 Wednesday, April 18, 2018 1:47 PM
    Monday, April 16, 2018 5:10 PM
  • Hi aidan1972,

    Thank you for posting here.

    Based on my test, I could get the all the .bak files include upper case files .Your code works well.

    It does not cause by the code your provided.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, April 17, 2018 6:31 AM
    Moderator
  • I am not sure about your specific issue, but I have had various issues with the file mask in GetFiles(). I generally apply the filter after getting the files. For example:

    System.IO.Directory.GetFiles("dir").Where(f=>f.EndsWith(".bak", StringComparison.InvariantCultureIgnoreCase);

    This is probably less efficient, but it works.

    Ethan


    Ethan Strauss

    • Marked as answer by aidan1972 Wednesday, April 18, 2018 1:47 PM
    Tuesday, April 17, 2018 3:33 PM
  • After that latest twist your question appears to be more SQL Server related than about the C# language!?

    Did you check whether the account that your SQLServerAgent uses to run that script has the required permissions to access the file system and delete those files? For instance, when the files are stored in a user-defined location, you'll need to grant the special access to that location. The default Access Control List of SQLServerAgent might not suffice. That could be the reason for the different results of your script depending on whether you run it on Visual Studio or on SQL Server.

    wizend
    • Marked as answer by aidan1972 Wednesday, April 18, 2018 1:47 PM
    Wednesday, April 18, 2018 9:50 AM

All replies

  • Shouldn't be any problem. Because Windows file names are case-insensitive. So, the search pattern "*.bak" should return all the files in your path ending on bak, BAK, Bak etc. .

    wizend

    • Marked as answer by aidan1972 Wednesday, April 18, 2018 1:47 PM
    Monday, April 16, 2018 5:10 PM
  • Hi aidan1972,

    Thank you for posting here.

    Based on my test, I could get the all the .bak files include upper case files .Your code works well.

    It does not cause by the code your provided.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, April 17, 2018 6:31 AM
    Moderator
  • I am not sure about your specific issue, but I have had various issues with the file mask in GetFiles(). I generally apply the filter after getting the files. For example:

    System.IO.Directory.GetFiles("dir").Where(f=>f.EndsWith(".bak", StringComparison.InvariantCultureIgnoreCase);

    This is probably less efficient, but it works.

    Ethan


    Ethan Strauss

    • Marked as answer by aidan1972 Wednesday, April 18, 2018 1:47 PM
    Tuesday, April 17, 2018 3:33 PM
  • Aidan,

    If I read your question 20 times, I'm not sure about it:

    1. it is a problem
    2. you think it can be a problem

    What is the case?

    As some others wrote, it cannot be a problem, but if it is the case for you. More persons are of course interested to know.

    https://msdn.microsoft.com/en-us/library/07wt70x2(v=vs.110).aspx


    Success
    Cor

    • Marked as answer by aidan1972 Wednesday, April 18, 2018 1:47 PM
    Tuesday, April 17, 2018 5:05 PM
  • Hi Cor/Ethan/Wendy/Wizend,

    Really appreciate your help on this..and turns out that the the upper case extension was a bit of a red herring..

    The situation (after further investigation is this)

    I have job created with script task on my local pc...and when I run it from Visual Studio it runs perfect. When I run it from sql management studio Sql Server Agent Jobs on my pc it runs correctly.

    On the server where I am deploying it...the package runs correctly from visual studio and deletes the relevant files. However when running the job from sql management studio Sql Server Agent Jobs  on the server...I get no error messages and the log says success..but no files are deleted.....I am running as the job as sa....I attach full copy of my script--->is there something I could add to log any errors or to try and find out what is happening.

    Again, thanks for all your help, its appreciated.

    (I know the part below (oldFilesToUpper is over kill and I will remove this from the script

    Cheers

    Aidan


    public void Main()

    		{
                int RetentionPeriod = Convert.ToInt32(Dts.Variables["NumberOfDays"].Value.ToString());
                string directoryPath = Dts.Variables["FolderToDeleteFromF"].Value.ToString();
                string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.bak*");
                string[] oldFilesToUpper = System.IO.Directory.GetFiles(directoryPath, "*.Bak*");
    
                foreach (string currFile in oldFiles)
                {
                    FileInfo currFileInfo = new FileInfo(currFile);
    
                    if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))
                    {
                        currFileInfo.Delete();
                    }
                }
    
                foreach (string currFile in oldFilesToUpper)
                {
                    FileInfo currFileInfo = new FileInfo(currFile);
    
                    if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))
                    {
                        currFileInfo.Delete();
                    }
                }
            }
    
            #region ScriptResults declaration
            /// <summary>
            /// This enum provides a convenient shorthand within the scope of this class for setting the
            /// result of the script.
            /// 
            /// This code was generated automatically.
            /// </summary>
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };

    Wednesday, April 18, 2018 9:13 AM
  • After that latest twist your question appears to be more SQL Server related than about the C# language!?

    Did you check whether the account that your SQLServerAgent uses to run that script has the required permissions to access the file system and delete those files? For instance, when the files are stored in a user-defined location, you'll need to grant the special access to that location. The default Access Control List of SQLServerAgent might not suffice. That could be the reason for the different results of your script depending on whether you run it on Visual Studio or on SQL Server.

    wizend
    • Marked as answer by aidan1972 Wednesday, April 18, 2018 1:47 PM
    Wednesday, April 18, 2018 9:50 AM