Error importing Excel data to SSIS in SQL Server 2005
I am just starting to learn SQL Server 2005 and I can't believe how absurdly difficult SSIS is when compared to DTS. I have been trying to import an Excel spreadsheet for two hours (would take me two minutes with DTS) and I continue to get the error: The column can't be inserted because the conversion between types DT_R8 and DT_STR is not supported. I don't even know what DT_R8 is?
I'm using an Excel source and a SQL Server destination. I've tried importing to an existing table and creating a new one. I've tried changing all the cells to text in the spreadsheet. Nothing works. Do you have any ideas for me?
Whenever a client has to deliver a file to me that I will need to extract via a DTS or SSIS package, I absolutely always avoid Excel for many reasons. Give me a csv file, a tab delimited file, or a '|' delimited file, but please not Excel. Sorry for ranting…here are some things to try. You can essentially line up the data types so they match. Carefully inspect the data types in your Excel file – especially for the failed column. You can right-click on cells and choose format to view the data types. The destination table's column must match these data types. If they do not, you may consider using the SSIS Convert task to change the data type before the insert.
This was first published in December 2007