I am trying to archive a set of files from the source path to an archive path once I have completed doing some Data Flow Task items. Inside of a Foreach Loop Container I have: Script Task -> Data Flow Task -> Execute SQL Task -> File System Task
I have a User variable set to the "Name and extension" item under the Collection settings in the Foreach Loop Container. The variable is called "fileName" and is used in both the Script Task (used to parse out info from file and perform the Execute SQL Task) as well as in the Data Flow Task in the Flat File Connection Manager which in turn is used by the Flat File Source element. I then parse the file and insert the data into a database. All works as it should until I get to the File System Task (FST).
What I would like is to have the file moved to an archive folder once its insert is completed.
Using several links online (here, here and here) I have added different variables that are either hard-coded or derived from massaging other variables.
In any case what happens is that on the FST I get errors like 'invalid characters in path' or 'unknown path'.
If I try to massage the examples linked above to fit my filesystem structure I now get an error on the Flat File Source step in the Data Flow Task stating it cannot find the file specified. This is caused because it
a) cannot find the path to the file because no file path is give, just the filname.ext
b) cannot parse the variable that contains the full path to the source file (@FullSourcePathFileName
which has its value set to @[User::SourcePath]
+ @[User::fileName]
)
I have tested other various modifications including doing exactly what is in the first example I posted (however that one does not actually do anything the Data Flow Task so I just added a Flat File Source step with no destination) and received the same set of errors. I am at a loss here and would like any input on how to solve this issue.
EDIT:
Seems that it keeps failing on the FullArchivePathFileName
- it never evaluates the expression even though I have it set to 'True'. Still confused as to why it is not evaluating it. So I made its expression the same as FullSourcePathFileName
and verified the EvaluateAsExpression flag is set to True. It still does not evaluate this variable.The FullSourcePathFileName
variable is being evaluated just fine.