SSIS For Each Loop over files with Date in Name

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:
image
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.
image
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.
image
The file name is mapped to the current file variable.
image
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.
image
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.
image
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.

image

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.

Advertisements
This entry was posted in SSIS, Syndication and tagged . Bookmark the permalink.

22 Responses to SSIS For Each Loop over files with Date in Name

  1. Selwyn says:

    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))

  2. MikeDavisSQL says:

    Selwyn, it is on the the precedence constraint line.

  3. Selwyn says:

    I thought that “@dtFileDate <= @dtCutOffDate " is on the precedence constraint line??
    Can you give me a screen shot??….

  4. aditya says:

    Where to put expressions is not so clear.can you elaborate some more extent.i need little urgently

  5. MikeDavisSQL says:

    Selwyn, Sorry you are right. The first expresion you asked about is the one on the variable dtFileDate

  6. MikeDavisSQL says:

    I have the variable name above each expression that is in a variable and the last expression is in the precedence constraint.

  7. Vinay Suneja says:

    Instead of precedence constraint we can also use that expression in the Disable properties of the container task just add !(expression)

  8. Steven says:

    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!

  9. GS says:

    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

    • MikeDavisSQL says:

      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.

      • GS says:

        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.

  10. Bhuvan says:

    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

  11. Brian says:

    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
    ——————————

    • jampa says:

      If you have copied the expression it is throwing the above error message , delete the “-” and try again and it works .

  12. Brian says:

    I also tried

    @dtFileDate=(DT_DATE) (Substring(Right(@[User::strCurrentFile],8),1,4)+ “-” + Substring( Right(@[User::strCurrentFile],4),1,2)+”-“+ Right(@[User::strCurrentFile],2))

    • jampa says:

      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

  13. Brian says:

    What is (DT_DATE)?

  14. jampa says:

    I mean delete “-” and type them again , i thing SSIS is not picking up the characters

  15. Mick Bourke says:

    This was exactly what I needed, after much searching on the internet. Thanks for taking the time to post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s