November 15, 2021 at 5:57 pm
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?
November 15, 2021 at 6:03 pm
Should be doable. Which part of this problem is puzzling you?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 15, 2021 at 6:11 pm
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.
November 15, 2021 at 6:59 pm
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 15, 2021 at 10:52 pm
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".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply