Using the File System and FTP Task

You should pause again and try out a few of the tasks. In this example, you're going to try to FTP a file from a university and then copy the file to an archive folder. Create a new directory if it doesn't exist yet called C:\SSISDemos\Archive. Then, create a new package in your Chapter3 SSIS project. Change the name of the package to FileCopy.dtsx.

Drag over the FTP task from the Toolbox to the design pane. Double-click on FTP task to open the editor, as shown in Figure 3-11. Name the task "FTP a File" and type any description. For the FTPConnection drop-down box, select <New connection…>. This will open the FTP Connection Editor. For the Server Name option, type ftp.microsoft.com and click Test Connection. Click OK to go back to the FTP Task Editor.

Figure 3-11

Next, go to the File Transfer page and type /bussys/readme.txt for the remote path. You can also click the ellipsis button to point to the file. For the Local Path option, select <New connection…> to open the File Connection Manager Editor. Select Existing Folder for the Usage Type and type C:\SSISDemos for the Folder.

The Operation drop-down box should be set to Receive Files. For the OverwriteFileAtDest option, select True. Click OK to exit the editor (shown in Figure 3-12).

Figure 3-12

Now that the file will be downloaded, go ahead and archive it. In a real-world scenario, chances are you'd download the file, load it into a SQL Server, and then archive it, but you'll see that complete example in Chapter 5's tutorial. To copy the file into an archive directory, you'll use the File System task. Drag it onto the design pane and double-click on it to open the editor.

For the DestinationConnection option in the General page, select <New connection…>. Once the File Connection Manager Editor opens, select the usage type of Existing Folder and type C:\SSISDemos\ Archive for the folder name. Click OK to go back to the General page. Change the OverwriteDestination option to True.

For the Name option, type Archive File and type whatever description you'd like. The Operation option should be set to Copy File. Select True for the IsSourcePathVariable option and select <New variable…> for the SourceVariable drop-down box. This will open the Add Variable dialog box. The name of your variable will be SourceFile, and the value will be set to C:\SSISDemos\Readme.txt as shown in Figure 3-13.

Figure 3-13

Click OK to go back to the General page. The SourceFile variable will be transposed into the SourceVariable drop-down box. The path to your destination file will be pulled dynamically from this variable. The reason you may wish to do this is to point to a file that does not exist yet. Your configuration of the task is now complete (shown in Figure 3-14). Click OK to go back to the design pane.

Figure 3-14

Back in the Control Flow tab, connect the FTP file task to the File System task with a precedence constraint. This way, the file will be FTPed first and then copied to the archive folder. Execute the package, and the final results should look something like Figure 3-15. Click Stop after the package has successfully executed and save the package. You should be able to see a Readme.txt file in the C:\SSISDemos\ directory and in the C:\SSISDemos\Archive directory now.

Figure 3-15