Recently I was working on a project to extract data from CSV files for a client. The package was pretty standard and it ran fine under my account in BIDS. The issue occurred when I tried to deploy and executed under the service account. The error message “Description: The file name property is not valid. The file name is a device or contains invalid characters” was not too helpful, but it did lead me in the right direction.
After asking around I found that another developer had a similar issue and pointed me to a couple of blog posts:
http://www.sqlservercentral.com/Forums/Topic1128829-148-2.aspx
It turned out that the service account had access to the directory that contained the CSV files, but not the entire path.
\\Server\Directory 1\Directory 2\CSV Files\My File.csv
Once the account was granted access to ‘Directory 1’ and ‘Directory 2’ the issue was resolved. Hopefully this post will save someone some time if they run into this.