SSIS - Working with the File System Task

In this blog post, we will look into the SQL Server Integration Services (SSIS) feature provided by the File System task, which is available in the control flow tab as shown below:

As the name suggests, this task is used to perform operations on files in the system(computer). With this task we can move, delete folders and files. We can also use it to specify the attributes of files.

The following activities can be performed with the help of File System Task:

• Copy directory: To copy all the files from a folder to another directory. In this case we need to configure the source connection and destination folders.
• Copy File: To copy a single file from one location to another.
• Create Directory: To create a folder, you need to specify the source connection where the folder is to be created (You need to have NTFS rights for creation, if not rights run time error is thrown)
• Delete Directory: To delete the specified folder, you need to specify source connection. (you need NTFS rights for deletion)
• Delete Directory Content: To delete all entries in a folder
• Delete File: To delete the specified file from the specified location
• Move Directory: To move complete folder from one location to another
• Move file: To move the specified file from the specified location to new one.
• Rename file: To rename the specified file
• Set Attributes: to set the attributes for files as well as folders, which can be hidden, readonly, achieve and system.

Let us take a small example where a variable stores the information about the destination folder.

1. Create a new Integration Services Project > rename the default package to FileSystemDemo.dtsx
2. Create 2 variables with package scope namely ‘SourceConn’ and ‘DestFolder’ of string type > specify the value as the required folder names.
   Note: To create a variable, select variables from the SSIS menu > select Add variable (ensure that no task is selected so that the scope of the variable is for complete package)
3. Drag and drop the File System Task control on to the designer
4. Configure the File System Task in following manner
   
5. You will have to be careful while giving the source and connection values as variables, as the required folder and files must be existing. Otherwise we will end up in getting a run time error.
6. To execute the package from Business Intelligence Development Studio (BIDS) use F5.