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.
Dig Deeper on Microsoft SQL Server 2005
Is there a query to search text in a SQL Server DTS package? SQL Server expert Joe Toscano explains which database objects can be viewed.continue reading
Convert SQL Server 2000 DTS packages to SQL Server 2005 with this advice and also see how to overwrite data.continue reading
Migrate DTS packages to SQL Server Integration Services with Migration Wizard and deploy SSIS packages in SQL 2005. Learn how to access DTS Designer ...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.