excel to sql tables
-
how to find latest excel file in folder using ssis and import it into sql tables?
i tried to use file system task but i don't know how to find latest file in a folder?
thanks
-
you could use a foreach loop to loop through the folder, get the filename for each file, then use a script task to check for the Last Update date. Or, use a script task to do the whole thing.
Keep track of these files and their dates and use the newest file at the end of the loop. If you have to keep rechecking the same folder, and the list of files keeps growing, you could hold the filenames and dates in a persistent state (store in a table or something) rather than checking everything from cold every time.
Check out this forum on MSDN for more info on getting file attributes. The file system task doesn't seem to be able to access attributes, only set things like hidden, archived, etc.
Tom
Life: it twists and turns like a twisty turny thing
-
Howdy! All you need to do is create a variable named Path1 as a string and put in the dir as the Value, use the "Foreach" Control Flow Item, Set the Variable Mapping of the Foreach item to the variable with an index of 0, pass the output to a Data Flow Task, Change the SourceConnectionFlatfile to use an expression of: @[User::Path1]
And viola - you will then have the results of the last file processed in the Foreach loop (a.k.a. the latest file in the directory) passed in to the SourceConnectionFlatfile to process.
Cheers,
SSchommer@!
(a.k.a. Shasta247!)
-
We really dont need foreach task to do this, all the steps can be done in single scrit task using for each file collection...
get all files using system.io
compare modified date, take latest file into variable
loop thru all files..
import that file..:cool:
OR
Each time you process a file, save the file name in database
loop thru all files, check gainst table , were the file processed or not
if processed then leave that file
if not processed , process that file
this works if u have multiple files which are new...and to be processed...
cheers
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply