SSIS File System Task
The File System Task is used to manipulate operations on files and directories on a server. For example, you can create the task to create, copy, delete or move a file or a directory. All the operations in the task use a source and some of them, such as copy and move, need a destination as well. Either a source or a destination stands for a file or a directory and can be specified by using a File connection manager or a variable.
Totally 10 operations can be defined in a File System Task and are described in the following table.
|Copy directory||Copy a source folder to a destination folder.||IsDestinationPathVariable, DestinationConnection, DestinationVariable, OverwriteDestination|
|Copy file||Copy a source file to a destination.||IsDestinationPathVariable, DestinationConnection, DestinationVariable, OverwriteDestination|
|Create directory||Create a source directory.||UseDirectoryIfExists|
|Delete directory||Delete a source directory.|
|Delete directory content||Delete all in a source directory.|
|Delete file||Delete a source file.|
|Move directory||Move a source folder to a destination folder.||IsDestinationPathVariable, DestinationConnection, DestinationVariable, OverwriteDestination|
|Move file||Move a source file to a destination.||IsDestinationPathVariable, DestinationConnection, DestinationVariable, OverwriteDestination|
|Rename file||Rename a source file.||IsDestinationPathVariable, DestinationConnection, DestinationVariable, OverwriteDestination|
|Set attributes||Sets attributes on a file or a folder.||Hidden, ReadOnly, Archive, System|
- All the operations contain Name, Description, IsSourcePathVariable, SourceConnection and SourceVariable common properties.
- Delete directory content operation deletes all the sub folders and files in a folder but not include the folder itself.
- Delete directory operation deletes a folder including all the sub folders and files in the folder.
- There is no "Rename directory" operation but "Move directory" can be used to rename a directory.
The following is the list of all the properties in a File System Task.
|Name||The name of the task.||File System Task|
|Descripton||The description of the task.||File System Task|
|Operation||The operation of the task.||Copy file|
|IsSourcePathVariable||True if the source uses a variable otherwise uses a connection.||False|
|SourceConnection||A source connetion manager which points to a file or a foler.|
|SourceVariable||A variable which contains a source file or directory.|
|IsDestinationPathVariable||True if the destination uses a variable otherwise uses a connection.||False|
|DestinationConnection||A destination connetion manager which points to a file or a foler.|
|DestinationVariable||A variable which contains a destination file or directory.|
|OverwriteDestination||True if the destination can be overwritten.||False|
|Hidden||True if the file is hidden.||False|
|ReadOnly||True if the file is read-only.||False|
|Archive||True if the file is archivable.||False|
|System||True if the file is a system file.||False|
|Expressions||Replace a property with the result of an expression.|
Before starting to create a sample package, you need to create a TXT file test.txt in the root of C drive. In the sample package, we'll create a folder, move the file text.txt to the folder and at last make the file read-only. Let's do it step by step as follows.
- Create a new package in LearnSSIS1 project and rename it to "FileSystemTask.dtsx". Then open the package if it is closed.
- Drag and drop the File System Task 3 times from SSIS toolbox to the control flow of the package and link them as shown below.
- Right click the first task and click "Edit..." to open File System Task Editor. Then change the Name to "Create Folder" and Description to "Create a folder from a variable.".
- Change the Operation to "Create directory", IsSourcePathVariable to "True" and click
in SourceVariable field.
- The Add Variable window will be opened and change Name to "Folder" and the Value to "C:\SSIS".
- Click "OK" to create the variable Folder and click "OK" again to close the File System Task Editor window.
- Open the editor of the second task as we did in step 3. Then change the Name to "Move File" and Description to "Move a file to the new folder."
- Change the Operation to "Move file", IsDestinationPathVariable to "True" and DestinationVariable to the variable "User::Folder".
- Click the drop-down list in SourceConnection and choose
to open File Connection Manager Editor. Then click "Browse..." button to choose the test.txt file in the C drive then click "Open" button.
- Click "OK" to close the File Connection Manager Editor. You can see a connection manager "test.txt" is added in the Connection Managers area. The settings of the editor looks like below.
- Click "OK" to finish the setting on the second task. We'll set read-only attribute to the file in the last task. But the folder and file have not been created so a new variable to hold the file in the new folder is needed.
- Create a new variable File with string type and set its expression as @[User::Folder] + "\\test.txt".
- Right click the last task and open the File System Task Editor again. Change the setting as the following.
- Click "OK" to close the editor. Then click the "Save" icon to save the whole package.
- Run the package you will see it runs successfully. Then click "Stop Debugging".
- At last, check the attribute of the file test.txt in the folder C:\SSIS by right clicking the file and choosing properties.
You can see the read-only attribute is checked.