Copy excel extension files from various subdirectories with ssis

Question

I To head banging on finding the solution to copy excel extension files from multiple subdirectories with ssis.

I tried to get the foreach loop container to do it but I couldn’t, then they passed me a C# code to insert into a Scriptask

string filtro = "*.xslx";
        string diretorioDestino = @"C:\Arquivos\Destino";

        List<string> origens = new List<string>();
        origens.Add(@"D:\ArquivosDepto1\");
        origens.Add(@"F:\ArquivosDepto3\");


        foreach (var origem in origens)
        {
            foreach (string arquivo in Directory.GetFiles(origem, filtro))
            {
                File.Copy(arquivo, Path.Combine(diretorioDestino, Path.GetFileName(arquivo)));
            }
        }

Answer 1

You don’t need a script, you can just use the properties of the Container Foreach Loop, which lets you search all the files in a directory or sub-directory and extension you want. Maps the result (the filenames) of this Loop to a SSIS variable and inside the container uses the Task "File System Task" which allows you to copy/move/delete/etc the files in each loop. If you don’t understand what I have said, I can explain it better if you like. My Inkedin is the same as my name.

Answer 2

Ednilton, the Directory.GetFiles returns the filenames (including paths) in the specified directory. To list all the files, including those of subdirectories, you need to pass more the SearchOption.

Simply change your own foreach

foreach (string arquivo in Directory.GetFiles(origem, filtro, SearchOption.AllDirectories))

Eventually the Directory.GetFiles may not be allowed to access a particular directory, thus launching a UnauthorizedAccessException.

A solution would be first you get all the directories using the GetDirectories and then go through all the directories searching for the files using the GetFiles:

public static List<string> GetFiles(string path, string searchPattern, SearchOption searchOption)
{
    var diretorios = Directory.GetDirectories(path);

    List<string> arquivos = new List<string>();
    try
    {

        foreach (var diretorio in diretorios)
        {
            arquivos.AddRange(Directory.GetFiles(diretorio, searchPattern, searchOption).ToList());
        }
    }
    catch (UnauthorizedAccessException)
    {

    }

    return arquivos;
}

The method Main would look like this:

public static void Main()
{
    string filtro = "*.xlsx";
    string diretorioDestino = @"D:\temp";
    List<string> origens = new List<string>();
    origens.Add(@"C:\Users\pablotondolo\Documents");
    // 
    foreach (var origem in origens)
    {
        foreach (string arquivo in GetFiles(origem, filtro, SearchOption.AllDirectories))
        {
            File.Copy(arquivo, Path.Combine(diretorioDestino, Path.GetFileName(arquivo)));
        }
    }
}

NOTE: the extension of excel is xlsx and not xslx.