Here is what I am trying to do. I have a single excel spreadsheet that I want to import. The data structure remains the same but the worksheet names changes every so often. I simply need to read the single spreadsheet and no matter the worksheet name import the data.
I have been attempting to do this in SSIS using a foreach container.
The format of the foreach container is this:
1.Foreach ADO.NET Schema Rowset Enumerator. The connection is set up to the Excel data source and the schema is set to table.
2. The variable mapping is set to use the Sheet Name package variable (this is the same variable I am trying to apply to my Data Flow task) and the index is set to 0.
I am just missing something in the logic of this process. I have been able to loop successfully through multiple Excel workbooks using the foreach file enumerator, but when I try to drill down and do the actual worksheets using the above settings it fails.
Start Free Trial
