Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

SSIS scenarios...
Browse by Tags · View All
MSBI 30
SSIS 27
BRH 15
SQL Server 15
#BI 14
Script Component 9
#SQLSERVER 8
#SQL Server 7
Flat File Source 5
Script Component Source 5

Archive · View All
August 2010 4
March 2011 3
September 2010 3
February 2013 2
June 2011 2
November 2010 2
May 2010 2
November 2009 2
March 2010 2
May 2012 1

SSIS - Delete files in a folder older than a specified number of days

Apr 7 2010 9:01AM by Sudeep Raj   

Scenario: How to delete files created a number of days before today. The files will have the date appended to the file name and we will not check the file properties to decide the file age. The file name would follow the naming convention: File_mmddyyyy.txt

This can be achieved very easily in SSIS. Create an integer variable to store the value stating how old files do you wish to delete. Suppose you wish to delete all files older than 3 days, the variable would have the value 3. I name this variable intFileMaxAge. Make this variable configurable so that it can be changed as and when required.

Next I show the layout of the package.

Control Flow

                         Fig. 1

The Foreach Loop Container is configured to pick the files from a particular folder(you should make this connection configurable) and the “Retrieve file name” property is set to “Name and extension”. Refer fig. 2

ForEach loop

                         Fig. 2

 

 

Next we need to save the file name for each iteration to a string variable strFileName. For this go to the Variable Mappings tab and select the variable strFileName from the drop down box and set the index to zero. Refer fig.  3

 

ForEach loop_VarMapping

                         Fig. 3

Once the above is done put a script task in the Foreach Loop Container. Do not make any changes to the script task. It is just there so that we can have Precedence Constraint before the File system task to control the flow. We need the File System Task to be triggered only when the condition is met i.e. files created before certain days (Refer fig. 1). Double click the precedence  and set the Evaluation operation to Expression. In the Expression put the below expression:

 

DATEDIFF("dd",(DT_Date)(SUBSTRING(@[User::strFileName],6,2) + "-" + SUBSTRING(@[User::strFileName],8,2) + "-" + SUBSTRING(@[User::strFileName],10,4)),GETDATE()) > @[User::intFileMaxAge]

 

The above expression extracts the date part from the file name and converts it to date type. Once that’s done it gets the difference of the between the file date and today’s date. After this the expression checks whether the difference is greater than the value specified in the variable intFileMaxAge.

If the above expression will be evaluated to true the control will go to the File Sytem Task.

Setting up the File System Task. (refer fig. 4) The operation is set to Delete file  as we need to delete files. Set the IsSourcePathVariable to False as I create a connection to connect to the file and use expression to modify the connection at run time.

FileSystem Task

                         Fig. 4

NOTE: During development set the variable strFileName value to File_mmddyyyy.txt  or else the expression will not evaluate. 

In the Connection Managers select the connection manager configured above(refer fig. 4) go to its properties and in the expression set connection string to the following expression:

@[User::strFolderPath]+ @[User::strFileName]

Where the variable strFolderPath contains the folder in which the files are placed. Again make this configurable.

That completes the package.

 

Update: The file format File_mmddyyyy.txt means that the file name will have leading zeros in case the date or month is of single digit. So a valid file name would be File_02182010.txt or File_10042010.txt. In case the file is File_222010 it would be an illegal file and the package would fail and we need to do error handling accordingly.

The expression has been modified to incorporate some validation not all.

LEN(@[User::strFileName]) == 17&& (DT_I8)SUBSTRING(@[User::strFileName],6,2) < 13 && (DT_I8)SUBSTRING(@[User::strFileName],8,2) < 32 && DATEDIFF("dd",(DT_Date)(SUBSTRING(@[User::strFileName],6,2) + "-"+SUBSTRING(@[User::strFileName],8,2) + "-" + SUBSTRING(@[User::strFileName],10,4)),GETDATE()) > @[User::intFileMaxAge]

 

 Thanks Siddharth for pointing this out.

Tags: SSIS, Precedence Constraint, MSBI, DeleteFiles, Foreach Loop Container,


Sudeep Raj
12 · 13% · 4303
2
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

8  Comments  

  • Thanks for sharing this article Sudeep. But I have a different opinion about this approach. Do you feel that the formula you have mentioned is failsafe ? To the best of my understanding this formula would mean that even single digit date should be in dd format like 1 should be 01. There is always a chance of user of system error in such cases. Can you suggest some other approach that is more fail safe or error handling for such cases, which should make the article complete :)

    commented on Apr 6 2010 7:40PM
    ,
    169 · 1% · 293
  • Hi Siddharth, When I mentioned ddmmyyyy format I meant that the date has to be appended with 0 in case of single digit. had it been the other way the format would be dmyyyy. If there is no separator between the days months and year as is the case above there would be no logic to tell what is the date for sure.

    commented on Apr 6 2010 8:03PM
    Sudeep Raj
    12 · 13% · 4303
  • Agreed on your point, but in my views, whenever casting is done, error handling is quite necessary. And it would just make your article a working soluiton. It's just my viewpoint, no offence :)

    commented on Apr 6 2010 8:32PM
    ,
    169 · 1% · 293
  • So when I click on the precedence and choose "Expression" as my Evaluation Operation, I get an error.

    Am I missing something here? I have created the two variables, intFileMaxAge is int32 with value of 3 and strFileName is String with no value.

    commented on Apr 29 2010 5:36PM
    slick
    3063 · 0% · 2
  • While developing the package give some value to the file name variable as well. What is the error that you are getting?

    commented on Apr 29 2010 11:31PM
    Sudeep Raj
    12 · 13% · 4303
  • Hello. you mention "make variable CONFIGURABLE" and then mention making other objects in this dtsx configurable... i'm not sure what you mean by this? I understand the concept... should make them be able to update when query runs... but... can you describe a bit more detail? (especially first part, the variable configurable?)

    thanks\ alex

    commented on Jun 26 2010 12:14AM
    alenknight
    3063 · 0% · 2
  • Hi Alen, Have a look at this link on package configuration. Let me know if you need further clarification. http://www.mssqltips.com/tip.asp?tip=1405

    commented on Jul 17 2010 9:02AM
    Sudeep Raj
    12 · 13% · 4303
  • nice article ......good job bro..

    commented on Jan 8 2011 4:11PM
    kishorereddymedi
    2038 · 0% · 8

Your Comment


Sign Up or Login to post a comment.

"SSIS - Delete files in a folder older than a specified number of days" rated 5 out of 5 by 2 readers
SSIS - Delete files in a folder older than a specified number of days , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]