The message I get from the 2017 SQL Server Import and Export Wizard which I need help with is,
"Error 0xc0204016: SSIS.Pipeline: The 'Data Conversion 0 – 0.Outputs[Data Conversion Output].Columns[Description]' has a
length that is not valid. The length must be between 0 and 4000"
The data source column in question is titled "Description" and is of Excel type "General". This spreadsheet was given a long filename extension of ".iqy.xslx", and it was produced by a SharePoint Online's "Export to Excel" command which I performed on a SharePoint list. I'm using Excel installed locally via the Office365 website. Back in the SharePoint list, the original column "Description" is of type "Multiple lines of text". The destination I am trying to populate using the SQL Server Import and Export Wizard is a VARCHAR(MAX) column within a table inside an Azure SQL Database.
You can clearly see in the following screen how the SQL Server Import and Export Wizard perceives the Excel data column in question. Here it calls it a "LongText", and I'm unable to change the data types of neither the source nor the destination:
The error message pops up once I begin the conversion in a subsequent screen, and you can see it if you click on the Messages link as seen below.
How can I properly input this simple Excel spreadsheet into my table on Azure?
Best Answer
I've finally obtained an easy enough workaround. What I did was reopen my *.idy.xlsx Excel spreadsheet where my source data resided and re-saved it as an Excel 97-2003 Workbook. These get a filename extension of *.xls. I went back to the SQL Server Import and Export Wizard and repeated the import steps, this time selecting the newly saved *.xls file. Ladies and gentlemen, it worked. A pair of trivial warnings appeared but the data came through flawlessly for me!