We're on different pages. We receive these from customers exporting their own data. The Excel file then contains multiple sheets, some filled out to the very end with data, some not. As an experiment, try creating a simple 3 column Excel sheet. Give each column a header. Fill out 4 rows, but don't fill in the last cell or two for some of the rows. Save as CSV. Open that in a text editor. Unless things have changed, you will not have extra commas indicating an empty column. That means that SSIS doesn't know what to do until it hits the actual EOL character or another comma/delimiter if it expects that first.

We can also add in some fun with people typing things like " The person said, "I want to go home later." and then left." in one column. 😀

I'm not really having major issues with this myself as I've learned a lot of workarounds to the fun that is Excel. My main point in responding to this thread is to help people not get caught as easily by the same things I've seen way too many times. DTS handled things differently, from Unicode conversions to mixed data, to how it handled imperfectly formed CSV files. SSIS is more strict and that's generally a good thing, but it can be really frustrating when DTS "just worked" and SSIS seems to require a lot more hand-holding. 🙂