File System Task

Consider a typical ETL process from a mainframe system. Typically when you want to automate a data load of a system nightly, the process would look like this:

• You receive a file from a mainframe or source system via FTP.

• Your package would see the file in the directory and react by moving it to another working directory.

• The data would then be extracted out of the file and the file would be archived to another directory.

Previously in SQL Server 2000, each of the preceding steps would have taken lots of scripting by using the ActiveX Script task. You would have had to write one task in VBScript to poll the directory to see if the file had arrived. Another script would see the file and move it to another directory. The last script would archive the file. Typically this would take dozens, if not hundreds, of lines of code once you write all the error checking, and it would be a nightmare to update.

In SQL Server 2005, this process is much simpler with the addition of the File System task. This task allows you to perform file operations such as copying, moving, renaming, and deleting a file. It also can perform directory functions such as creating, deleting, copying, moving, or renaming a directory. It can also set operating system attributes and delete all the contents in a directory.

Most of the properties in this task are set in the General page of the File System Task Editor, which is shown in Figure 3-8. The contents of this page may vary widely based on what you set in the Operation option. This option specifies what you'd like the task to accomplish. Your options are all the previously mentioned items that can perform file, directory, and SET operations. Once you set the option, the page dynamically molds itself to that operation. For example, if you select Delete Files, there's no need for the DestinationPath options.

Figure 3-8

The IsDestinationPathVariable option allows you to specify whether the destination path will be set to a SSIS variable. If this is set to true, the dynamic property DestinationVariable sets the destination path to a variable. If it's set to false, then the DestinationConnection option will be available for you to select the Connection Manager that contains your file or directory. These same properties exist for the source connection in the bottom of the page. The OverwriteDestination option is set to false by default and specifies whether the task will overwrite the destination file or directory if it already exists. You'll be using this task a lot in Chapter 5's tutorials.