0

Task: I'm trying to iterate through excel files using foreachloop editor container. I was successful until i had different extensions meaning it's works as long as file extension is xls or xlsx but not both together.

Problem: I get errors when i try to iterate files with extensions xls and xlsx. Cannot acquire connection to connectionmanager.

For instance: I have abc.xls and agh.xlsx in a folder and i have trouble iterating thru files using Foreachloop editor.I think i understand & know why it's happening but can i write a script to do it or how to complete this task successfully.

Any ideas..

user1810575
  • 823
  • 3
  • 24
  • 45

2 Answers2

1

You will need to add 2 For Each Loop containers to iterate through files. the 1st FLC will process only .xls (or .xlsx) and the second FLC would process only .xlsx (or .xls). Other than that, I dont think writing a script would be of any help. But I could be wrong.

rvphx
  • 2,324
  • 6
  • 40
  • 69
0

Presuming all xls file have the same format and all xlsx files have the same format...

What you also could do is using one FOREACH loop to loop through all Excel files... then add a dummy task (empty Script Task or Sequence Container) and connect it to two Data Flow Tasks. One for XLS and one for XLSX. Then add expressions on the lines between the dummy tasks and data flow tasks where you check the extensions. Something like:

LOWER(RIGHT(@[User::Filepath],4))==".xls"

LOWER(RIGHT(@[User::Filepath],4))=="xlsx"

Joost
  • 1,873
  • 2
  • 17
  • 18