Another VS_NEEDSNEWMETADATA issue, this time involving excel and a for each loop container.

• Duran

  SSChampion

  Points: 13259

  More actions

  June 18, 2017 at 11:46 pm

  #326831

  Hello,
  I hope someone can help.

  Its simple really, I have a For Each Loop container that contains a data flow task which consists of an Excel data source (.xls) that goes to a data conversion (nvar to var), then to a derived column task to remove a few bits from in front of some numbers, then into an ole db destination.

  I have yet to get this thing to work right all the way through, the looping appears to be working, so I know the filename (*) and directory are set up ok, but what appears to be happening is it goes through the first Excel file fine, as these are inserted into the destination, but when it gets to the second file it throws up an error which is...
  [Excel Source [199]] Warning: The external columns for Excel Source are out of synchronization with the data source columns. The Excel Source.Outputs[Excel Source Output].ExternalColumns[Object ID] needs to be removed from the external columns.
  [SSIS.Pipeline] Error: "Excel Source" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

  The structure of the two Excels sheets is the same, same columns, same number of columns,both containing the same type of data. I can see no difference between them. Object ID is the last column in both files.
  I have delay validation as True on the Excel Connection Manager, which has two variables for the name and directory of the files
  @[User::InputFolder]+ @[User::FileName]
  The Data Flow task has Delay Validation set to TRUE.
  The value of Filename is blank, InputFolder has the path to the file.
  I have tried clicking on all the components to see if this will go away, but to no avail.
  When I double click the Excel Source in the DF, it throws an error saying it cannot connect to the Excel Connection Manager, and that the "The Microsoft Jet database engine cannot open the file (directory location here\), it is already opened exclusively by another user, or you need permission to view its data". But I think this is a sympton of have variables in the connection manager as the loop works on the first file.
  The package is executed under 32 bit. Both Excel files are the same version.
  I cant think of anything else to add. I am no longer sure which direction to turn to get this working. Some help would be much appreciated. Thank you.

  Kind regards,
  D.