locked
Foreach loop container Foreach File Enumerator sort files RRS feed

  • Question

  •  

    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

     

    Wednesday, April 4, 2007 11:49 PM

Answers

  •  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.
    • Proposed as answer by Sudeep Raj Wednesday, November 18, 2009 9:04 AM
    • Marked as answer by Bob Bojanic Monday, November 23, 2009 6:36 PM
    Thursday, April 5, 2007 12:22 AM

All replies

  •  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.
    • Proposed as answer by Sudeep Raj Wednesday, November 18, 2009 9:04 AM
    • Marked as answer by Bob Bojanic Monday, November 23, 2009 6:36 PM
    Thursday, April 5, 2007 12:22 AM
  • I was about to suggest the same; puting the file names in a table give you control over the order....
    Thursday, April 5, 2007 1:42 AM
  • Thanks.  It sounds like a lot of work for something that should be a property setting.

     

    Thursday, April 5, 2007 2:04 PM
  •  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
    Thursday, April 5, 2007 2:08 PM
  •  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 Smile

     

    -Jamie

     

    • Proposed as answer by QuentinR Wednesday, February 25, 2009 8:22 PM
    Thursday, April 5, 2007 2:09 PM
  •  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

     

    Thursday, April 5, 2007 2:14 PM
  •  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?
    Thursday, April 5, 2007 2:16 PM
  •  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

     

    Thursday, April 5, 2007 2:24 PM
  • 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.




    Thursday, April 5, 2007 4:45 PM
  • 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

     

    Thursday, April 5, 2007 5:04 PM
  •  

    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?

     

    Thursday, April 5, 2007 6:19 PM
  • 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.

    Friday, April 6, 2007 12:17 AM
  • 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.
    Friday, April 6, 2007 9:59 PM
  • 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.

    Friday, April 6, 2007 10:09 PM
  •  Cylon2005 wrote:

    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.



    So, the order that SSIS would process them in (and rightly so) is:

    C2007210_82812.XML
    C200729_82812.XML
    P2007210_82812.XML
    P200729_82812.XML

    But that isn't the order you want, is it?  That's a bear of a problem because you'll have to parse off the date component and add add leading zeros (or something else altogether) to get the correct sort order.  If you have any control over the naming convention, get the leading zeros inserted and you won't have the sort order problem anymore.

    eg., C20070209_082812.XML & C20070210_082812.XML
    Friday, April 6, 2007 10:36 PM
  • Here's a script task which sorts files in a folder based on a sort specification, sending the output to a DataTable (containing file attributes) that ADO enumerators in a ForEach Loop can utilize. Files can be sorted by FullName, Name, Extension, Length (aka Size), and the 6 timestamps, in whatever sort order (asc/desc) and combination. The task does not sort by regular expression, nor traverse subdirectories, although adding the latter is not too difficult.

    For input, use a file connection manager (not Flat File), pointing to a folder. "MGR Folder" is the name of the connection manager referenced in the task, so change that string in the script task to point to the name of the connection manager you need.

    For output, use an IS variable of type Object. This variable, named SortedDataTable, is loaded with a DataTable that may then be used with an ADO enumerator. Either add an object variable in the relevent IS package with the name SortedDataTable, or change the task to point to an object variable of a different name.

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports System.IO
    Imports System.Text
    Imports System.Xml
    
    Public Class ScriptMain
        Private exitNow As Boolean
        Private fireInfo as Boolean = True
    
        Private sb As StringBuilder = New StringBuilder()
        Private folderName As String
        Private vars As Variables
        Private isVar As Variable
    
        Private unsortedTable As DataTable
        Private sortedTable As DataTable
    
        Private sortOrder As String = "Length DESC"
        ' Sort specification (e.g. Length DESC, FullName ASC, LastWriteTime DESC ...
    
        Private srcFolderMgr As String = "MGR Folder"
        Private tgtVarName As String = "SortedDataTable"
    
    
        Public Sub Main()
            Dts.TaskResult = Dts.Results.Success
            Try
    
                folderName = _
                DirectCast(Dts.Connections(srcFolderMgr).AcquireConnection(Nothing), String)
                Dts.VariableDispenser.LockOneForWrite(tgtVarName, vars)
                isVar = vars(tgtVarName)
                If Not isVar.DataType = TypeCode.String _
                AndAlso Not isVar.DataType = TypeCode.Object Then
                    exitNow = Dts.Events.FireError(1, String.Empty, _
                        String.Format("Set var [{0}] to String or Object", tgtVarName), _
                        String.Empty, 0)
                    Dts.TaskResult = Dts.Results.Failure
                    Exit Sub
                End If
                Dts.Events.FireProgress("Start", 0, 0, 0, String.Empty, True)
                Dts.Events.FireInformation(0, String.Empty, _
                    String.Format("Attempting to retrieve sorted files from [{0}]", _
                    folderName), String.Empty, 0, fireInfo)
    
                CreateFileAttributeCols()
                LoadFileAttributesInRows()
                sortedTable = CopySortedFiles()
                Select Case isVar.DataType
                    Case TypeCode.Object
                        isVar.Value = sortedTable
                        ' Write DataTable to an IS object variable for use 
                        ' with a " For ADO Enumerator "
                    Case TypeCode.String
                        ' or instead, write xml to an IS String variable 
                        '  for NodeList Enumerator or XML source pipeline component
                        Using xw As XmlWriter = XmlWriter.Create(sb)
                            sortedTable.WriteXml(xw, XmlWriteMode.WriteSchema)
                        End Using
                        isVar.Value = sb.ToString().Replace(""""c, "'"c)
                End Select
                vars.Unlock()
                Dts.Events.FireProgress("Complete", 100, 0, 0, String.Empty, True)
            Catch ex As Exception
                exitNow = Dts.Events.FireError(1, ex.TargetSite.ToString(), _
                    ex.ToString(), String.Empty, 0)
                Dts.TaskResult = Dts.Results.Failure
            Finally
                If vars IsNot Nothing AndAlso vars.Locked Then
                    vars.Unlock()
                End If
            End Try
        End Sub
    
        Private Function CopySortedFiles() As DataTable
            Dim dv As DataView = New DataView(unsortedTable)
            dv.Sort = sortOrder
            ' sort files by sort spec
            Return dv.ToTable("SortedFile")
        End Function
    
        Private Sub CreateFileAttributeCols()
            unsortedTable = New DataTable("File")
            ' create attributes for mapping
            unsortedTable.Columns.Add(New DataColumn("FullName", GetType(String)))
            unsortedTable.Columns.Add(New DataColumn("Name", GetType(String)))
            unsortedTable.Columns.Add(New DataColumn("Extension", GetType(String)))
            unsortedTable.Columns.Add(New DataColumn("Length", GetType(Int64)))
            unsortedTable.Columns.Add(New DataColumn("CreationTime", GetType(DateTime)))
            unsortedTable.Columns.Add(New DataColumn("LastAccessTime", GetType(DateTime)))
            unsortedTable.Columns.Add(New DataColumn("LastWriteTime", GetType(DateTime)))
            unsortedTable.Columns.Add(New DataColumn("CreationTimeUtc", GetType(DateTime)))
            unsortedTable.Columns.Add(New DataColumn("LastAccessTimeUtc", GetType(DateTime)))
            unsortedTable.Columns.Add(New DataColumn("LastWriteTimeUtc", GetType(DateTime)))
        End Sub
    
        Private Sub LoadFileAttributesInRows()
            Dim d As DirectoryInfo = New DirectoryInfo(folderName)
            For Each fi As FileInfo In d.GetFiles()
                ' Load file metadata
                Dim dr As DataRow = unsortedTable.NewRow()
                dr("FullName") = fi.FullName
                dr("Name") = fi.Name
                dr("Extension") = fi.Extension
                dr("Length") = fi.Length
                dr("CreationTime") = fi.CreationTime
                dr("LastAccessTime") = fi.LastAccessTime
                dr("LastWriteTime") = fi.LastWriteTime
                dr("CreationTimeUtc") = fi.CreationTimeUtc
                dr("LastAccessTimeUtc") = fi.LastAccessTimeUtc
                dr("LastWriteTimeUtc") = fi.LastWriteTimeUtc
                unsortedTable.Rows.Add(dr)
            Next
        End Sub
    End Class
    
    

    If you point the output to a variable of type String, the task will write sorted XML output, which may be used with either a NodeList enumerator in a ForEach Loop, or an xml source in a data flow task, to at least visualize (with a data viewer) the sorted files.

    For an Xml Source, use a Data access mode "XML data from variable" with "Use inline schema" checked.

    For a NodeList enumerator, the settings would be as follows:


    EnumerationType: ElementCollection
    OuterXPathStringSourceType: DirectInput
    OutputXPathString: //SortedFile
    InnerElementType: NodeText
    InnerXPathStringSourceType: DirectInput
    InnerXPathString: *

    Wednesday, April 11, 2007 3:14 PM
  • Good stuff, jaegd.  This will certainly come in handy.

    Just want to add that this doesn't help the OP's issue because his filenames are not sortable correctly for what is desired without using regular expressions.  I only bring this up because the OP has not returned to this thread and I would like to be able to mark this thread as answered.
    Wednesday, April 11, 2007 3:22 PM
  • Fair enough, I'll look to add a "Regex" column to the DataTable in the task, the Match on which one can sort. I'll need to do a little more C# to VB.NET translation Smile
    Wednesday, April 11, 2007 3:34 PM
  •  jaegd wrote:
    Fair enough, I'll look to add a "Regex" column to the DataTable in the task, the Match on which one can sort. I'll need to do a little more C# to VB.NET translation


    Yeah, I didn't want to necessary say that your solution didn't add value; it does.  I just didn't want the OP to come in here and assume that his problem is solved...

    Good luck with the code addition!
    Wednesday, April 11, 2007 3:36 PM
  • The below script task will sort by a a given directories files by file substring (via regular expressions ) as well as name, date, and timestamp. A pattern based "substring" will do no good with ambiguous dates, but what will beyond an upstream change or a custom solution? As has been mentioned, no standard sort will disambiguate all 7 digit dates as this post requests. 2007113 would be found in a filename, what date would that represent (rhetorical question)?

    For input, use a file connection manager (not Flat File), pointing to a folder. "MGR Folder" is the name of the connection manager referenced in the task, so change that string in the script task to point to the name of the connection manager you need.

    For output, use an IS variable of type Object. This variable, named SortedDataTable, is loaded with a DataTable that may then be used with an ADO enumerator. Either add an object variable in the relevent IS package with the name SortedDataTable, or change the task to point to an object variable of a different name.

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports System.IO
    Imports System.Text
    Imports System.Xml
    Imports System.Text.RegularExpressions
    
    Public Class ScriptMain
        Private exitNow As Boolean
        Private fireInfo As Boolean = True
    
        Private sb As StringBuilder = New StringBuilder()
        Private folderName As String
        Private vars As Variables
        Private isVar As Variable
    
        Private unsortedTable As DataTable
        Private sortedTable As DataTable
    
        Private parseRegex As Regex
        Private validParsePattern As Boolean = True
        Private totalSubMatches As Int32 = 0
    
        Private srcFolderMgr As String = "MGR Folder"
        ' name of source file connection manager
        Private tgtVarName As String = "SortedDataTable"
        ' name of target variable ( Object variable which will be loaded with sorted files)
    
        Private sortOrder As String = "Length DESC"
        ' File Sort specification (e.g. Length DESC, NAME ASC, LastWriteTime DESC, Substring DESC ...
        Private parsePattern As String = "(\d+)"
        ' Substring(s) from file, extracted via regular expression
        ' and loaded to string column named "Substring"
        Private searchPattern As String = "*.*"
    
        Public Sub Main()
            Dts.TaskResult = Dts.Results.Success
            Try
                IsPatternValid()
    
                folderName = _
                DirectCast(Dts.Connections(srcFolderMgr).AcquireConnection(Nothing), String)
                Dts.VariableDispenser.LockOneForWrite(tgtVarName, vars)
                isVar = vars(tgtVarName)
                If Not isVar.DataType = TypeCode.String _
                AndAlso Not isVar.DataType = TypeCode.Object Then
                    exitNow = Dts.Events.FireError(1, String.Empty, _
                        String.Format("Set var [{0}] to String or Object", tgtVarName), _
                        String.Empty, 0)
                    Dts.TaskResult = Dts.Results.Failure
                    Exit Sub
                End If
                Dts.Events.FireProgress("Start", 0, 0, 0, String.Empty, True)
                Dts.Events.FireInformation(0, String.Empty, _
                    String.Format("Attempting to retrieve sorted files from [{0}]", _
                    folderName), String.Empty, 0, fireInfo)
    
                CreateFileAttributeCols()
                LoadFileAttributesInRows()
                sortedTable = CopySortedFiles()
                Select Case isVar.DataType
                    Case TypeCode.Object
                        isVar.Value = sortedTable
                        ' Write DataTable to an IS object variable for use 
                        ' with a " For ADO Enumerator "
                    Case TypeCode.String
                        ' or instead, write xml to an IS String variable 
                        '  for NodeList Enumerator or XML source pipeline component
                        Using xw As XmlWriter = XmlWriter.Create(sb)
                            sortedTable.WriteXml(xw, XmlWriteMode.WriteSchema)
                        End Using
                        isVar.Value = sb.ToString().Replace(""""c, "'"c)
                End Select
                vars.Unlock()
                Dts.Events.FireProgress("Complete", 100, 0, 0, String.Empty, True)
            Catch ex As Exception
                exitNow = Dts.Events.FireError(1, ex.TargetSite.ToString(), _
                    ex.ToString(), String.Empty, 0)
                Dts.TaskResult = Dts.Results.Failure
            Finally
                If vars IsNot Nothing AndAlso vars.Locked Then
                    vars.Unlock()
                End If
            End Try
        End Sub
    
        Private Function CopySortedFiles() As DataTable
            Dim dv As DataView = New DataView(unsortedTable)
            dv.Sort = sortOrder
            ' sort files by sort spec
            Return dv.ToTable("SortedFile")
        End Function
    
        Private Sub CreateFileAttributeCols()
            unsortedTable = New DataTable("File")
            ' create attributes for mapping
            unsortedTable.Columns.Add(New DataColumn("FullName", GetType(String)))
            unsortedTable.Columns.Add(New DataColumn("Name", GetType(String)))
            unsortedTable.Columns.Add(New DataColumn("Extension", GetType(String)))
            unsortedTable.Columns.Add(New DataColumn("Length", GetType(Int64)))
            unsortedTable.Columns.Add(New DataColumn("CreationTime", GetType(DateTime)))
            unsortedTable.Columns.Add(New DataColumn("LastAccessTime", GetType(DateTime)))
            unsortedTable.Columns.Add(New DataColumn("LastWriteTime", GetType(DateTime)))
            unsortedTable.Columns.Add(New DataColumn("CreationTimeUtc", GetType(DateTime)))
            unsortedTable.Columns.Add(New DataColumn("LastAccessTimeUtc", GetType(DateTime)))
            unsortedTable.Columns.Add(New DataColumn("LastWriteTimeUtc", GetType(DateTime)))
            unsortedTable.Columns.Add(New DataColumn("Substring", GetType(String)))
        End Sub
    
        Private Sub LoadFileAttributesInRows()
            Dim d As DirectoryInfo = New DirectoryInfo(folderName)
            For Each fi As FileInfo In d.GetFiles(searchPattern)
                ' Load file metadata
                Dim dr As DataRow = unsortedTable.NewRow()
                dr("FullName") = fi.FullName
                dr("Name") = fi.Name
                dr("Extension") = fi.Extension
                dr("Length") = fi.Length
                dr("CreationTime") = fi.CreationTime
                dr("LastAccessTime") = fi.LastAccessTime
                dr("LastWriteTime") = fi.LastWriteTime
                dr("CreationTimeUtc") = fi.CreationTimeUtc
                dr("LastAccessTimeUtc") = fi.LastAccessTimeUtc
                dr("LastWriteTimeUtc") = fi.LastWriteTimeUtc
                dr("Substring") = ExtractMatchedText(fi.Name)
                unsortedTable.Rows.Add(dr)
            Next
        End Sub
    
        Private Sub IsPatternValid()
            Try
                parseRegex = New Regex(parsePattern, RegexOptions.IgnorePatternWhitespace)
                totalSubMatches = parseRegex.GetGroupNumbers().Length - 1
            Catch ex As Exception
                Dts.Events.FireWarning(0, String.Empty, _
                    String.Format("The pattern ""{0}"" not a regular expression, ", _
                    parsePattern), String.Empty, 0)
                validParsePattern = False
            End Try
        End Sub
    
        Private Function ExtractMatchedText(ByVal fileName As String) As String
            Dim sb As StringBuilder = New StringBuilder()
            If validParsePattern = True Then
                Dim m As Match = parseRegex.Match(fileName)
                If m.Success Then
                    If totalSubMatches = 0 Then
                        Return m.Value
                    Else
                        For subMatchIndex As Int32 = 1 To totalSubMatches
                            sb.Append(m.Groups(subMatchIndex).Value)
                        Next
                    End If
                End If
            End If
            Return sb.ToString()
        End Function
    
    End Class
    
    In the Script Task itself, add a reference to the System.Xml assembly stored in System.Xml.dll via the Add Reference Menu. Also, to build regular expressions, the utility to use is the pattern builder called Expresso available at http://www.ultrapico.com , which allows expressions to be visually tested (among many other features).
    Friday, April 13, 2007 5:31 AM
  • I had a similar problem (files were named like FILE1, FILE2, ... FILE10, FILE11, etc.) and inspired by this thread I wound up extending the C# For Each Directory sample.  Here's the critical bits

    // modify AddDirectory to add file names instead of directory names
            private void AddDirectory(DirectoryInfo dirInfoParent, bool addParent)
            {
                DirectoryInfo[] dirInfoChildren = dirInfoParent.GetDirectories();

                /// Add the files in the parent parent.
                if (addParent) {
                    foreach (FileInfo file in dirInfoParent.GetFiles()) {
                        files.Add(file.FullName);
                    }

                    /// Add the subfolders of the parent.
                    foreach (DirectoryInfo dirInfoChild in dirInfoChildren) {
                        foreach (FileInfo file in dirInfoChild.GetFiles()) {
                            files.Add(file.FullName);
                        }

                        if (this.enumerateSubFoldersValue && !this.siblingFoldersBeforeSubFoldersValue) {
                            /// Subfolders of the subfolder, or the sibling folders and then subfolders?
                            AddDirectory(dirInfoChild, false);
                        }
                    }

                    /// Sibling folders before the subfolders folders, so now that the siblings
                    /// have been added, add the children of the siblings.
                    if (this.enumerateSubFoldersValue && this.siblingFoldersBeforeSubFoldersValue) {
                        foreach (DirectoryInfo dirInfoChild in dirInfoChildren)
                            AddDirectory(dirInfoChild, false);
                    }
                }


    // create a comparer that uses creation time
        public class FileTSComparer : IComparer
        {
            public int Compare(object a, object b)
            {
                FileInfo fa = new FileInfo(a.ToString());
                FileInfo fb = new FileInfo(b.ToString());       

                return fa.CreationTimeUtc.CompareTo(fb.CreationTimeUtc);
            }
        }

    // then in GetEnumerator call sort before returning the enumerator
    ...
                        files.Sort(new FileTSComparer()); // I renamed the "directories" array to "files"
                        return files.GetEnumerator();


    Thursday, May 31, 2007 9:11 PM
  • There is an easy solution for this.

    Use Item Enumerator.

    Get a list of files using .net code and Sort them using ICompare

    If you want sample how to perform looping over custom file list then check Package Builder Wizard of BI xPress. It has many predefined samples.


    Try BI xPress and get many templates for free

    http://www.pragmaticworks.com/Products/Business-Intelligence/BIxPress/

    Try Task Factory and get many Tasks For free

    http://www.pragmaticworks.com/Products/Business-Intelligence/TaskFactory

     


    SSIS-Tutorials-FAQs | Convert DTS to SSIS | Document Entire BI | SSIS Tasks | BIDS Plugin
    Wednesday, November 18, 2009 7:19 AM
  • This will not work :
      Execute SQL TASK oledb "select ld into variable order by sth"
      will not fill a SSIS package variable in order, so a following for each loop container will not go as "order by sth" !!
    Following script, inspirated by Jaegd's one, below in this post, Sorts a package variable of type object, previously filled
       using an Execute SQL TASK

    ' Microsoft SQL Server Integration Services Script Task
    ' Write scripts using Microsoft Visual Basic
    ' The ScriptMain class is the entry point of the Script Task.
    
    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports System.Xml
    Imports System.Data.OleDb
    
    
    Public Class ScriptMain
        ' Sorts a package variable of type object, previously filled
        ' usign an Execute SQL TASK
        ' Author : Federico Iori, fediori@gmail.com
        Public Sub Main()
            Dim filenames(300) As String 'array must be dimensioned
            Dim i As Integer
            i = 0
            Dim oleDA As New OleDbDataAdapter
            Dim dt As New DataTable
            Dim dtt As New DataTable
            Dim col As DataColumn
            Dim row As DataRow
    
            oleDA.Fill(dt, Dts.Variables("LOOP_DATES").Value)
    
            For Each row In dt.Rows
                For Each col In dt.Columns
                    MsgBox(row(0).ToString + "-" + CStr(i) + col.ColumnName)
                    'filenames.SetValue(row(0).ToString, i)
                    i = i + 1
                Next
            Next
            Dim dv As DataView = New DataView(dt)
            dv.Sort() = "ld asc" ' name of the column from the select statement
            dtt = dv.ToTable("dtt")
            i = 0
            For Each row In dtt.Rows
                MsgBox(row(0).ToString + "-" + CStr(i))
                'filenames.SetValue(row(0).ToString, i)
                i = i + 1
            Next
            'Array.Sort(filenames)
            ' Tryng to sort array and then assign it to variable did not work
            Dts.Variables("LOOP_DATES").Value = CType(dtt, Object)
            Dts.TaskResult = Dts.Results.Success
    
        End Sub
    
    End Class

    Friday, March 12, 2010 10:06 AM
  • I correct myself: it is not true that Execute SQL TASK oledb "select ld into variable order by sth"
      will not fill a SSIS package variable in order, so a following for each loop container will not go as "order by sth" !! Order by in the select will be preserved , so sorting a package variable is useful when working with files.
    Monday, March 15, 2010 7:17 AM
  • Here is an example of a Foreach Loop Container that is sorted in filedates:

    http://microsoft-ssis.blogspot.com/2011/04/how-to-configure-foreach-loop-container.html

     


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Friday, April 22, 2011 7:46 PM
  • And now also available as Custom Enumerator: http://microsoft-ssis.blogspot.com/2012/01/custom-ssis-component-foreach-sorted.html


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Friday, January 20, 2012 10:19 PM
  • Consider using Script Task using methods from LINQ as per my article: https://www.sqlshack.com/using-ssis-foreach-loop-containers-process-files-date-order/
    Thursday, August 3, 2017 6:41 PM