1

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?

Improve this question
2
  • Try to convert the file into .csv file and then import using SSIS. Commented Feb 24, 2012 at 3:29
  • 2
    You can fix it by updating the file (remove the extra space) if you know this file was an anomaly. Or if the file is going to have this trailing space going forward, you'll have to update the package. If you're planning on updating the package, you can ignore the first header row and just go by the column order to determine what they are. I don't know if that makes sense. Commented Feb 24, 2012 at 4:07

1 Answer 1

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks Josef its working fine for me now i followed your steps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.