SSIS and the File System Task

This post describes an issue I encountered when making my way through using the File System Task for the first time in SSIS.

In this case I needed to import data from a batch of files into the database, then move the files to a processed folder one the import was complete. It took me a good couple of days to get the FST to do as I wanted.

Here’s how I got it going:
• To make things easier for me to comprehend, I firstly created two variables to hold the Source File and Destination paths. Note the Source File is the fully qualified path to the file i.e. it contains the file name, and the Destination path is just the path i.e. it contains no file name. I originally made the mistake of having both the source and destination paths contain the file name and that led to a puzzling error of “Could not find a part of the path.” Much googling later, I figured out the answer myself. Hopefully this post will help you avoid too much googling.

• Using two specific variables meant that I could abstract away the need for an expression in the FST, although in the end it would have worked equally as well, but when debugging, it was simpler to have separated the combining of the part parts away from the use of the paths to move the file.

The two variables were made up of a server part, a root path part, a source/destination folder and the file name. I set the variables up to be based on expresssions meaning that I could check the result of the expression prior to having to use it in the FST. If I had discovered this was possible earlier the job may have been easier to get working.

The errors I encountered in using this task were:

Error	Cause
“Could not find a part of the path.”	The destination path contained the file name. The Destination Connection should represent a path only. No file name is required.

Hopefully this helps someone out there beginning SSIS some angst when they’re using SSIS to move files around their systems, during or post processing.