CODEWITHSUNDEEP
sqlssis
lch
lch

Reputation: 193

SSIS excel source column name mismatch error

I have an SSIS package, with Excel as the source, loading data to an OLE DB destination.

Today it failed with the error:

[Excel Source [1531]] Error: Column "Product Value" cannot be found at the datasource.

After some effort, I realized that the column name, which used to be "Product Value", was now "Product Value ".

The extra space at the end of the column name is causing this problem. Is there any fix for this type of error?

Upvotes: 1

Views: 7354

Answers (1)

Josef Richberg
Josef Richberg

Reputation: 613

In your Excel Connection Manger there is an option that is usually checked called 'First Row has column names'; un-check it. If you do that, the columns will be renamed 'F1,F2,F3' etc. You can then open up the Advanced Editor on your Excel Source and change those name to be whatever you need. This removes the mapping of the incoming header name to your data flow and makes your package immune to header name changes. You will have to add an extra component, Conditional Split, to remove the first column which will now contain header information, which you won't want to process.

Upvotes: 3

Related Questions