question

meytcheson avatar image
meytcheson asked

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!
ssis errorssis excel
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tom Staab avatar image
Tom Staab answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'll have to take a closer look, but all the data seemed to be in the same format in the files that were failing - same number of characters, data types, etc. I'll increase the expected number of characters to see if that helps.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.