August 26, 2007 at 2:51 pm
Hi
I'm using a ForEachFile iterator to iterate through a bunch of files in a specific folder. I need to make sure though that I iterate through the files in correct order - starting with the oldest one and ending with the lastest. Cna someone please tell me how I do that...?
Help mostly appreciated, thanks
//Helmut
August 29, 2007 at 2:00 am
I use a staging table to hold the filename and modified date which is populated via a stored procedure. I then use a Query to order the filenames to feed another for each loop with the required actions.
I can't remember where I got the code for finding the date modified so here's my stored procedure
ALTER PROCEDURE [dbo].[usp_FileModified]
@Filename VARCHAR(100)
AS
DECLARE @hr INT, --the HRESULT returned from
@objFileSystem INT, --the FileSystem object
@objFile INT, --the File object
@ErrorObject INT, --the error object
@ErrorMessage VARCHAR(255), --the potential error message
@Path VARCHAR(100), --
@DateLastModified datetime
SET NOCOUNT ON
SELECT @hr=0,@ErrorMessage='opening the file system object '
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject',
@objFileSystem OUT
IF @hr=0 SELECT @ErrorMessage='accessing the file '''
@ErrorObject=@objFileSystem
IF @hr=0 EXEC @hr = sp_OAMethod @objFileSystem,
'GetFile', @objFile out,@Filename
IF @hr=0
SELECT @ErrorMessage='getting the attributes of '''
@ErrorObject=@objFile
IF @hr=0 EXEC @hr = sp_OAGetProperty
@objFile, 'Path', @Path OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty
@objFile, 'DateLastModified', @DateLastModified OUT
IF @hr<>0
BEGIN
DECLARE
@Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT
EXECUTE sp_OAGetErrorInfo @ErrorObject,
@Source output,@Description output,
@Helpfile output,@HelpID output
SELECT @ErrorMessage='Error whilst '
RAISERROR (@ErrorMessage,16,1)
END
EXEC sp_OADestroy @objFileSystem
EXEC sp_OADestroy @objFile
INSERT INTO FileModified (Path, Modified)
SELECT @Path,
@DateLastModified
--SELECT [Path]= @Path,
-- [DateLastModified]= @DateLastModified
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
August 29, 2007 at 8:41 pm
Although John's approach will work, I would probably try to avoid using the sp_OACreate procedure and instantiating file system objects in a procedure. This is a SQL2000 way of solving the issue.
For an SSIS approach, I would tend to use a script component. It would be done in much the same way - use the file system objects to loop through the files and stream them into an ADO recordset object that you would then use in a loop container.
I would not be surprized to find all of the source code for this searching on Google as it seems like something that others will have had to do.
Also, if you decide to do this in a stored procedure, xp_cmdShell with a DIR command and the appropriate switches will give you a correctly ordered list as well.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply