I am creating a SSIS package, in which I am trying to create multiple dynamic excel files with 2 worksheets in each file. I can do this by using foreach loop container and flat files but I am not able to do this with excel files. I am able to create a dynamic file but each time the loop runs instead of creating new file it just overwrites the existing excel file. But the same approach works by using flat files.
For Flat files destination I am changing the file extension to .xls so it forcefully creates dynamic excel files.
The for each loop container is configured as foreach ADO Enumerator with the ADO object as a source variable which I have created to store all my distinct supplier numbers for which each separate excel file needs to be created.
I have a sql task connecting to my foreach loop container which provides the distinct list of suppliers from the table.
