SSIS – Delete all files except for the most recent onehttp://sqlblog.com/blogs/jorg_klein/archive/2010/05/28/ssis-delete-all-files-except-for-the-most-recent-one.aspxQuite often one or more sources for a data warehouse consist of flat files. Most of the times these files are delivered as a zip file with a date in the file name, for example FinanceDataExport_20100528.zip Currently I work at a project that does a fullenCommunityServer 2.1 SP2 (Build: 61129.1)re: SSIS – Delete all files except for the most recent onehttp://sqlblog.com/blogs/jorg_klein/archive/2010/05/28/ssis-delete-all-files-except-for-the-most-recent-one.aspx#25682Fri, 28 May 2010 15:49:59 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:25682Todd McDermid<p>Great post, Jorg - I can't tell you how often this question is asked on the forums. &nbsp;Now I'll just have to remember where to find this post so I can refer people to it!</p>
<p>Can you edit that sample above to use FileInfo to get the actual modification date for your comparison? &nbsp;Even though most people inquire about doing it exactly the way you have (the date is encoded in the file name) - they also tend to ask the same question when it's not.</p>
<p>Thanks!</p>
re: SSIS – Delete all files except for the most recent onehttp://sqlblog.com/blogs/jorg_klein/archive/2010/05/28/ssis-delete-all-files-except-for-the-most-recent-one.aspx#25705Sun, 30 May 2010 02:17:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:25705Mike C<p>Todd - I created a similar package a while back -- FileInfo was very slow over the network for some reason. &nbsp;I found the Directory class methods worked much faster than DirectoryInfo and FileInfo. &nbsp;If you want to quickly modify Jorg's example, just modify it to use the FileInfo .CreationTime property (it's a System.DateTime property): <a rel="nofollow" target="_new" href="http://msdn.microsoft.com/en-us/library/system.io.filesysteminfo.creationtime">http://msdn.microsoft.com/en-us/library/system.io.filesysteminfo.creationtime</a>(v=VS.100).aspx</p>
re: SSIS – Delete all files except for the most recent onehttp://sqlblog.com/blogs/jorg_klein/archive/2010/05/28/ssis-delete-all-files-except-for-the-most-recent-one.aspx#25780Tue, 01 Jun 2010 12:34:54 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:25780jorg<p>@Todd: Thanks, I assume this post can help quite some people and that why I wrote it :-) I have changed the code so you can choose between using a date in the filename or the creation date. Thanks for the suggestion.</p>
<p>@Mike C: I haven't experienced any problems with FileInfo over the network here so I haven't changed the code. Thanks for pointing me to FileInfo .CreationTime!</p>
re: SSIS – Delete all files except for the most recent onehttp://sqlblog.com/blogs/jorg_klein/archive/2010/05/28/ssis-delete-all-files-except-for-the-most-recent-one.aspx#28031Thu, 19 Aug 2010 01:08:55 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:28031Paul<p>I would suggest you use ModificationTime on the file, as creation time is when the file arrives on your filesystem, not when it was last written.</p>
<p>If you copy the file from a server to your machine, the Creation Time is when you copied the file, Not the CreationDate you actually expect.</p>
<p>Modificationtime remains consistent in both scenarios.</p>
re: SSIS – Delete all files except for the most recent onehttp://sqlblog.com/blogs/jorg_klein/archive/2010/05/28/ssis-delete-all-files-except-for-the-most-recent-one.aspx#33468Mon, 14 Feb 2011 16:10:08 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:33468e<p>d</p>
re: SSIS – Delete all files except for the most recent onehttp://sqlblog.com/blogs/jorg_klein/archive/2010/05/28/ssis-delete-all-files-except-for-the-most-recent-one.aspx#39167Tue, 18 Oct 2011 19:34:35 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:39167Dave Ruijter<p>Hi Jorg,</p>
<p>You might note that it is a Microsoft Visual Basic (2008) script and that the System.IO namespace is required, e.g. &quot;Imports System.IO&quot;</p>
<p>Great post!</p>
re: SSIS – Delete all files except for the most recent onehttp://sqlblog.com/blogs/jorg_klein/archive/2010/05/28/ssis-delete-all-files-except-for-the-most-recent-one.aspx#52864Mon, 17 Feb 2014 15:27:57 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:52864Mark<p>Very useful thanks. &nbsp;Needed some code to find the latest csv file in a folder a log it's name, modification\created date etc. &nbsp;This helped me on my way. &nbsp;Excellent post.</p>
re: SSIS – Delete all files except for the most recent onehttp://sqlblog.com/blogs/jorg_klein/archive/2010/05/28/ssis-delete-all-files-except-for-the-most-recent-one.aspx#53712Wed, 23 Apr 2014 23:44:14 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:53712Gusmundo<p>Very good. Just what I needed. :-)</p>
<p>As per a previous post, the only thing missing was.</p>
<p>Imports System.IO</p>
<p>Also very easy to modify to delete all .JPG in directory. which is another common issue if retrieving attachments from emails,</p>
<p>Thanks again.</p>
re: SSIS – Delete all files except for the most recent onehttp://sqlblog.com/blogs/jorg_klein/archive/2010/05/28/ssis-delete-all-files-except-for-the-most-recent-one.aspx#55317Fri, 19 Sep 2014 16:27:47 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:55317Aravind<p>Very Usefull code.</p>
re: SSIS – Delete all files except for the most recent onehttp://sqlblog.com/blogs/jorg_klein/archive/2010/05/28/ssis-delete-all-files-except-for-the-most-recent-one.aspx#55904Mon, 20 Oct 2014 12:06:41 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:55904Pavan<p>Thank you.. It was very helpfull</p>
re: SSIS – Delete all files except for the most recent onehttp://sqlblog.com/blogs/jorg_klein/archive/2010/05/28/ssis-delete-all-files-except-for-the-most-recent-one.aspx#57858Thu, 12 Feb 2015 21:24:21 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:57858Aravind<p>Hi Jorg,</p>
<p>I used your code to keep the recent file and delete the rest of the files. I am getting a weird situation when i used your code where &quot;Get creation date of current file&quot;, I have 4 files in the remote folder &nbsp;where I downloaded these files and move it to local folder and then in local folder i want to delete all files except the recent one</p>
<p>1)2/9/2015</p>
<p>2)2/10/2015</p>
<p>3)2/11/2015</p>
<p>4)2/12/2015</p>
<p>when i ran the script task for the first time it is keeping the file with date 2/9/2015 &nbsp;and deleting the rest and when i ran for the second time it is keeping 2/12/2015 and deleting the rest. is there any bug &nbsp;in the code? i tried so many times but not able to find out why it is behaving like that. can you help me out??</p>
re: SSIS – Delete all files except for the most recent onehttp://sqlblog.com/blogs/jorg_klein/archive/2010/05/28/ssis-delete-all-files-except-for-the-most-recent-one.aspx#57859Thu, 12 Feb 2015 23:14:47 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:57859Aravind<p>Sorry got it,</p>
<p>I was using CreationTime in the code .. instead of Creationtime i need to use LastWriteTime. Then it will work.</p>