Few days back I got a question from one of the SQL DBA’s who was working on a SSIS project. The task was simple but the error was a little tricky to understand and so was the solution (easy one, but worth jotting)
The user is trying to execute an SSIS package but it fails on the FTP task with the following error:
[Connection manager "FTP Connection Manager"] Error: An error occurred in the requested FTP operation. Detailed error description: The filename, directory name, or volume label syntax is incorrect. .
Here is the detailed package flow and logic
The SSIS Package had the following logic flow:
1. The First Step: Data is copied into Source.csv file.
2. The file Content is then copied to source OLEDB data connection – SQL Server
3. The data is then further copied from the source to the destination
4. In destination there is a rename operation which is performed
5. Renamed file is then copied to FTP
Data Flow Diagram
1 Process data & dump into csv file
2 File operation:- Copy Template file from Source to destination
3. File operation :- Rename the destination file to required format.
4. FTP:- send operation in FTP
delete destination file.
FTP Task Editor=> IsLocalPathVarible:- True
Local Varible :- User::Final
Operation :Send Files
ISTransferASCII :- False
REmote Path :- \Location
OverwriteFileAtDest :- YES
Final:-D:\test20130724_230456_662000000.xls which has the following expression :
Fix the final and Rename Variables with correct values. Include the \ after C: to fix the problem. As you know that if you try to write C:ProgramFiles, you won’t be able to get to program files, so we need to fix the expression and evaluated expression for User::Final to get this working. Once that is fixed you will be able to get the package working.
See the User variable Destination, User::Destination – that has a correct value C:\Destination. But not the Final and Rename variables, you will have to fix them both and that should be really easy.
If you see the error: [Connection manager "FTP Connection Manager"] Error: An error occurred in the requested FTP operation. Detailed error description: The filename, directory name, or volume label syntax is incorrect. .
1. When I go to FTP Task and check for the File Transfer Task. I see that its using a User variable called Final: and if you see the Value for the Final is C:Destination, it is missing ‘\’
2. To confirm I went into the expression and I tried to evaluate it and found the following, see the screenshot. If you see the evaluated string says C:Destination\OneSource….., we are missing the ‘\’ after C:
This did solve some part of the issue, but not the complete problem. Further to it the error still popped up and this time I gave it a closer look.
I saw this further and if you see closely, somehow there is a space between the “ and C:\\, remove that space in the expression builder.
" C:\\Destination\\OnesourceReport"+(DT_WSTR, 20) (DT_DBDATE) GETDATE() +".csv"