SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


FTP Task to import files based on last processed file variable


FTP Task to import files based on last processed file variable

Author
Message
Aparna-1
Aparna-1
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 601
Hi All,

I have to import files into adatabase from the data provider through FTP. Each day the vendor provides 3,000 files which I need to import. The idea is the package will run every 30 minutes and I should grab files greater than last processed id during each run

ex:

20100217 is the folder which contains upto 3000 files
ex 1.txt, 2.txt, 3.txt, 4.txt

if in my previous run i have processed files 1,2,3 I should copy files only from 4.txt.
I see that the FTP Task has the option to define remote path as variable but how can i achieve the >lastprocessed option

may be i can save the ftp variable as
/20100217/*.txt but it will fetch all the files and each time i do not want to get 3000 files and then discard already processed ones. Is there an efficient way of doing this.

If FTP task has limitations then probably i will have to write a batch file that might do the same and I have to explore the possibilities

Your help in this regard is appreciated.
Phil Parkin
Phil Parkin
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35739 Visits: 20891
It is common practice to move a file to an archive folder of some sort after processing it - perhaps adding 'processed date' to the file name in the process.

If you adopt this process, your requirement changes to become: process and move all available files. A FOREACH container will help you do this quite easily.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Aparna-1
Aparna-1
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 601
yes I process the files and move it to an archive directory once I get the files in my working directory and process them.

The problem was with i should be ftping into my working directory ( i cannot move files from FTP I am allowed to copy only). On a given day there are allways files from 1- 3000 files (no particular order of file names and are very huge sometimes) and if i have processed 1-50 files i did not want to copy files from 1-3000 all i want is 51-3000.

Any body who might run into this problem. Here is what I have done. There may be better ways of doing this and I would like to hear others suggestions.

I got a directory listing of ftp through a script task. similar to dir command in FTP (say it listed 1-150 files at a given instance).
I Saved the filenames in an array and imported into a table. I matched with what I have already processed and looped through only unprocessed files for the next run and copied the files(51-150).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search