FTP 501 Error Woes with SSIS and AS/400

The other day I was helping Anne (our AS/400 guru) with an SSIS task needed for a data conversion project she’s working on. The process was simple. She’d connect to a file (sort of like a database table) on the AS/400 and download the data to a local text file (space padded). She’d then take this file and upload it as a file on the AS/400 in another location, which would automatically load it up as a table for viewing/modification.

Anne had created a SSIS package for this that was quite simple. One task to download the file over an ODBC connection, and another task to upload the file via FTP to the server again. However, after configuring and running the package, the job kept failing with the following message:

501 Unknown extension in database file name.

Anne had verified the path was correct. The path in question was:

/qsys.lib/trlibobx.lib/ccstx1p.mbr

The way the AS/400 works, if memory serves, is that you specify the file you want to upload into when doing FTP uploads and the server handles the processing of the file into the table accordingly.

After a few proverbial beatings of the head against a wall ni search of details, we donned our Googlian Monk robes and did some searching. Interestingly enough, the FTP task within SSIS doesn’t function the same way. We never pulled the full debug log to figure out why, but what we did discover was two key points:

  1. Contrary to typical file uploads, when using the FTP task, you DO NOT specify the file that you upload to in your path.
  2. When doing uploads in this fashion, the AS/400 server will only allow files with a .file in their extension.

With this info, and a few more tweaks to the task, we finally got things to work. Instead of downloading our data into a .txt file, we downloaded it directly into the filename we needed:

Secondly, in the FTP task, we specifed only the path to the location that we were to upload to. We could even overwrite an existing file if it was already there on the server.

Success! There were a total of three files that needed to go through this routine, and the rest of them took on the magnitude of minutes to accomplish, as opposed to a good 2 hours for the first one. Enjoy!

4 thoughts on “FTP 501 Error Woes with SSIS and AS/400

  1. This is great and i thank you. now the ftp task works fine, though only that now using extension ” .file ” is giving gibberish data when i select table or view it in iseries , fyi i use the same file with ” .prn” extension with command prompt and works fine.

  2. Hi dilli-o… have you try get file from AS/400.. I’m having trouble to get flat file from the AS/400.. keep getting message that the file does’nt exists. I put the remote path to something like this –> /QSYS.LIB/u38000XXX.lib/detail1.FILE, and kept having this as a reponse
    [FTP Task] Error: File represented by “/QSYS.LIB/u38000XXX.lib/detail1.FILE” does not exist.

    1. I’m not sure about that one, can you verify that the file exists in the location specified? Another issue could be the path. Typically on the SSIS side of things file paths need a double slash // to parse properly. That might be worth checking as well.

What are your 10 bits on the matter? I want to know!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s