Foreach loop container Foreach File Enumerator sort files

How do you sort files from the Foreach loop container?

I love this component, except I can't get it to sort by filename.

Nice to know how to sort by timestamps also.

Dave

Cylon2005 wrote:

How do you sort files from the Foreach loop container?

I love this component, except I can't get it to sort by filename.

Nice to know how to sort by timestamps also.

Dave

You can't sort the foreach loop container. My experience is that it always *does* sort by name, but I've never trusted it.

One thing you can do is with a few steps. First use your foreach loop container to get the list of files, and then populate a database table. Then, outside of the foreach loop, use an execute SQL task to select from that table using an ORDER BY. Load an object variable with that result set. Then use a second foreach loop to spin through that object variable (ADO.net recordset). From here you can perform the work you desire.|||I was about to suggest the same; puting the file names in a table give you control over the order....|||

Thanks. It sounds like a lot of work for something that should be a property setting.

|||

Cylon2005 wrote:

Thanks. It sounds like a lot of work for something that should be a property setting.

Post your feedback at http://connect.microsoft.com/sqlserver/feedback|||

Cylon2005 wrote:

Thanks. It sounds like a lot of work for something that should be a property setting.

I would agree. Perhaps you could post a suggestion at connect (http://connect.microsoft.com/sqlserver/feedback/)

Your original mail said you wanted to order by filename. By default, this is what it does. I'm a little bit more trusting than Phil as well - I've never seen it do anything different

-Jamie

|||

Rafael Salas wrote:

I was about to suggest the same; puting the file names in a table give you control over the order....

This SQLCLR sproc *might* help you do this:

Replacement for xp_getfiledetails

(http://blogs.conchango.com/jamiethomson/archive/2006/08/24/4400.aspx)

-Jamie

|||

Jamie Thomson wrote:

I would agree. Perhaps you could post a suggestion at connect (http://connect.microsoft.com/sqlserver/feedback/)

Your original mail said you wanted to order by filename. By default, this is what it does. I'm a little bit more trusting than Phil as well - I've never seen it do anything different

-Jamie

The only reason I've never trusted it is because I've never had to. I haven't had a situation yet where I've been required to process the files in order. My requirements to date have been to process all available files in the directory, order irrelevant.

However, the OP is claiming that it doesn't order by filename. I'm curious to know two things, Dave: 1 - What are your filenames? 2 - How have you confirmed that they are not being processed in order?|||

Cylon2005 wrote:

Thanks. It sounds like a lot of work for something that should be a property setting.

Well, as discussed, it isn't. Have you posted anything to Connect?

-Jamie

|||A ascending/descending Sorted Files ForEach enumerator would be a fairly straight-forward extension of the ForEach Directory enumerator included in the set of SQL samples (SqlServerSamples.msi). For example, I added sorting on properties like name (not full path), extension, size, dates, and a regular expression on the file name itself. The included enumerator is for directories, but modifying it to return sorted files was not much of a change.

The included enumerator sample is "%ProgramFiles%\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\ForEachDirectory Sample", which has both VB.NET and C# versions.

After building the sample, the new enumerator shows up in the For Each container's enumerator list box as "For Each Directory CS", and it has a UI included. Property expressions (on the enumerator itself) may be used as well, as below. The actual enumerated object is an ArrayList of directories. No, this enumerator is not sorted by default, since it calls GetDirectories() on a DirectoryInfo object. Point is, its "easy" (for those with a .NET framework background) to add sorting to it and almost all of it is already built.

Example expressions on enumerator
RootDirectory =>3 "c:\\"
RootDirectorySourceFile => 3 (DirectInput)
EnumerateSubFolders => false

To install the enumerator, modify the project's (in this case C#) post-build event to write the enumerator assembly (which actually contains the enumerator's UI as well) to the Global Assembly Cache and also into the For Each Enumerators directory.

xcopy /y "$(TargetPath)" "%ProgramFiles%\Microsoft SQL Server\90\DTS\ForEachEnumerators"
"$(DevEnvDir)..\..\SDK\v2.0\bin\gacutil.exe" /if "$(TargetPath)"

The second step (gacutil...) is an attempt to write the enumerator to the Global assembly cache. Global assembly cache installs can also be done by copy and paste of the dll (pointed to by $TargetPath) into the "%windir%\assembly" folder.

|||

Wow. Fantastic stuff Jaegd. If I have time I might try and have a play with that later - see if I can get it sorted any differently. Unless you've done it already (which it sounds like you might have done)?

-Jamie

|||

Yes, I have built the sorted files enumerator. The part I'm not sure about is how to build a platform independent installer for SSIS widgets (e.g. an installer that writes to the correct locations on x64 and x86). Installing to the Global assembly cache is no problem.

Perhaps Darren can answer this one or point me in the correct direction. What are the steps necessary to acquire the correct location(s) for a platform indepedent SSIS "widget" install (task,component,enumerator,logger,connection manager). I'm not sure where you can get a platform independent location(s). Registry, environment variable, function call?

|||

SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS\Setup

This registry entry holds the DTS install location for 32-bit. I don't have my 64-bit machine available, so I'm not sure if it is the same for 64-bit. I'll check tomorrow.

|||I've just tested this a little by creating files. Try sort files 1.txt, 2.txt, 21.txt, 3.txt, 33.txt, 31.txt. and msgbox the files in the order they are processed.... Not what you would want or expect for a sort.|||

The files contain date and time stamp.

C200729_82812.XML

C2007210_82812.XML

P200729_82812.XML

P2007210_82812.XML

They are FTP'd over and we decrypt them.

It's be nice if SSIS had the ability to decrypt PGP files too.

Then I wouldn't have to do all this werid batch stuff outside.