Q
Problem solve Get help with specific problems with your technologies, process and projects.

Error importing Excel data to SSIS in SQL Server 2005

Getting an error when importing data from an Excel spreadsheet to SSIS in SQL Server 2005? Try lining up data types in Excel and SSIS so they match.

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 last published in December 2007

Dig Deeper on Microsoft SQL Server 2005

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close