July 20, 2011 at 11:55 am
SSIS 2008 R2 pkg failing when referencing a Connection Manager data source specifying a PATH\FILE name where the PATH has spaces in one of the subdirectory names/ Actiual error returned in SSIS execution:
"myFile.mdb" Description: The file name "\\MyServer\share$\subDir1\sub Dir2\subDir3\myFile.mdb" specified in the connection was not valid. End Error Error: 2011-07-20 11:24:23.74 Code: 0xC0202070 Source: MyFile Connection manager "myFile.mdb" Description: The file name property is not valid. The file name is a device or contains invalid characters. End Error
eg. \\MyServer\share$\subDir1\sub Dir2\subDir3\myFile.mdb
Is there an escape character I can use in the Connection Manager properties for this PATH name?
this worked fine in our SQL 2005 environment.. Not working in SQL 2008 R2 environment.
July 25, 2011 at 9:18 am
By coincidence I am getting the same error on 2005. The server share I was getting a text file from has been migrated to a new server and now it's not working.
I can see the directory and file using explorer but when you try and load it via SSIS I get the error 0xC0202070, "The file name property is not valid. The file name is a device or contains invalid characters."
From what searching I've done on the web I suspect that it is a permissions problem - your problem might be too?
July 25, 2011 at 10:33 am
The space in the path directory name is fine. I had a very similar issue back in Aug 2009 (found my own post when I googled this issue -- http://www.sqlservercentral.com/Forums/Topic773784-148-1.aspx#bm775154 )
This was absolutely a permissions issue on both occasions. Check your permissions to the Path and/or share.. and beware that inherited permissions in a lower level path also need to have permissions.
March 27, 2012 at 9:42 am
I was having a very similar problem importing csv files into SQL 2008 R2 through SSIS from a fileshare accessed by a UNC path.
The package worked perfectly from BIDS and also after deploying it and executing it manually from the Stored Packages in SQL, but when a SQL Agent job kicked it off, I was also getting the following error:
<filename> specified in the connection was not valid.
The file name property is not valid.
The file name is a device or contains invalid characters.
Because it worked when run from all the other locations, the error makes you believe it's a SQL Agent user permissions problem.
I tried making sure the job step runs as the SQL Agent service account and tried enabling the Proxy Account, tried giving Everyone full access to the share, but nothing worked.
It turns out the solution was simple ... the space in the UNC file path was the problem when run from SQL Agent. When I created a new share (with no spaces), it worked just fine, without the Proxy Account enabled or any other elevated file permissions.
Hope that helps.
March 28, 2012 at 1:17 am
just to throw this one into the mix as well. are you generating these file paths by the use of expressions? if so you need to do a double slash not a single slash to get around the escape characters
\\\\SERVER\\D$\\Backups\
just my 2p's worth, might not be relivent to this issue
March 27, 2014 at 6:53 am
Hi ,
I am getting below error in SSIS 2008 package when it is run through control-m
The file name is not valid. The file name is a device or contains invalid characters.
component "FF_SRC_InboundFidelityInputFile" (1) failed the pre-execute phase and returned error code 0xC020207E.
although this package is working fine in dev environment from visual studio.
control-m agent has proper permissions to the folder still it is failing. Please if anyone knows solution for this issue. I am working on this issue since more than a week but reached nowhere. Please help.
Thanks Deepti
June 17, 2016 at 8:19 am
My fix was to set the SSIS property DelayValidation to true on Tasks and Connection Managers. I was getting pre-validation errors on file names on a network share. It was a timing issue. It was trying to validate the files before the connection to the share was finished. The symptoms I experienced were failure when the SSIS package ran via the SQL Server Job Agent (e.g., a scheduled job). However, the package ran successfully from Visual Studio (my personal user account), and from the SSIS Catalog in SQL Server Management Studio under the same service account as used when running under the Job Agent. So, permissions were ruled out. Since it was a timing issue, it would succeed at times, but fail at others. Once I set the DelayValidation to true on the errant objects, it consistently ran from the Job Agent.
May 19, 2020 at 6:11 pm
I landed here researching the same error as mentioned by some users above "The file name is not valid. The file name is a device or contains invalid characters"
It was an access issue although it was a bit confusing in my case since it was not obvious permission error and we were able to read/write files manually but not through dtexec.exe.
We had a directory structure with several folders and sub-folders. e.g. \\Server\Sharefolder\mainfolder\sub-folder\....
We had permissions at subfolder level only and not even read at mainfolder level. Our service account showed being able to read/write files successfully while using the absolute path.
The dtexec command had one variable with value of a file inside the share directory structure (e.g. "\\Server\Sharefolder\mainfolder\sub-folder\file.csv")
Apparently it never appeared to be a permission issue until I went through several forums talking about permission issue.
When we granted read privilege at the mainfolder level, after that the SSIS package started working. So just posting lesson learned for anyone who comes here in future.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply