Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I have a list of 10 or so text files that I need to make sure exist in a specific folder at a specific time in SSIS. If these files exist I will kick off a package if they do not I will email the client. I have no problem checking for a single file but I am having trouble looping through all the files to find the specific ones I am needing.

An example would be I need file A.txt b.txt e.txt and z.txt and in the directory you have a.txt through y.txt but no z.txt. I hope this makes sense. If anyone could help me I would greatly appreciate it! SQL 2008 R2 BTW and VS 2008.

There are plenty of ways to solve this, but if you're interested in C#, I'd look at something like Enumerable.Intersect method.

Intersection , as we all recall from our Set Theory, is the group that exists in both A and B. In your case, your first set will be the "expected" set (a.txt, b.txt, e.txt and z.txt). Define that list however you need to. You can pull it from a table, hard code the list, whatever.

var expected = new List<string>() {
    @"C:\ssisdata\so\input\so_36362799_a.txt", 
    @"C:\ssisdata\so\input\so_36362799_b.txt" };
The second set will be the "actual". Since we're in .NET land, I'd look at the System.IO.Directory.GetFiles method to pull all the .txt files from our folder.
var actual = System.IO.Directory.GetFiles(@"C:\ssisdata\so\input", "so*.txt");
At this point we have two sets, physically implemented as List<string> but anything that implements IEnumerable should suffice. If I generate the intersection of the the two lists, I'll get a set back. If you need to know what file(s) aren't in the intersection, then you should capture the resulting set. We don't care though, all we want to know is "do we have all the files we need"
expected.Intersect(actual)
Since we don't care about what file(s) might be missing, we can simply count the number of elements in our expected and compare that to the intersection. If the count is the same, then we should have all the files we need. 
expected.Count() == expected.Intersect(actual).Count()
Since this is Windows, we do but don't need to worry about case sensitivity. In a Unix world, a directory can have foo.txt, Foo.txt, foo.TXT, etc as long as the case sensitive file name is unique. Windows doesn't have that concern. But, the string comparison that will take place to determine the Intersection will be case sensitive so if it's possible a person and not a program will be generating these files, you might want to force the file names to upper/lower case to be consistent with your expected set.
Putting it all together, you'd get a script task like this. Plenty of opportunities for improvement. We can pass in the source folder, our list of files, the file mask, and then what should happen if we don't find all the files we expected to find - right now it fails out the Script Task. 
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Runtime;
namespace ST_6fabd2d80a6340399c540f8fdeaebf97
    /// <summary>
    /// StackOverflow 36362799
    /// </summary>
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        /// <summary>
        /// Determine if all the files in our expected set match the actual set
        /// </summary>
        public void Main()
            var expected = new List<string>() { @"C:\ssisdata\so\input\so_36362799_a.txt", @"C:\ssisdata\so\input\so_36362799_b.txt" };
            var actual = System.IO.Directory.GetFiles(@"C:\ssisdata\so\input", "so*.txt");
            bool pokemon = expected.Count() == expected.Intersect(actual).Count();
            if (pokemon)
                Dts.TaskResult = (int)ScriptResults.Success;
                Dts.TaskResult = (int)ScriptResults.Failure;
        enum ScriptResults
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
Since this was tagged with SQL Server 2008, the default Script Task will target a .NET framework of 2.0. The above code won't work as the intersect call will require the System.Linq namespace. It's a simple remedy, within the script task, right click on the project (ST_#########) and go to the properties menu.
                Very nice.  Like a database join, but in C# and without the overhead of creating tiny "temporary" tables in a database.
– SebTHU
                Apr 4, 2016 at 15:56
Thinking in database terms, this would be a LEFT join (show unmatched) from a table of files that should be there, to a table of files that actually do exist.  Trouble is this is not a database context.
You could turn it into one by storing the names of your 10 files in a table, then importing the names of all files that exist into a "temporary" table.  That would make the comparison easy.
Iterating through all the files in the folder is never going to make an elegant solution, because you have to check 10 files, on an AND basis (they must all exist, or you do something else).  You'd have to have 10 flags in your C# code, set each when its corresponding file turns up in the loop, and check that they're all TRUE at the end.  This is not fun.
May be better to iterate through just your 10 file names (which means you'd have to get them into some kind of loopable structure, like a table).  Then the C# code can do a System.IO.File.Exists on each, and if it fails to find any file, reset an AllFilesAreThere flag that you set at the start.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.