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, _

        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")

        Dts.TaskResult = Dts.Results.Success

    End Sub

End Class


Comments (2) -



Having created a SSIS package, the output was in the form of an Excel file, which worked.

The requirement is for output Excel file to be created in the form such as "filename" + Systemdate.xls as that would allow newly generated names. This did not work. How do I generate new Excel file names at runtime.





This is not the best venue for technical questions. I suggest that you get to know the Integration Services forum at">

Your solution will require an SSIS expression to concatenate a dynamic filename. One solution is described in this forum thread:">

Best wishes,


Pingbacks and trackbacks (1)+