The mystery of MULTIFILE

So the other day I was wondering what the heck a Multiple Files connection manager was good for. I read this statement in BOL by a respected predecessor...

"A Multiple Files connection manager enables a package to reference existing files and folders or to create files and folders at run time. The tasks and data flow components in Microsoft SQL Server Integration Services that can use information in files perform their work by referencing a Multiple Files connection manager that is included in the package."

...and I snorted rudely and said... "Huh?" So then I searched the Integration Services forum on MSDN, where I found other people (MVPs, no less!) asking the same question, and not getting an answer. Hmmm.

After applying the current U.S. administration's generous torture policy, I finally extracted a confession: There are no built-in tasks or components that work with the Multiple Files connection manager. (Note that we are NOT talking about the Multiple FLAT Files connection manager here, MULTIFLATFILE, which works with the Flat File Source.)

The theory was that these connection managers would let you select multiple files manually, or select files that could not necessarily be selected by specifying wildcards in the Foreach File Enumerator. You could in fact use it for this purpose, but you could only do so easily in a Script task or Script component. Each time that you call the AcquireConnection method of the connection manager, it returns the next filename in its list. The ConnectionString property returns the entire list of files, delimited with the pipe "|" character.

I played around with a MULTIFILE connection a little. I created a test package, added a MULTIFILE and selected some files, then added a Script task and used this quick-and-dirty code to examine the file list and the individual file names:

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

    Public Sub Main()

        Dim multiFileCM As ConnectionManagerMultiFile
        Dim connectionString As String
        Dim delimiter As String
        Dim fileList As String()

        multiFileCM = _
            DirectCast(Dts.Connections("MultiFileConnection").InnerObject, _
            ConnectionManagerMultiFile)

        connectionString = multiFileCM.ConnectionString
        System.Windows.Forms.MessageBox.Show(multiFileCM.ConnectionString, _
            "Connection String")

        delimiter = "|"
        fileList = connectionString.Split(delimiter.ToCharArray())

        For Each currentFile As String In fileList
            System.Windows.Forms.MessageBox.Show(currentFile, "Current File")
        Next

        Dts.TaskResult = Dts.Results.Success

    End Sub

End Class