File System Task

Additional information

License

SQL Server Version

, , , , ,

Vendor

Description

The File System Task is used to run operations on files and folders on a server or a local machine. If you need to create, copy, delete, move, or rename a file or a directory, you can do that with the help of a File System Task.

How does it work and what operations and properties are available with this task?

All the operations in the task require a source, and some of them need a destination as well, these are copy, move and other. Both sources and destinations are files or directories, and these are specified using a file connection manager, or a variable, which stores a path to a file or a directory.

There are 10 operations available in the File System Task, which come in this particular order:

–  Copy directory – copies a source folder to a destination;
–  Copy file – copies a source file to a destination;
–  Create directory – creates a source directory;
–  Delete directory – deletes a source directory;
–  Delete directory content – deletes the entire content of a source directory;
–  Delete file – deletes a source file;
–  Move directory – moves a source folder to a destination folder;
–  Move file – moves a source file to a destination;
–  Rename file – renames a source file;
–  Set attributes – sets attributes for a file or a folder.

Hints!

“Delete directory content” operation deletes all the sub folders and file in a source folder but does not deletes the folder itself. To remove a source folder, use the “Delete directory” operation.

A “Rename directory” operation does not exist, however, the “Move directory” can be used for this purpose.

Example - how the File System Task can be used.

Let’s say we want to Create a Folder, move a file into the Folder, and Rename the file after it was moved.

Step 1

–  Find the File System Task in the SSIS Toolbox and drag and drop it in the Control Flow area.
–  Double click the task to open the File System Task Editor;
–  By default, the “General” tab is selected, which is tab where the majority of the properties are;
–  First, find the Name and set the name of the Task to “Create a Folder” or anything that would suggest the operation this task is going to run;
–  Set the operation property to “Create Directory”;
–  Now, find the IsSourcePathVariable property and change it to “True”;
–  The property right under it, will change from SourceConnection to SourceVariable;
–  Click to select a SourceVariable and select the “New Variable” option;
–  Under Container click and select the File System Task, by expanding the folders;
–  Set the name to “NewFolder”;
–  Namespace and Value type can stay unchanged;
–  Set the Value to the full path to the folder to be created (C:\ FolderName) and click OK;
Click OK one more time to close the System Task Editor window.

 

This is how the Folder Creation File System Task configuration should look like

Step 2

–  Drag and drop a second File System Task to the Control Flow area, under the previously created task;
–  Double click the task to open the File System Task Editor;
–  Change the Name of the Task to “Move File” or anything that would suggest the operation this task is going to run;
–  Set the operation property to “Move file”;
–  Then set the isDestinationPathVariable to “True”, and the DestinationVariable to the “User::NewFolder” variable, from the dropdown;
–  Find the SourceConnection property on the very bottom, click on its dropdown and select a new connection;
–  Leave the Usage Type to “Existing file”, click on Browse, find the file you want to move (it has to be existing on your machine already), select it and click Open;
Click OK to close the File System Task Editor for the second task. 

This is how the Move File System Task configuration should look like

Step 3

We will now need a variable to store the path to the moved file, which now should be in a new directory, according to our flow.

Open the Variables menu and create a new variable called “FilePath”, a string data type, using the following expression @[User::NewFolder] + “\\test.txt”, text.txt being the file I want to move. In your case it could be different, so please adjust the expression accordingly.

The two variables that we are using in this example

Step 4

–  Drag and drop a third File System Task to the Control Flow area, under the previously created task;
–  Double click the task to open the File System Task Editor;
–  Set the operation property to “Rename file”;
–  Now, set the IsSourcePathVariable property to “True” and under SourceVariable select the “FilePath” variable that we’ve created earlier;
–  Under the DestinationConnection property, select the “New Connection”;
–  Once the File Connection Manager Editor opens, set the Usage type to “Create file”;
–  Now in the File input box, write down the path to the folder that will be created, in our case it’s “FolderName” and new name for the file we want to rename. 
–  It should look like this: C:\FolderName\RenamedTest.txt, then click OK to close the Editor;
Click OK again to close the File System Task Editor.

This is how the Rename File System Task configuration should look like

Run the package!