locked
ssis c# script to check file exists RRS feed

  • Question

  • User-583959464 posted

     I have a ssis to add a c# program to check 3 excels files exists or not in SSIS component <Script Task Editor>, the script is shown in below. anyone tell me how to explain the script ? 
       
       
       
       for below script, what does it mean?
       Directory.GetFiles(Dts.Variables["User::file_path_input"].Value.ToString())
                                                    .Select(fPath => fPath.Split('\\').Last()).ToArray(); 

    public void Main()
      {
                try
                {
                    //try to match the name

                    String[] inputPathfileNames = Directory.GetFiles(Dts.Variables["User::file_path_input"].Value.ToString())
                                                    .Select(fPath => fPath.Split('\\').Last()).ToArray(); 
                    String[] checkResult = Dts.Variables["User::acceptFileList"].Value.ToString().Split(',');
                    List<String> listCommon = new List<String> { };  // find the current month input 
                    foreach (string aFile in checkResult)
                    {
                        var results = Array.FindAll(inputPathfileNames, file => file.Contains(aFile));
                        if (results != null && results.Length > 0)
                            listCommon.Add(results[0]);
                    }
                    if (listCommon.Count == checkResult.Length)
                    {
                        Dts.Variables["User::isBothABexists"].Value = true;
                        Dts.Variables["User::AB01_CURRENT_MONTH_FILE"].Value = listCommon[0];
                        Dts.Variables["User::AB02_CURRENT_MONTH_FILE"].Value = listCommon[1];
                        Dts.Variables["User::AB03_CURRENT_MONTH_FILE"].Value = listCommon[2];
                    }
                    else
                    {
                        Dts.Variables["User::isBothABexists"].Value = false;
                        Dts.Variables["User::fail_msg"].Value = "at least one of the required file missing, job aborted; available file list: " + string.Join( ",",listCommon);
                    }
                }

    Monday, May 3, 2021 9:40 AM

All replies

  • User753101303 posted

    Hi,

    This line takes all files found in  the directory name found in file_path_input, creates an array for each element separated by \ and keep the last one (ie the file name without any path information).

    It is then testing user provided names but it is case sensitive and it looks if the name is "part" of an actual file name. Likel;y a confusion between string.Contains (which check if a string contains another string) and Array.Contains (whiich check if an array element is found into another array).

    So in short IMO this piece of code can report that "MyFile.txt" doesn't exists because the file is "myfile.txt" and it can report that "file.txt" exists because it is part of "myfile.txt"

    My approach would be rather to use https://docs.microsoft.com/en-us/dotnet/api/system.io.path.combine?view=net-5.0 to combine the full parh and each use provided file name and  https://docs.microsoft.com/en-us/dotnet/api/system.io.file.exists?view=net-5.0 to test if each file exists.

    Edit;: basically it does exactly what you want ie to check if reach user provided file name does exists. By loading file names as string then checking another string against this array you can end up with ssomething close but not 100% correct. For example if doing an case insenstive comparison it would now fail for Linux as file names are then case sensitive.

    If usign System.IO.File.Exists it won't be case sensitive for Windows and will be case sensitive for Linux.

    Monday, May 3, 2021 12:57 PM