There was a series of questions (Post1, Post2, Post3) on SSt page no-wrapper"IS MSDN forum regarding moving the files to a folder based on:

Solution:

Step 1: Take a variable SourcePath with value as C:\IS_Test\

Step 2: Take a foreach loop with for each file enumerator as collection. Set an expression for directory as @[User::SourcePath]. Set regex for Files as *.xlsx and retrieve fully qualified name in a variable FilePath. Check the Traverse subfolders box.

Put a file system task (FST2) inside the for each loop with the following:

Step 3: Create a variable filename with expression as:

    SUBSTRING(REVERSE(SUBSTRING(REVERSE(@[User::FilePath]),5,FINDSTRING(REVERSE(@[User::FilePath]),"\\",1)-5)),1,4)

Step 4: Set an expression for connection string of Dest connection manager as:  "C:\\IS_Test\\HotFolders\\Download\\"+@[User::filename]
 
Step 5: Add a string variable FolderPath within scope of the package and set its evaluate as expression property to true.

            Set the expression as: "C:\\....\\Upload\\"+@[User::filename]

Step 6: Add a script task to check if the folder is present before moving the files, using a script task (ST1) and based on the script task's result you can execute the file system task (FST2) to move the files within the foreach loop as ST1-->FST2.

Step 7: Create a variable Exists (Int).Take a script task and configure it as:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
.Runtime;
using System.Windows.Forms;

namespace ST_58766d3d454f4e008ccd08ee2bd2181e.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{
//If folder exists
if (Directory.Exists(Dts.Variables["FolderPath"].Value.ToString()))
{
Dts.Variables["Exists"].Value = 1;
}
else
{
Dts.Variables["Exists"].Value = 0;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}

Step 8: Add a precedence constraint between ST1 and FST2 (double click the thick green arrow between ST1 and FST2) as:

See Also