SSIS ForEach Loop: Excel workbooks with same structure failing "VS_NEEDSNEWMETADATA"
I am using SSIS to loop through a folder containing .xlsx files which all have one sheet and the same structure on that same sheet: 6 Header rows On row 7 I have 4 columns: SerialNumber PreReading PostReading DeltaAmt The ForEach Loop finds the workbooks, then finds the single sheet on each workbook and imports the data from the 4 columns above into SQL Server. The Data Flow takes into account new .xlsx files and existing .xlsx files that have been updated since the last run. Everything works great except that I encounter the odd error: Excel Source failed validation and returned validation status "VS_NEEDSNEWMETADATA" for only a handful of the .xlsx files. I've done a visual compare of these failing .xlsx files with the correctly processed .xlsx files and I can't spot any differences (the .xlsx files are created via a macro process I don't have access to and inserted into this folder). When I compare these failing .xlsx files, I can't spot any differences in the number of rows, data type differences, bad data, where the columns are located, where the header rows start/stop, etc. Any ideas of where I might be able to look to see where the differences in these failing .xlsx files are or ways to determine what is different about them? I load these files into a separate data task as the Excel source and I don't see any different column names or structure. Thanks!
I suspect one of the columns has a larger value like perhaps a longer character string than you have configured. I haven't had to import from Excel files in about a year, but I know we have seen that happen from time to time with text files. Try increasing the expected number of characters and see if that helps.