How to change the default varchar 255 for a column when importing data from Excel into Sql Server using the export import wizard?

Question

I am trying to import data from excel into Sql Server using the import wizard. In the first steps, everything seems beautiful. The wizard can read the excel file and correctly identify the columns. But at the last stage, when the process is really running, it does not give the following errors:

Error 0xc020901c: data stream Task 1: an error occurred with the output of the AlternateName column (24) at the output of Excel Source Output (9). Return column: "The text has been truncated or one or more characters did not match on the target code page." (SQL Server Import and Export Wizard)

Error 0xc020902a: data flow Task 1: "AlternateName" output column (24) "failed because a truncation occurred, and the disposition on" output column "AlternateName" (24) truncation line indicates a truncation failure. A truncation error on the specified object for the specified component. (SQL Server Import and Export Wizard)

Error 0xc0047038: data stream Task 1: SSIS error code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on the Source - Sheet1 $ component (1) returned error code 0xC020902A. failure code when the pipeline engine is called PrimeOutput (). the failure code value is determined by the component, but the error is fatal and the pipeline has stopped executing. There may be error messages posted before that, with additional error information. (SQL Server Import and Export Wizard)

The AlternateName column has a maximum data length of 658 characters. The destination table has an AlternateName column defined as varchar (1000). So I was not sure why I was getting this error. But then I noticed it

I have a suspicion that this may cause a problem. But how to change the Source column defined as varchar 255?

Answer 1

You may need to write your own query in the wizard and specify the columns as varchar (1000). I am not sure that 100% this will work. Above, how big is the leaf? The wizard only "looks down" so many lines (IIRC 10000 by default, this is a registry setting) to determine the type and width of data. Reorder the excel file so that the longest AlternateName is at the top.

Answer 2

I had the same problem, and I realized that one way is to sort the rows that are large and cause problems in the Excel document according to the cell length from largest to smallest since the Import Import Wizard checks only the first 200 rows.

After the rows are used, the export import wizard detects a large length and changes the data type of the source column to LongText.