File System Task Assistance

SSIS and Import/Export
1

Hello, I'm trying to setup a task in SSIS to transfer a file generated by a client machine to a directory on the server. The client machine puts it on the local c:\ partition. I'd like the package to grab this file and put in on the server.

The file name will vary depending on the time it is run. So for example, the file can be named:

12765_201507091211_prodin.msf

This file is time stamped with a date of 20150709 at 12:11. What is the best way to have my SSIS package grab the latest file in this directory, move it to my server, then archive it?

Thanks so much for your assistance.

Mike

2

Use a foreach loop container with a script task:

To check for exclusive access, use a script task as the first step in the container, with the following code (variables need setting, but they are in the script):

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };

    public void Main()
    {

        try
        {
            string filePath = Dts.Variables["FilePath"].Value.ToString();
            string fileMask = Dts.Variables["FileMask"].Value.ToString();

            foreach (string dataFile in Directory.GetFiles(filePath, fileMask))
            {
                //Console.WriteLine(dataFile);
                while (1 == 1)
                {
                    try
                    {
                        using (Stream stream = new FileStream(dataFile, FileMode.Open))
                        {
                        }

                        Dts.Variables["FileName"].Value = Path.GetFileName(dataFile);

                        break;
                    }
                    catch
                    {
                    }

                    Thread.Sleep(5000);
                }
                break;
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        catch
        {
            Dts.TaskResult = (int)ScriptResults.Failure;
            throw;
        }
    }
}