November 12, 2009 at 2:51 pm
Hey forumites, I have an ssis task that I need directions with. I have an FTP task that I have set up to receive some files. A new file is added everyday to this ftp site and the old files are never deleted.
I need to somehow get the new file when i run the package everyday (dynamically) without mistakenly getting the old files. How can I do this dynamically? I also need to export the new file to a database everyday. Here is what I am doing so far.
For the FTP file transfer - I have set up the task and configured the manager. How do I dynamically let it choose the most recent file to transferf rom the ftp site?
For the dataflowr - I will attach a forloop container to loop through the csv file and in the container a data flow task for the transfer.
Any additions, subtractions, recommendations or better way to do this? Any input would be nice. Thanks
November 12, 2009 at 2:58 pm
Hi,
Can't you ask the file name created to be appended or prefixed with DDMMYYYY as 12112009 or MMDDYYYY format? That would save a lot lot of time for you.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 12, 2009 at 3:15 pm
Actually the filename uploaded is very consistent...i.e for today, it would be
konamifiles_transfer_2009-11-11.csv
Yesterday would be
konamifiles_transfer_2009-11-10.csv
and so on. How do I get only the file for the current day?
November 12, 2009 at 3:33 pm
Use an expression to set the file (name) that you want the FTP task to retrieve 🙂
Steve.
November 12, 2009 at 3:37 pm
Thanks for your input Steve, i have never worked with expressions and variables anyway you can explain further?
November 12, 2009 at 8:51 pm
JC7,
After dropping your ftp task on the canvas:
- on the general tab, set the connection
- on the File Transfer tab, set
- the operation to be Receive Files
- IsAscii (usually) set to false (ie use binary transfer)
- On LocalPath, create a new connection and point to the directory you want to save the file/s to
- On RemotePath, set this initially to / (ie forward slash).
On the Expressions tab, click in the text box next to the word 'Expressions' and then click the ellipses button (ie the '...' one). From the Property list, select RemotePath and then click the ellipses button again.
In the Expression Builder, enter an expression like the one below
"/konamifiles_transfer_" + (DT_WSTR, 4) YEAR( GETDATE() ) + "-" + (DT_WSTR, 2) MONTH( GETDATE() ) + "-" + (DT_WSTR, 2) DAY( GETDATE() ) + ".csv"
and click Evaluate Expression to check that it returns what you want.
Notes on the expression:
- because we're using an expression, whatever was written in the RemotePath (we initially set it to '/') will be overwritten with the expression value.
- the (DT_WSTR, <length>) is a cast, that is, a conversion of one data type (in this case, integer) to another (string).
- the GETDATE() is a function that returns the date 'as at right now'
- the '+' is a string concatenator
- note we still had to commence the remotepath with a forward slash. Also, if the file was not on the remote root, then we would have to include that path (e.g. the file is at /steve/at/home/yourfile.csv then we'd need the /steve/at/home/ in the front of our expression.
HTH,
Steve.
November 16, 2009 at 5:06 pm
Thank you for taking your time to help me with this problem, sorry this response is late, your effort is well appreciated !
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply