Using The File System Task in SQL Server 2008 Integration Services

SQL Server Integration Services (SSIS) is a Business Intelligence tool which can be used by database developers or administrators to perform extract transform & load (ETL) operations. In my previous article entitled Container Tasks in SQL Server 2008 Integration Services I discussed the different types of container tasks such as For Loop Container, Foreach Loop Container, Task Host and Sequence Container and how they can be used by database developers. In this article I will examine the use of the File System Task which is available in SQL Server 2005 and later versions of Integration Services.

Overview of Data Preparation Tasks in SSIS 2005 & Later Versions

The data preparation tasks within SSIS can be used to retrieve data or to validate the quality of data. The different types of data preparation tasks which are available in SSIS are the File System Task, FTP Task, Web Service Task, XML Task and Data Profiling Task.

Overview of the File System Task

The File System Task can be used to perform different types of operations on files and directories such as create, move, delete, and also to set the attributes for files and folders. The different types of operations which can be performed by the File System tasks are shown in the below snippet.

· Copy Directory can be used to copy all the files from one directory to another. You will need to configure both the source and destination directories when using this operation of the File System task.

· Copy File can be used to copy a file from one location to another location. You will need to configure both the source and destination when using this operation of the File System task.

· Create Directory can be used to create a folder. You will need to specify the source location where the folder needs to be created.

· Delete Directory can be used to delete a folder from a specified location. You will need to specify the source location from where the folder needs to be dropped.

· Delete Directory Content can be used to delete all the files available within the user specified directory.

· Delete File can be used to delete a user specified file from the specified folder.

· Move Directory can be used to move a folder/directory from one location to another location.

· Move File can be used to move a file from one location to another.

· Rename File can be used to rename a file available within a specified location.

· Set Attributes can be used to set attributes to files and folders. Different types of attributes which can be set are hidden, archive, read only and system.

One important point to note is that the File System Task can operate on a single file or a directory at a time, so if you want to perform an operation on multiple tasks you will need to run this task within a Foreach Loop container.

Example – File System Task

We will run through an example using the File System task to copy MDF and LDF files of the Resource Database from the “C:\DDBackups\SystemDatabaseBackups” folder to the“C:\DBBackups\SystemDatabaseBackups\ResourceDB” folder. Once the files are copied we will be setting the attributes of ResourceDB folder to ReadOnly. For more info on the Resource database which is available in SQL Server 2005 and later version you can refer to my previous article entitled Importance of the Resource Database  

1.       Create a new SQL Server Integration Services Project and rename the default package to FileSystemTaskExample.dtsx

2.       Double click FileSystemTaskExample.dtsx package to open it up in Design mode.

3.       Add variables named ResourceDB_MDF_File, ResourceDB_LDF_File and DestinationDirectory, you can open up the Variables window by right clicking within the control flow design window and by choosing the Variables option from the drop down list as shown in the below snippet.

4.       Once the   Variables window opens up, click on Add Variable button and provide the details as shown in the below snippet.

5.       Drag and drop “File System Task” from the toolbox to the Control Flow window and rename it “Copy ResourceDB MDF File”. Then double click the File System Task to open up the editor window as shown in the below snippet.

The File System Task Editor should be configured with the details shown in the below table.