none
Cannot FTP text file to as400 via FTP task in SSIS

    Question

  • Hi,

    I have been struggling for the past day or so trying to get a text file to FTP to our as400 box.

    The FTP task is the last part of a larger SSIS package I have developed and it's driving me insane that I can't get such a simple part to work. Basically if any of the file creation package or the FTP task fails, an SMS text message will send to users telling them there has been a problem with the package.

    Anyway to the FTP task...

    I test my connection fine and I have configured other settings as follows:

    the remote path is not a variable but should be in a library on the as400 called cfststfile. The login name I am using for the as400 should be hard coded to go to this cfststfile location only. I can use this login and password fine when using an external FTP client such as Ipswitch FTP Pro.

    Remote Path: /cfststfile

    The local path will probably be a variable eventually once I get the hard coded path to work.

    It's to send files and they have to be ASCII.

    Every time I try this it fails after 30 secs saying "unable to send files via FTP Connection Manager"!!!!!!!

    I would have thought this would have been dead easy. I have checked with network guys across from me who have said I ahve domain admin rights to go anywhere on the network anyway and no firewalls are blocking me.

    Does anybody out there have any ideas?? I have reasonably good skills on SSIS and I just can't believe that what I thought would be an easy part of my SSIS package is taking so long. Not only that but I must have been through every blog on SSIS and ftp on the net and learned more about FTP than I ever thought I could ever possibly know.

    If anyone has some gems of knowledge then I will be eternally grateful as even our as400 supplier (we are on a managed service for our as400 box) could not come up with s solution.

    Yours in waiting.

    Graeme

    Update to the problem,

    Internally we had some firewall rules which was stopping FTP from command prompt on my local machine. Once those rules were removed I could FTP via command line but FTP task still does not work.

    It seems that with command line you open up port 21 on the firewall and then transmit the data to the as400.

    With SSIS FTP task it opens up a port and then fires the file straight away, but we get nothing back from the as400. I can get round the problem by using an execute process task using a .bat file to FTP but it's very annoying that the FTP task won't work.

    Does anyone know if it is simply because SSIS FTP task does not like as400/I-series.

    Tuesday, September 29, 2009 4:45 PM

Answers

  • Problem fixed!! I needed to specify exactly the file path to the as400 with an extension on the flat file of .file.

    So I had to specify /qsys.lib/cfststfile.lib/

    and then send a file of filename.file

    I belive this is due to how the as400 deals with file extensions and linking into SSIS. With dos it manages to cope without specify file extensions but using the SSIS ftp task you need to specify the file as a .file
    • Marked as answer by Gmuc79 Wednesday, September 30, 2009 1:13 PM
    Wednesday, September 30, 2009 1:13 PM

All replies

  • Have you tried passive mode?
    Todd McDermid's Blog
    Tuesday, September 29, 2009 4:46 PM
    Moderator
  • please post the ssis log messages.

    Duane Douglas, MCAD, MCITP | http://www.ssisbi.com | Please mark the post(s) that answered your question.

    SSIS Business Intelligence

    Wednesday, September 30, 2009 5:40 AM
    Moderator
  • SSIS package "Feed File Transfer.dtsx" starting.

    Error: 0xC001602A at Feed File Transfer, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: 200 Representation type is ASCII nonprint.

    227 Entering Passive Mode (195,79,78,10,77,167).

    501 Unknown extension in database file name.

    .

    Error: 0xC002918B at FTP Task, FTP Task: Unable to send files using "FTP Connection Manager".

    Task failed: FTP Task

    Warning: 0x80019002 at Feed File Transfer: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    SSIS package "Feed File Transfer.dtsx" finished: Failure.

    Wednesday, September 30, 2009 9:55 AM
  • SSIS package "Feed File Transfer.dtsx" starting.

    Error: 0xC001602A at Feed File Transfer, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: 200 Representation type is ASCII nonprint.

    227 Entering Passive Mode (195,79,78,10,77,167).

    501 Unknown extension in database file name.

    .

    Error: 0xC002918B at FTP Task, FTP Task: Unable to send files using "FTP Connection Manager".

    Task failed: FTP Task

    Warning: 0x80019002 at Feed File Transfer: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    SSIS package "Feed File Transfer.dtsx" finished: Failure.

    Wednesday, September 30, 2009 9:56 AM
  • Yes tried passive mode but no luck. I have posted the output.
    Wednesday, September 30, 2009 10:07 AM
  • Problem fixed!! I needed to specify exactly the file path to the as400 with an extension on the flat file of .file.

    So I had to specify /qsys.lib/cfststfile.lib/

    and then send a file of filename.file

    I belive this is due to how the as400 deals with file extensions and linking into SSIS. With dos it manages to cope without specify file extensions but using the SSIS ftp task you need to specify the file as a .file
    • Marked as answer by Gmuc79 Wednesday, September 30, 2009 1:13 PM
    Wednesday, September 30, 2009 1:13 PM