Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
 Posted - 2007-09-07 : 11:27:35
|
| Hope I can make this clear...I have to download a file via FTP from one of our vendors, the filename is MMDDYYYY.zip, this is a weekly file and is posted between Tuesday and Thursday, so basically I never know exactly what the file is going to be called, to fix that, I added a FTP Task that will download a file if the date = today (Today = Thursday, when the job kicks off), if this fails, try a file where the name = today-1 (Wednesday), if this fails, try today-2 (Tuesday), all of that inside of a Sequence Container and with MaximumErrorCount set to 3. This is fine and it works fine, good.Now my other issue, the .txt file inside the zip file (1080.txt), after being decompressed, needs to be renamed to MMDDYYYY.txt, where MMDDYYY = to the file I downloaded, but I never know what the file is going to named (MMDDYYYY, MMDD-1YYYY, MMDD-2YYYY).Is it possible to output the filename of the file that I successfully download to a variable, so I can re-use name?Any other thoughts? |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-07 : 14:58:49
|
| "Any other thoughts?"Batch file that checks that there is only one file in the "receiving" folder, renames it to "INPUT.TXT" (or somesuch fixed filename) and then MOVEs it to Folder2.SQL process looks in Folder2, processes the file (if its there) and then deletes it (or moves it to some other "Done" Folder3)The presence of a file in Folder2 signifies that a) there is work for SQL to do and 2) SQL has not yet finished doing the work. That can be used as a semaphore to stop other processes placing a new file in Folder2.One variation on this is to rename the Done Folder, keeping the last, say, 10; so something likeRD Folder10 /YREN Folder9 Folder10...REN Folder1 Folder2MD Folder1and run this batch file each time a new file is added, or schedule to run once a day (e.g. where numerous files are accumulated during the day in Folder1, and you would like to purge them after approximately 10 days [in this example])Kristen |
 |
|
|
|
|
|
|
|
Output filename downloaded to a variable