Well it looks like I am answering my own question. Even SSIS in SQL Server 2008 with SP1 is just not capable of a simple import from a CSV file, one of the most common and transportable data formats in the world!

I tried using SQL Server 2000 DTS for the CSV Import and it worked perfectly. It even recognized the double-quote text delimiters without any help from me. I should be able to create a DTS package for all the files and then move it to SQL2005 using "backward" compatibility tools (I hope).

Imagine that...11 years later SQL Server 2008 with Service Pack 1 can’t even perform the most rudimentary import. That's simply shameful Microsoft.