SSIS Convert Between Unicode and Non-Unicode Error with LARGE Field

0
votes

I have been struggling with this error now for days now and have tried everything I know. I have an SQL statement that pulls data from several tables into another table. The field in question is a NTEXT field from a SQL 2000 database, which I now import into a SQL 2008 R2 table that is NVARCHAR(MAX) data type because I though the issue was the NTEXT data type. However the SSIS package that is just an OLE DB Source (with 1 field) into an Excel Destination is still giving me the Unicode and Non-Unicode Error!! Several rows of data are over 8000 characters in length. Please help ...

1
ssis
check the datatype on your Excel destination column. It is likely that it is a non-unicode format. – Matt
No luck either. I followed these instructions to make the Excel destination UNICODE but I still got the same error. help.surveygizmo.com/help/article/link/… – Dave Stuart
That link shows you how to save a file as UTF8 but what version of SQL server are you using? UTF8 isn't supported till 2016. Latin1 such as in SQL_Latin1_General_CP1_CI_AS collation is windows 1252. – Matt
I'm using SQL 2008R2. I'm getting this error just trying to export a VARCHAR(MAX) with any length to Excel 2010 xlsx file. – Dave Stuart
Can you post the exact error? What is the encoding of the Excel file? If the File is UTF8 that is not supported. What is your goal? Are you trying to maintain latin characters in an export to Excel? Or is it really CSV (this was format of your link above)? Note Excel will open a CSV but it is not an Excel file. Sample Data? – Matt

1 Answers

1
votes

After a lot of pain I finally came to the conclusion that Exporting to EXCEL is not possible so I turned to CSV. I used "Flat File Destination" object, pointed to a CSV that I had created with just the Headers. The Text Qualifier was set to double quotes. In the Columns section I set the Row delimiter to {CR}{LF} and the Column delimiter to Comma{,} because it is a CSV! The final part of the puzzle was to remove and double quotes, Carriage Returns and Line Feeds. I also had to convert the NTEXT field to VARCHAR(MAX) because REPLACE will not work with NTEXT. This is what I ended up with for the columns that had these "invalid characters".

REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(MAX),[MyNTEXTColumn]), CHAR(13),' '), '"', ''), CHAR(10),'') AS 'Corrected Output'

I replaced {CR} CHAR(13) with a space to that we could have it formatted well for the consumer. I hope this helps someone out one day.