December 23, 2014 at 10:12 am
I'm using an Execute Process Task to connect to a remote sftp site and to get a file having a specific filename. This is hardcoded for a specific file having structure 'name_YYYYMMDD.txt'.
Because the remote site sometimes retains outdated copies of the file in the same directory I need a way to get the file having a filename with the most recent YYYYMMDD in it's filename.
Can someone share a link or advise on an SSIS solution that can do this without scripting in some other language and without creation of a sql table for storing filenames before parsing?
I'd like a method that would pull the filenames into a variable which I can evaluate in memory.
Thanks in advance!
December 23, 2014 at 5:13 pm
One way might be to read the names of all the files in the directory into a variable one at a time, then parse off the date into a date variable. Then compare to the next file's date (that you strip out). Since you wouild only have @CurrentFileDate and @LatestDate as variables, you could compare the two easily. You'd have to create some variables to hold the currentFileName, the LatestFileDate, and the FileName for the file with the LatestFileDate. Then you could just loop through the directory, read the file names, split off the date, do the the comparisons. At the end, you'll have the file with the latest date. Then just use that in your transfer.
December 28, 2014 at 5:59 am
pietlinden, in the end, the approach I took was to create the portion of the filename that holds the date using functions and concatenate it to the part of the filename that always stays the same.
part-of-filename-that-doesn't-change + (dt_wstr,4)year(getdate()) + right("0"+(dt_wstr,2)month(getdate()),2)+right("0"+(dt_wstr,2) day(getdate()),2)+".csv"
The dtsx package will run everyday looking for a file having the current day's timestamp, and retrieve only it.
This idea was fortunately advised to me by my boss...which is great because I think your suggested method, peitlinden, is the only alternative to scripting. Hope you are having wonderful holidays.
December 29, 2014 at 6:18 pm
KoldCoffee (12/28/2014)
pietlinden, in the end, the approach I took was to create the portion of the filename that holds the date using functions and concatenate it to the part of the filename that always stays the same.part-of-filename-that-doesn't-change + (dt_wstr,4)year(getdate()) + right("0"+(dt_wstr,2)month(getdate()),2)+right("0"+(dt_wstr,2) day(getdate()),2)+".csv"
The dtsx package will run everyday looking for a file having the current day's timestamp, and retrieve only it.
This idea was fortunately advised to me by my boss...which is great because I think your suggested method, peitlinden, is the only alternative to scripting. Hope you are having wonderful holidays.
I wonder though what happens if on a given day the file is not there? There is no record that is was not pickup up and the next day a new file will be there to be processed. I would put some error handler on the task and email you at the least in this case so you can arrange a manual pickup .
----------------------------------------------------
December 29, 2014 at 6:32 pm
Thanks! I put a send mail task in case file not there, else there would be a hard failure which isn't nice, you're right.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply