The File System Task in SSIS allows us to perform Ten types of Operations on Files and Folders. In this article We will show you, How to Move Multiple files using File System Task in SSIS with example. To Move the single file from one location to another location, Please refer Move File Using File System Task in SSIS article present in the SSIS page.
We have File System Task Folder inside the Documents drive. Below screenshot shows you the data inside that folder. Our task is to move the 27 files inside the File System Task Folder to Destination Folder inside E Drive.
Move Multiple files using File System Task in SSIS
In order to move multiple files using File System Task in SSIS, First Drag and drop the Foreach Loop Container into the Control Flow region as shown below
Double click on it will open the Foreach Loop Editor to configure it. Before we step into the remaining properties, we have to understand the Enumerator property. From the below screenshot you can observe that, we are selecting Foreach File Enumerator because we want to loop over the files present in our file system.
Next, Click on the Browse button to select the folder name from our file system. From the below screenshot you can observe that, we are selecting FILE SYSTEM TASK folder present in our E Drive. Click OK button to select it.
Within the Files section we have to specify the file type using the Wildcards. In this example, we want to move all files (irrespective of extensions) from one location to another in our local file system so we are using Wildcard *.*.
TIP: If you want to send only text files then use: *.txt
Next, we have to store the file names in a variable so that we can use them in our File System Task. In order to do that, go to Variable Mappings section and select the user variable (if any) or create new variable as shown below
Once you click on the <New Variable..>, Add Variable window will be opened to create new variable. In this example, we require variable to store the File name and its extension for example, SALES1.xls. So, we are creating string variable and assigned the name as Variable. Click OK to finish creating string variable.
Next select the created variable and assign it to index 0 as we shown in below screenshot. Once you are done, click OK to finish configuring the Foreach Loop Container.
Next, Drag and drop the File System Task into the Control Flow region and rename it as per your requirements
Double click on it will open the File System Task Editor to configure it. In this example, We are Moving multiple files from one location to another. So, Please change the operation property to Move File.
Next, we have to configure the SourceConnection property. In our previous step we used the Foreach Loop Container to loop over the files and stored the File names with extension in a variable called Variable. So, we are setting the IsSourcePathVariable to True and selecting the User:Variable variable as SourceVariable as shown in below screenshot.
Now we have to configure the Destination Connection so, Please select the DestinationConnection property. If you already created the File Connection Manager before then select the created one or If you stored the Destination Connection in Variable then, please change the IsDestinationPathVariable property to TRUE and select the Variable Name.
Here, We haven’t created any connection Manager before so, We are selecting <New Connection..>.
In this example we are Moving existing files to an already existing folder. So, we are selecting Existing Folder option from the Usage Type. Next, click on the Browse button to select the Existing Folder from the file system. From the below screenshot you can observe that, We selected the Destination Folder.
Click Ok to finish configuring the Move Multiple files using File System Task in SSIS package.
Before executing the task, Please change the Delay Validation property of the File system Task to TRUE.
Let’s run and see whether we successfully Moved the 27 files from one location to another using the File System Task or Not.
Well, We successfully Moved 27 files present in the File System Task Folder to the Destination Folder.
Thank You for Visiting Our Blog