Still stuck on this Ill try and make everything a little bit clearer. (For myself and any help I can get)
VARIABLES
I have the following variables
ToSchoolsFullFilePath - Value C:\Services\To_Schools\CAF\4197_CAF_Testfile.xls
ToSchoolsSourcePath – Value C:\Services\To_Schools\
SchoolFromToSchoolsFullFilePath – Expression
SUBSTRING(REVERSE(SUBSTRING(REVERSE(@[User::ToSchoolsFullFilePath]),5,FINDSTRING(REVERSE( @[User::ToSchoolsFullFilePath] ),"\\",1)-5)),1,4)
Which Evaluates to (For Example 4197)
Ensure EvaluateAsExpression is set to True
FOR EACH LOOP
I have a For Each Loop
Collection - ForEach Loop Editor Expressions Directory = @[User::ToSchoolsSourcePath]
Variable Mapping @[User::ToSchoolsFullFilePath]
FILE SYSTEM TASK
And I have a File System Task to move the files to folders dependant upon the 1<sup>st</sup> 4 digits of the code.
IsDestinationPathVariable False
Destination Connection 1101 Which links to the connection Manager 1101 Expression String
"C:\\HotFolders\\Upload\\"+ @[User::SchoolFromToSchoolsFullFilePath]
SourceConnection True User::ToSchoolsFullFilePath
If I run this with the correct file in it works perfectly. All the Files in Services folder in Upload got to the corresponding 4 digit schools folders.
If I add another file that has no corresponding folder (Which may happen)
It errors [File System Task] Error: An error occurred with the following error message: "Could not find a part of the path.".
So for this I have added the new developments
Variables
HotFoldersUploadPath – Value C:\HotFolders\Upload
HotFoldersUploadPathWithSchool - Expression @[User::HotFoldersUploadPath]+ "\\"+@[User::SchoolFromToSchoolsFullFilePath]
If you remember SchoolFromToSchoolsFullFilePath is an original variable and evaluates to the 4 digit School Code
I have also set the property EvaluateAsExpression = True
This full variable evaluates to C:\HotFolders\Upload\4197
Exists int 1 or 0
Script
ReadOnlyVariables User::HotFoldersUploadPathWithSchool
ReadWriteVariables User::Exists
[CODE]
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
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["HotFoldersUploadPathWithSchool"].Value.ToString()))
{
//Directory.CreateDirectory(Dts.Variables["FolderPath"].Value.ToString(), null);
Dts.Variables["Exists"].Value = 1;
}
else
{
Dts.Variables["Exists"].Value = 0;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
[/CODE]
This script task sits within the ForEach loop before the Script task and is joined by a green arrow.
On the green arrow pointing to the script task
Expression and Constraint
Value Success
Expression @[User::Exists]==1
Logical AND
So the directory MUST exist or else the file is passed over.
I run this and everything runs APART from the script task. It seems that everything is being set as 0 and I cant quite figure out why.
The variable HotFoldersUploadPathWithSchool
Must be the problem. Having another look at this
It evaluates to what I need
C:\HotFolders\Upload\4197
So Again I’m at a loss as to why this isn’t working?
Any help on this would be greatly appreciated. Ive come to a bit of a halt on this.
I was hoping to find a way to just keep going if a file fails without having to use a script because clearly Im struggling.
Debbie
Moving Files to folders based on First 4 digits of Name. If Folder doesn't exist DONT move file
