Professional SQL Server™ 2005 Integration Services

3.7. 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 ...