A File connection manager enables a package to reference an existing file or folder, or to create a file or folder at run time. For example, you can reference an Excel file. Certain components in Microsoft SQL Server Integration Services use information in files to perform their work. For example, an Execute SQL task can reference a file that contains the SQL statements that the task executes. Other components perform operations on files. For example, the File System task can reference a file to copy it to a new location.
Usage Types of the File Connection Manager
The FileUsageType property of the File connection manager specifies how the file connection is used. The File connection manager can create a file, create a folder, use an existing file, or use an existing folder.
The following table lists the values of FileUsageType.
|0||File connection manager uses an existing file.|
|1||File connection manager creates a file.|
|2||File connection manager uses an existing folder.|
|3||File connection manager creates a folder.|
Multiple File or Folder Connections
The File connection manager can reference only one file or folder. To reference multiple files or folders, use a Multiple Files connection manager instead of a File connection manager. For more information, see Multiple Files Connection Manager.
Configuration of the File Connection Manager
When you add a File connection manager to a package, Integration Services creates a connection manager that will resolve to a File connection at run time, sets the File connection properties, and adds the File connection to the Connections collection of the package.
The ConnectionManagerType property of the connection manager is set to FILE.
You can configure a File connection manager in the following ways:
Specify the usage type.
Specify a file or folder.
You can set the ConnectionString property for the File connection manager by specifying an expression in the Properties window of SQL Server Data Tools (SSDT). However, to avoid a validation error when you use an expression to specify the file or folder, in the File Connection Manager Editor, for File/Folder, add a file or folder path.
You can set properties through SSIS Designer or programmatically.
For more information about the properties that you can set in SSIS Designer, see File Connection Manager Editor.