Down loading file from Ftp site

  • For our web I am having to down load a file off a ftp site.

    The file name can change from RORD0001, RORD0002 e.t.c

    I need to down load the file and then delete off the ftp site.

    Down loading the file is not a problem but the issue I have is to delete just the one file. For example if I was downloading RORD* then deleting RORD* it could delete files I have not yet download if they we dropped onto the site whilst the others were downloading. Therefore I need to take the name of the file I have down loaded and delete that one file.

    Once the file has been deleted eg RORD0001 then they drop another file called RORD001

    There is probably a better way of doing it

    Sorry it is so muddly and confusing. If you are not clear on something please say.

    Glad of any help

  • Try SSIS. You will find all necessary tasks there you might need to perform on your files.

  • Is this using the Export and Import Tool?

  • IIRC, Export Import tool doesn't support FTP. You need to create a new SSIS package in BIDS.

  • Thanks. I have been having a look at it. Never realised there was this tool.

    Do you have an example of what you have done similar to what I am trying to do.

    What sequence fo tasks would you set up

  • SSIS Tutorial: Creating a Simple ETL Package

    http://msdn.microsoft.com/en-us/library/ms169917.aspx

  • kyle.doouss (12/5/2011)


    Thanks. I have been having a look at it. Never realised there was this tool.

    Do you have an example of what you have done similar to what I am trying to do.

    What sequence fo tasks would you set up

    You are looking on FTP task here is the steps to follow:

    Open BIDS then

    1. Drag Data Flow Task on the Control Flow tab.

    2. Double click on the Data Flow Task.

    3. Drag Ole Db Source on the Data Flow tab.

    4. Double click on it.

    5. Click on New button and add connection to database table.

    6. Click on Columns list box item and select columns from the table.

    7. Click on OK button.

    8. Drag Flat File Destination on the Data Flow tab.

    9. Drag green arrow from it on the Flat File Destination component.

    10. Double click on it.

    11. Click on new button and select destination file.

    12. Click on mappings list box item and select column mappings between database columns and file columns.

    13. Click on Control Flow tab.

    14. Drag FTP task on it.

    15. Drag green arrow from Data Flow Task and place on FTP task.

    16. Double click on FTP task and click on FTP connection and enter valid data for it

    17. Click on File transfer list box item and enter remote and local paths.

    15. press F5 and check if file is on the FTP site.

    After that you can set u credentials and connetion...

    _______________________________________________________________

    Need help? Help us help you.

  • Thanks. I will get started!!

  • WC...If u face any issues post it 🙂

    _______________________________________________________________

    Need help? Help us help you.

  • I faced now issues regards exporting a view to a flat file and sending it to the FTP site. Thanks for your clear instructions it worked first time!!

    I currently have set this up as stored procedures which is working.

    The issue I have is downloading from the ftp site as mention on my first post.

    Do you have any ideas how I will cope with this? Glad of any help. Please ask questions if you don't understand what I am trying to do.

  • kyle.doouss (12/6/2011)


    I faced now issues regards exporting a view to a flat file and sending it to the FTP site. Thanks for your clear instructions it worked first time!!

    I currently have set this up as stored procedures which is working.

    The issue I have is downloading from the ftp site as mention on my first post.

    Do you have any ideas how I will cope with this? Glad of any help. Please ask questions if you don't understand what I am trying to do.

    If u dnt mine...Canu post an original or sample packge wt ur xactly trying to do 🙂

    _______________________________________________________________

    Need help? Help us help you.

  • Sorry I have not created a package yet.

    Below is what I am trying to do.

    1. The website sends a file to the ftp site (say tab delimited) This is name RORD0001, then RORD0002 for the next order.

    2. I need to then down load that file to a folder on our server

    3. Upload the file in a API table

    4. Delete the file off the ftp site

    Once the file RORD0001 has been deleted then the website will replace it with another file called RORD001.

    If you have any questions please ask

  • kyle.doouss (12/6/2011)


    Sorry I have not created a package yet.

    Below is what I am trying to do.

    1. The website sends a file to the ftp site (say tab delimited) This is name RORD0001, then RORD0002 for the next order.

    2. I need to then down load that file to a folder on our server

    3. Upload the file in a API table

    4. Delete the file off the ftp site

    Once the file RORD0001 has been deleted then the website will replace it with another file called RORD001.

    If you have any questions please ask

    Sry for late rply...If you want to delete that file(if exists in loaction) you can use the WMI Event watcher task to know more go through the below link:

    http://msdn.microsoft.com/en-us/library/ms141130.aspx

    I hope this will help you

    _______________________________________________________________

    Need help? Help us help you.

  • I feel the database server should be left to do the core task of processing SQL and serving data to clients. Other activities wherever possible should be moved off to another box. SSIS surely does a great job of performing a variety of tasks but in future as load increases on your database server it can cause a performance problem.

    In this case I would write a PowerShell script that does two activities:

    1. Download the files from FTP and delete them

    2. Use BCP (part of client tools) to load the file into the database

    You can schedule this PowerShell script using Windows Task Scheduler or any enterprise scheduling software if you already have it in your environment.

    Since PowerShell is a much more expressive programming language there is a lot more custom logic you can put in. You can take a call based on how big/loaded your environment is.

  • Thanks for the guidance.

    How would you use the Bcp to put specific fields into specific fields in the API table?

    Does it work with XML documents?

Viewing 15 posts - 1 through 15 (of 15 total)

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