SQL Server imports, multiple columns with the same name

I needed to set up a recurring import of CSV data into SQL Server using BIDS. The source data contained multiple columns with the same name. SQL Server does not like this situation – it errors out with “There is more than one data source column with the name ‘First Name'”. One option is to change the column names manually in the source data, but that’s not a very good solution for a recurring import.

After a little searching I found that you can rename the source columns when you set up your data source. Change each offending name one time when you set up the import, then the error disappears.