Excel file changing hourly

Question

Post reply

Excel file changing hourly

rafamaniac

Old Hand

Points: 359
More actions
November 15, 2021 at 5:57 pm

#3953259
I have some excel files that got imported to a certain folder through ftp. this files are going to that folder hourly. every time an excel file goes into this certain folder it has all the accumulations of the excel files before, so only the most recent one matters. this basically means the moment i import a file i have to deleted since it won't matter any more. How can i automatize this process in ssis? i know i have excel source and foreach loop but i am not seeing how to do this because the objective is not to constantly change the "excel file path" in excel connection manager every hour obviously. i have searched and there are some people that do this in scripts but is kind of confusing and i am not sure if it is even related to my problem. can someone explain to me in detail how to do this?
- This topic was modified 3 years ago by rafamaniac.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Should be doable. Which part of this problem is puzzling you?

How do I delete (I would suggest 'archive' as a possible alternative to deletion) a file?
How do I kick off an SSIS package when a file is placed in a folder?
How do I do an incremental load?
something else?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

rafamaniac Old Hand Points: 359 More actions · Answer 2

i just cannot figure how to do this. so imagine i import file_x1, then after i imported it i delete it with a file task, but the next hour the other file comes file_x2 the "Excel file path" won't recognize the new file unless i go there and change it manually.Eevery hour, there is only a single file before import.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

So every time the file appears, it has a slightly different name? As long as there is only one file in the folder at any one time, this is not difficult to do.

You need to use a FOREACH container – and within that, you configure the filename pattern, *.xlsx, for example.

Please take a read of the following link. It does most of what you want - the fact that you have only one file is not important.

https://www.sentryone.com/blog/how-to-loop-through-files-in-a-specified-folder-load-one-by-one-and-move-to-archive-folder-using-ssis

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

rafamaniac Old Hand Points: 359 More actions · Answer 4

thank you, the trick was to make delay validations to true on data and foreach loop and also make a dynamic connection string with a variable filename something like "

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::Filename] + ";Extended Properties=Excel 12.0".