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 article present in the SSIS page.
We have File System Task Folder inside the Documents drive. The below screenshot shows you the data inside that folder. Our task is to move the 27 files inside the SSIS File System Task Folder to Destination Folder inside E Drive.
Move Multiple files using File System Task in SSIS
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 see we are choosing the FILE SYSTEM TASK folder present in our E Drive. Click the 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. To do that, go to Variable Mappings section and select the user variable (if any) or create a new variable
Once you click on the <New Variable..>, the Add Variable window will open to create a new variable. In this example, we require variables to store the File name and its extension, for instance, SALES1.xls. So, we are creating a string variable and assigned the name as Variable. Click OK to finish creating a string variable.
Next, select the created variable and assign it to index 0. Once you completed, 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.
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 the Existing Folder option from the Usage Type. Next, click on the Browse button to select the Existing Folder from the file system. As you see from the below screenshot, we chose 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 SSIS File System Task or Not.
Well, We successfully Moved 27 files present in the File System Task Folder to the Destination Folder.
