Search code examples
sql-serverssisetlsql-server-data-tools

SSIS - How to copy csv file saved in different folders to a single folder

I have a package which save multiple(14) csv files with region name to its respective Region (14) folders. Now i would like to copy all the newly created 14 csv files to a single folder . How can i achieve it through SSIS and can anyone help me achieving it please.

Any help much appreciated.

Solution

  • There are 3 ways to copy files between directories in SSIS:

    1. Using Execute Process Task

    you can use a similar command to copy files:

    COPY c:\Source\*.txt c:\Destination
    1. Using a Script Task

    You can write a small script that loop over files in a directory and copy them to a destination

    string fileName = string.Empty;
string destFile = string.Empty;
string sourcePath = @"C:\test1";
string targetPath = @"C:\test2";   

// Create a new target folder, if necessary. 
if (!System.IO.Directory.Exists(targetPath))
{
    System.IO.Directory.CreateDirectory(targetPath);
}

if (System.IO.Directory.Exists(sourcePath))
{
    string wildcard = "*.txt";
    string[] files = System.IO.Directory.GetFiles(sourcePath, wildcard);

    // Copy the files and overwrite destination files if they already exist. 
    foreach (string s in files)
    {
        fileName = System.IO.Path.GetFileName(s);
        destFile = System.IO.Path.Combine(targetPath, fileName);
        System.IO.File.Copy(s, destFile, true);
    }
}
else
{
    throw new Exception("Source path does not exist!");
}

    Reference: Copying all files in SSIS without Foreach

    1. Using a File System Task

    You can refer to one of the following links, to get an idea on how File System Task works