A common request I have received over the years is to move or copy files from one directory to another based on the file name. This can be accomplished in SSIS without using any script task. You can do this with a for each loop, an empty sequence container, a file system task, and a few expressions on some variables.
Here is a look at the entire package:
Now here are the variables, the variables with the red circle have expressions on them. You can build this package with fewer variables but that would cause maintenance headaches in the future. This package can be maintained using configuration files or tables and control all variables without an expression. The variables with expressions are based on the other variables so the configuration changes would up these indirectly.
The first thing you need to do is create a for each loop and map in the variables to the directory and file spec properties. In the image below you can see the directory is using the input directory variable and the file spec is using the file extension and has an asterisk in front of the expression for the wild card. Notice also that you only need the file name only.
The file name is mapped to the current file variable.
Here are the files that you want to loop over and check the date. If the date is older than one day then you want the file to be moved to the backup folder.
Now you need a sequence container inside the for each loop. There will be no task in this sequence container because the container is used as an anchor point for the expression between the sequence container and the file system task.
Below you can see the file system task is set up to use the variables and is set to move the files. This could also be changed to copy or delete the file based on your needs.
Now for the expressions, the variables with the red circles next to them have expressions on them.
The dtFileDate variable is used to get the date from the current file name in the loop. This takes a combination of right and substring functions to parse out the date as the following format yyyy-mm-dd. Then this is converted from a string to date. This is now used in the precedence constraint expression between the sequence container and the file system task to see if the date meets the move criteria.
dtFileDate=
(DT_DATE) (Substring(Right(@[User::strCurrentFile],8),1,4)+ “-” +
Substring( Right(@[User::strCurrentFile],4),1,2)+”-“+
Right(@[User::strCurrentFile],2))
The dtCutoffDate Variable uses the strDaysBack variable to subtract the number of days from today’s date. This allows you to update the strDaysBack variable using configurations. This is why it is a good idea to never hard code in the values in your expressions. Always use a separate variable to reduce maintenance.
dtCutOffDate =
DATEADD( “d”, @[User::strDaysBack] , GETDATE() )
The strFullFileName Variable combines three variables to get the complete path to the current file. This is needed because we only get the name of the file in the for each loop. Getting just the name in the for each loop makes it easier to get the date in the file name.
strFullFileName =
@[User::strInputDirectory]+ @[User::strCurrentFile]+ @[User::strFileExtension]
The last thing you need is the expression between the sequence container and the file system task. This compares the dates and if the expression is true then the file system task is performed.
Hope this help you when building complex packages looping through files. Keep in mind if your files have a different date format you will need to adjust your expressions to get the date. Let me know if you have any questions.
MikeDavisSQL 
Where do you insert/set:
dtFileDate=
(DT_DATE) (Substring(Right(@[User::strCurrentFile],8),1,4)+ “-” +
Substring( Right(@[User::strCurrentFile],4),1,2)+”-”+
Right(@[User::strCurrentFile],2))
Selwyn, it is on the the precedence constraint line.
I thought that “@dtFileDate <= @dtCutOffDate " is on the precedence constraint line??
Can you give me a screen shot??….
Where to put expressions is not so clear.can you elaborate some more extent.i need little urgently
Selwyn, Sorry you are right. The first expresion you asked about is the one on the variable dtFileDate
I have the variable name above each expression that is in a variable and the last expression is in the precedence constraint.
Instead of precedence constraint we can also use that expression in the Disable properties of the container task just add !(expression)
Yep, that would work too
I can’t find where to put the code(expressions) for any of the variables:
dtFileDate=
dtCutOffDate =
strFullFileName =
Please provide instructions or a screen shot.
thanks!
That code is not entered. It is the expression on the variable.
Hi,
I’ve tried this solution and it works almost perfectly for what I need to do so thank you very much for that. The reason why I say almost is because I have 2 small problems. 1. How would I change the above to copy different types of files ie. I have .pdf’s as well as .doc’s. 2. I have 2 folders setup for now… June 2014 and July 2014. The scripts puts all the .pdf files into the correct folders except that it puts the files for July 1st into the June folder. Any ideas as to why?
Thanx
I would have to look at the package to troubleshoot this. Try putting some break points on it and looking at the variable values in the locals window.
Inserted a breakpoint and basically what happens is that dtFileDate changes from 2014-06-30 to 2014-07-01 but the strBackupDirectory doesn’t change to July. Only once the file name changes to 2014-07-02 does strBackupDirectory to July.
Hi, I have a file by name of ‘B2KUDF.000071.201504290436’ which is YYYYMMDDHHMM but I want to find the file from my SSIS package. I dont the time so I want to try finding by ‘B2KUDF.000071.20150429’ (excluding time). I gave the * at the end but it doesnt work and gives me illegal path error. When I remove the asterik it works fine. Using foreachloop container and task in it. Let me know if you have solution for this
i add
(DT_DATE) (Substring(Right(@[User::strCurrentFile],8),1,4)+ “-” + Substring( Right(@[User::strCurrentFile],4),1,2)+”-“+ Right(@[User::strCurrentFile],2))
to dtFileDate Expressions and get this returned
TITLE: Expression Builder
——————————
Expression cannot be evaluated.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2008&ProdVer=9.0.30729.1&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476
——————————
ADDITIONAL INFORMATION:
Attempt to parse the expression “(DT_DATE) (Substring(Right(@[User::strCurrentFile],8),1,4)+ “-” + Substring( Right(@[User::strCurrentFile],4),1,2)+”-“+ Right(@[User::strCurrentFile],2))” failed. The token ” ” at line number “1”, character number “61” was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.
(Microsoft.DataTransformationServices.Controls)
——————————
BUTTONS:
OK
——————————
If you have copied the expression it is throwing the above error message , delete the “-” and try again and it works .
I also tried
@dtFileDate=(DT_DATE) (Substring(Right(@[User::strCurrentFile],8),1,4)+ “-” + Substring( Right(@[User::strCurrentFile],4),1,2)+”-“+ Right(@[User::strCurrentFile],2))
No no , you cannot use as is
@dtFileDate=(DT_DATE) (Substring(Right(@[User::strCurrentFile],8),1,4)+ “-” + Substring( Right(@[User::strCurrentFile],4),1,2)+”-“+ Right(@[User::strCurrentFile],2))
What Mike says in the above example is , under expression for the variable @dtFileDate, you’ll need to use (DT_DATE) (Substring(Right(@[User::strCurrentFile],8),1,4)+ “-” + Substring( Right(@[User::strCurrentFile],4),1,2)+”-“+ Right(@[User::strCurrentFile],2)) and evaluate if it is working or not. but make sure you use the same filename as the example is tailored to.
you’ll need to delete “-” and type them again , it worked for me . i’m working on similar request but i need to delete files
What is (DT_DATE)?
https://msdn.microsoft.com/en-us/ms141036.aspx
Please refer to the link
I mean delete “-” and type them again , i thing SSIS is not picking up the characters
This was exactly what I needed, after much searching on the internet. Thanks for taking the time to post.