The File Connection Manager in SSIS enables SQL Server Integration Services packages to connect with the Files and Folders present in the File System. The SSIS File Connection Manager not only allows us to connect with existing files and folders but also creates New files and folders at run time.
TIP: In SSIS, File System Task uses File Connection Manager only to connect with the Files and Folder.
Configuring File Connection Manager in SSIS
In this example, we are going to show how to create or configure the File Connection Manager in SSIS (SQL Server Integration Services)
Once you create a new Project, if you look at the Solution explorer, we have three folders. For now, let us concentrate on the Connection Managers Folder.
Right-click on the Connection Managers folder and select the New Connection Manager option from the context menu.
When you click on the New Connection Manager option, an Add Connection Manager window form will open. Use this to select the connections managers from the list.
Here we have to select the File Connection Manager from the provided list
Once you choose the Connection Managers for files, the SSIS File Connection Manager Editor opens in a new window. Using this editor, we have to configure the connection with files and folders.
Available File and Folder Options
- File: This text box will display the full path of the selected file or folder.
- Browse: This button will be used to traverse the file system to locate the required file or folder.
- Usage Type: File Connection Manager provides us with four different options to connect with the files and folders. The below table explains their functionality in detail:
|Create File||This option creates a new file at run time. For example, If we are renaming the file, then we have to create a new file with a new name.|
|Existing File||It is helpful to work with an already existing file in the file system. For instance, If we want to copy or move a file from one location to another, then we have to select this option.|
|Create Folder||This SSIS File Connection Manager option creates a new folder at run time. For example, If we want to copy or move directory content from one location to another, we can create a new folder with a better name.|
This option will help to work with an already existing folder in the file system. For instance, If we want to copy or move a directory from one location to another, use this option to select the required folder.
If you select the Existing File option, when you click on the Browse button, it will open the Select File window to select the existing files.
If you select the Existing Folder option, when you click on the Browse button, it opens the Browse For Folder window to choose the existing folders.
Once you have selected the required file, Click on the Ok button
Click the Ok button to finish configuring the File Connection Manager.
From the above screenshot, you can observe that we successfully created File Connection Manager in SSIS.