Post reply

FTP (yes another FTP question!)

  • June 24, 2015 at 7:24 am

    #318498

    Hi to all. I want to be able to copy file(s) from an FTP server where specific characters appear in the file name. The file name format is YYYYMMDDHHMMSS_DBBLURBXXKK.txt.

    I am only interested in files with KK before the file extension.

    In my FTP task File Transfer > Remote Parameters > RemotePath I have used /path/????????????????????????KK.txt

    This appears to work OK.

    Question: Is there a better way to do it?

    The file will always exist in the remote path, no deletes.

    Once copied locally and processed it will be moved to an archive folder.

    so copy from:

    FTP

    copy to:

    C:/FTP_Files

    processed and moved from C:/FTP_Files to C:/FTP_Processed

    C:/FTP_Files will then be empty.

    UPDATE:

    I am now looking to delete files in the remote path after copying locally.

    I do not want the FTP task to re-copy processed files to C:/FTP_Files. I'm not sure if I can do this based on what I have said previously! Logic overload....

    Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • June 26, 2015 at 2:00 pm

    #1808528

    I have now implemented a solution. I will post when I find time 🙂

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • June 29, 2015 at 5:26 am

    #1808707

    So the solution for me was to follow this article with the addition of the odd tweak to suit my environment.

    http://sqlage.blogspot.co.uk/2015/05/how-to-download-file-from-ftp-site-and.html

    I tweaked the following:

    SSIS package variable FileName:

    I amended value to *WM.txt as I only wanted to receive files from remote site where the file name matched a specific pattern.

    SSIS package Script Taks:

    I amended code as follows:

    If fileName = Dts.Variables("User::FileName").Value.ToString() Then

    to

    If fileName LIKE Dts.Variables("User::FileName").Value.ToString() Then

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • June 29, 2015 at 6:23 am

    #1808723

    2Tall (6/24/2015)

    Hi to all. I want to be able to copy file(s) from an FTP server where specific characters appear in the file name. The file name format is YYYYMMDDHHMMSS_DBBLURBXXKK.txt.

    I am only interested in files with KK before the file extension.

    In my FTP task File Transfer > Remote Parameters > RemotePath I have used /path/????????????????????????KK.txt

    This appears to work OK.

    Question: Is there a better way to do it?

    The file will always exist in the remote path, no deletes.

    Once copied locally and processed it will be moved to an archive folder.

    so copy from:

    FTP

    copy to:

    C:/FTP_Files

    processed and moved from C:/FTP_Files to C:/FTP_Processed

    C:/FTP_Files will then be empty.

    UPDATE:

    I am now looking to delete files in the remote path after copying locally.

    I do not want the FTP task to re-copy processed files to C:/FTP_Files. I'm not sure if I can do this based on what I have said previously! Logic overload....

    Thanks,

    Phil.

    I can't help you with the SSIS part, but for the FTP command, how about using an mget *KK.txt to copy the files down?

    Then, in DOS, you can delete the downloaded files where they exist in FTP_Processed.

    From there, you're off doing whatever else it is you need to do with them.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • June 29, 2015 at 7:16 am

    #1808748

    Hi Ed. I am all up and running with SSIS now but thanks for your input.

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply