0

When importing a csv file into SQL Server 2019 via Import/Export wizard I am getting following well known error despite the fact that the destination column length is varchar(50) and the source column length is 22. I thought this highly active stack overflow response would have resolved the issue. But no success.

Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "column_name" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Similar to what is advised in the above linked response to a similar question, in the advanced tab of the wizard, I am correctly getting the destination column data type, as shown below:

enter image description here

Question: what else could be a cause of the error and how can we resolve it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
nam
  • 21,967
  • 37
  • 158
  • 332
  • If you've defined the source column as 22 characters that implies that one, or more, of the values in the file is 23 characters or longer. – Thom A Nov 15 '21 at 00:00
  • 3
    The quickest way to get to the bottom of this is to just extend all data types to varchar(8000) and see what actually gets imported. Most likely you have an extra or missing comma in the file and it's not importing the data you think it is. – Nick.Mc Nov 15 '21 at 00:17
  • @Larnu I checked in [Azure Synapse studio](https://www.sqlshack.com/getting-started-with-azure-synapse-studio/) by using `LEN` function on the source column to verify that the length is 22. I tried also by using the `DATALENGTH` function (that does not ignore the trailing blanks) - and the result was still 22. – nam Nov 15 '21 at 00:48
  • There are two parts to the error message. Have you considered the possibility that it's not actually truncation but instead international or Unicode characters in the CSV file that cannot be mapped to your destination column's collation? – AlwaysLearning Nov 15 '21 at 03:50
  • Maybe there is comma missing causing the columns to shift one place so to speak. Or could be a string value not quoted correctly. You could check the csv file for any errors using the **CSV Lint** plugin for Notepad++ https://github.com/BdR76/CSVLint/ – BdR Nov 15 '21 at 08:29
  • If you're using Synapse.... why are you using SSIS? – Nick.Mc Nov 18 '21 at 11:44

0 Answers0