January 23, 2009 at 7:10 am
Copy File
How do I get the FTP Task to search in a RemotePath for a dynamic file name.
i.e. a file with today’s date
January 23, 2009 at 7:13 am
You will need to use a variable for this, in the FTP task editor set IsRemoteVariable to True. then construct a variable based on the date and use this as the file name
January 23, 2009 at 7:30 am
Thank you, this is where im having the problem, constructing a variable based on the date.
The file name it 'Football_Results_TODAYSDATE.pdf'
So in the 'Add Variable' box under Value can you help me with the vaule?
Thanks A LOT!
January 23, 2009 at 7:36 am
Oh and the date format will always be...
20090123
So the full file name would be
Football_Results_20090123.pdf
January 23, 2009 at 7:46 am
Could I put this in as a Vaule in the 'Add Variable' field
print 'football_results' + convert(char(4),datepart(year,getdate()))
+ case convert(char(2),datepart(month,getdate()))
WHEN 1 THEN '01'
WHEN 2 THEN '02'
WHEN 3 THEN '03'
WHEN 4 THEN '04'
WHEN 5 THEN '05'
WHEN 6 THEN '06'
WHEN 7 THEN '07'
WHEN 8 THEN '08'
WHEN 9 THEN '09'
else convert(char(2),datepart(month,getdate()))
END
+ case convert(char(2),datepart(day,getdate()))
WHEN 23 THEN '01'
WHEN 2 THEN '02'
WHEN 3 THEN '03'
WHEN 4 THEN '04'
WHEN 5 THEN '05'
WHEN 6 THEN '06'
WHEN 7 THEN '07'
WHEN 8 THEN '08'
WHEN 9 THEN '09'
else convert(char(2),datepart(day,getdate())) END + '.pdf'
January 26, 2009 at 10:35 pm
You can use an expression in your FTP Task to accomplish this. Open the FTP Task, and in the Expression Editor, select the Remote Path and use something similar to the following:
"Football_Results_"
+ RIGHT("00" + (DT_STR, 2, 1252)MONTH(GETDATE()), 2)
+ RIGHT("00" + (DT_STR, 2, 1252)DAY(GETDATE()), 2)
+ (DT_STR, 4, 1252)YEAR(GETDATE())
+ ".pdf"
This should get you the filename formatted with the current date. If you need to modify this to pull a different date, you can use the date add functions in the Expression Language.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
March 24, 2009 at 11:51 pm
How about dynamically creating that same date format -2 days?
May 4, 2009 at 8:04 am
How about find a file posted yesterday?
Thanks,
Qjlee
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply