Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 get latest file from ftp
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shilpash
Posting Yak Master

103 Posts

Posted - 07/26/2013 :  16:02:42  Show Profile  Reply with Quote
I am trying to get the latest file from ftp.File format is like this-
abc_xx_123_a_b_c.exe
abc_xx_124_a_b_c.exe
abc_xx_125_a_b_c.exe
abc_xx_126_a_b_c.exe

There is no datestamp in the filename but number like 126 determine the latest file.So here abc_TEST_126_a_b_c.exe
is greater than abc_TEST_123_a_b_c.exe
and abc_xx_123_a_b_c.exe is the oldest file.

How would I accomplish this and I would prefer without doing script task.Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/27/2013 :  03:44:36  Show Profile  Reply with Quote
Add a for each loop to loop through the files and add filenames received to a table. Then add a logic to get max of number value within filename, something like

SELECT TOP 1 filename
FROM Table
ORDER BY LEFT(STUFF(filename,1,PATINDEX('%[0-9]%',filename)-1,''),CHARINDEX('_',STUFF(filename,1,PATINDEX('%[0-9]%',filename)-1,''))-1)*1 DESC



then in a file system task pass the filename got above as source to move file to wherever you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 07/27/2013 03:45:37
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000