Share via

Data conversion failed

Nicky 116 Reputation points
2020-10-22T08:35:25.407+00:00

Hi

I am exporting data from Sql server to Csv. My table has special characters which are important to retrieve but SSIS task throw error while exporting data. Characters like ' ồ ' and many more in different columns.

Datatype in SQL Server for this column is nvarchar(50) and in flat file I am using unicode string [DT_WSTR]. If I check Unicode in General tab data gets exported by I get a comma delimited file. When i do not check Unicode checkbox my csv gives perfect data but fails at these special characters.

I am using 1252 ANSI-Latin 1
Error: [Flat File Destination 1 [34]] Error: Data conversion failed. The data conversion for column "Column10" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Thanks !

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,897 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,467 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
Accepted answer
  1. Nicky 116 Reputation points
    2020-10-23T15:30:57.693+00:00

    anonymous user : If I do that I will get error 'column and text can't be same' as you suggested text to be '' .

9 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Ben Miller (DBAduck) 951 Reputation points
    2020-10-23T16:17:18.567+00:00

    You could do excel and format the column as you put it in. Maybe using ImportExcel module for PowerShell to take the | delimited files and Export it to Excel using that module. it will let you format the column and you should be able to get it to not convert the value.

    0 comments
  2. Nicky 116 Reputation points
    2020-10-23T16:14:55.633+00:00

    @Ben Miller (DBAduck)

    I was epecting to give data to business in a way where they can use files directly as there are many csv files. If i convert it to excel file, numeric data will be converted to text. As of now I am sharing csv with them comma seperated or | seperated.

    Thanks for your help. Will figure out something.

    0 comments
  3. Nicky 116 Reputation points
    2020-10-23T15:57:50.31+00:00

    @Ben Miller (DBAduck)

    Hi Ben

    Thanks alot for your help. It works fine, I have used '|' instead of ','. Also I used nothing for text. Data export is successfull. 

    Is there any way I can show data in seperate columns in csv ?
  4. Nicky 116 Reputation points
    2020-10-23T15:02:50.567+00:00

    @Ben Miller (DBAduck) 

    What should be RowDelimeter in Columns tab?

    I get error "Row delimeter cannot be same as column delimeter". My row delimeter is CR LF

    Thanks !