[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. .

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)

Problem:

The user is trying to execute an SSIS package but it fails on the FTP task with the following error:

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.

 

Details

FTP Task Editor=> IsLocalPathVarible:- True

Local Varible :- User::Final

Operation :Send Files

ISTransferASCII :- False

Remote Parameter

IsRemotePathVarible :-False

REmote Path :- \Location

OverwriteFileAtDest :- YES

Final:-D:\test20130724_230456_662000000.xls which has the following expression :

"D:\\test"+Replace(Replace(Replace(Replace((DT_WSTR,50)(getdate()),"-","")," ","_"),":",""),".","_")+".xls"

Resolution:

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.

My Analysis

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 isC: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"

Issue Resolved!!