Solving an SSIS Error – Cannot convert between Unicode and non-Unicode

Question

Post reply

Solving an SSIS Error – Cannot convert between Unicode and non-Unicode

Thomas LeBlanc

SSCrazy Eights

Points: 8085
More actions
December 14, 2016 at 10:13 pm

#332816

Comments posted to this topic are about the item Solving an SSIS Error – Cannot convert between Unicode and non-Unicode
Thomas LeBlanc, MVP Data Platform Consultant

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

sqlpadawan_1 SSCommitted Points: 1571 More actions · Answer 1

Great article.

So, do you find this a better option than using a data conversion transformation, or just a different option?

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 2

If my source is a SQL Server, I generally prefer using CAST/CONVERT for the appropriate data-type conversion. For non-relational sources (e.g. an Excel File), I use the Data Conversion Transform.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Thomas LeBlanc SSCrazy Eights Points: 8085 More actions · Answer 3

Thanks for comments.

You can use the Data Conversion component and get the same results. My preference is changing the T-SQL and not use a Data Conversion component, but not for any technical reason.

What I was really trying to do here in addition to converting the text data, was to show the Advanced Options of a Source Component, Input and Output Properties and the effect of changing the data types.

This was something I never used or looked into in SSIS until I needed it.

There are many areas of SSIS I never knew existed until I dug deeper, then realized how powerful of an application SSIS really is.

Thomas LeBlanc, MVP Data Platform Consultant

Thomas LeBlanc SSCrazy Eights Points: 8085 More actions · Answer 4

Thanks Nakul for comments.

Good point about when to use Data Conversion component.

Thomas LeBlanc, MVP Data Platform Consultant

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

December 15, 2016 at 6:26 am

#1918158

Thanks for today's lesson.

kbatta SSCrazy Points: 2274 More actions · Answer 6

Another cause of this error is someone managed to get a tab character into what is supposed to be a text file. In that case you may need to do some editing and either replace the tab with a space or report it and fail the load. I worked in data warehousing so I am used to bad data. There are some other characters besides tab that will also cause this error.

fsamstag SSC Rookie Points: 40 More actions · Answer 7

As a training exercise to learn about the Advanced Options, I suppose this is useful. But wouldn't it have been a lot quicker and easier (especially for a much larger project) to simply delete the Data Source Task, and recreate it with the modified T-SQL?

RonKyle SSC-Dedicated Points: 31557 More actions · Answer 8

While the article doesn't explain the context of the data move, if it's the start of a BI ETL, I think it's better to make the landing table match the source. The first job is to own the data with ideally no changes made to the data. Once you own the data, there are some columns you may want to convert. But there are some columns for which you might want to maintain the nvarchar because of the characters they might contain. There are some designers who make all their columns nvarchar because of what they might possibly contain. This seems to me to be wrong because some columns will only have numbers, and others only basic alphanumerics. But I think your article could have addressed this briefly.

When I do change it, I also prefer to use the cast in TSQL rather than the data converter. I've done it both ways and at least for me, this seems to be easier to maintain.

Eric S Blake SSC Enthusiast Points: 195 More actions · Answer 9

Non-ascii (control characters) in SQL field.

Normally, I wouldn't worry about these, however, if you are running a Data Warehouse and use these as dimension attributes, keys, etc. OLAP processing will have a fit!

Since a couple of my feeds come from a web-based application server, there is always the potential for CR, LF, TABS and other strange non-compliant characters.

The other place this gets you is in reporting, sometimes you end up with a FF (Form-Feed) character that the printer recognizes and boom, your report now has a blank page or two in the middle of it.

To reduce this I have created a CLR function that uses REGEX to remove all non printing characters from the string value.

Doing this does a couple of things, A) remove the unwanted character and B) converts from nchar/nvarchar to char/varchar and finally C) Handles nulls (I hate nulls) esp in OLAP Data Warehousing.

As to the original post on the SSIS component, I default to the CAST/CONVERT of SQL server to deal with most of this, in getting the output fields to match, I take the source SQL and use a simple SELECT 1 AS A , then I click on the link (between source and destination) then remove unmapped fields, this remove the now incompatible types. I then replace my simple select with my true select and remap the fields.

This approach works for me as I am in control of both the SOURCE and DESTINATION schema, so the column mapping happens automagically due to the names being identical. This may not be the best approach for all situations.

sql Sarah SSCrazy Points: 2081 More actions · Answer 10

I have a question about this. We have fields with the data type of NVARCHAR because there are special characters in them.

If you are converting them to VARCHAR, what happens to the special characters? What does the data look like in the output?

Thomas LeBlanc SSCrazy Eights Points: 8085 More actions · Answer 11

sql Sarah (12/15/2016)
I have a question about this. We have fields with the data type of NVARCHAR because there are special characters in them.
If you are converting them to VARCHAR, what happens to the special characters? What does the data look like in the output?

SQL Sarah,

If that is the case, I would have the Destination table columns as nvarchar. This example case in the article does not have special characters for the Source column.

Thanks for the question!

Thomas LeBlanc, MVP Data Platform Consultant

Thomas LeBlanc SSCrazy Eights Points: 8085 More actions · Answer 12

fsamstag (12/15/2016)
As a training exercise to learn about the Advanced Options, I suppose this is useful. But wouldn't it have been a lot quicker and easier (especially for a much larger project) to simply delete the Data Source Task, and recreate it with the modified T-SQL?

fsamstag,

Yes, that is easier if there are not many columns coming from the source. 🙂

Thanks for reading and commenting,

Thomas

Thomas LeBlanc, MVP Data Platform Consultant

Eric S Blake SSC Enthusiast Points: 195 More actions · Answer 13

Sarah: Depends on the character. If the characters are non-printable the return blanks. Otherwise they map to non-extended code chars.

I work with overseas (Non-US) data and have to remove some of the accents/extended chars due to the way OLAP is configured to work (US Only). So these are either stripped out by the CLR function or converted to 128 ascii.

Since we are US based it is not a big deal as all of the business logic is based off of.

Hope this helps answer you question, but short of making up some examples I don't have a direct answer. Guess I should have started with this sentence.

RonKyle SSC-Dedicated Points: 31557 More actions · Answer 14

I work with overseas (Non-US) data and have to remove some of the accents/extended chars due to the way OLAP is configured to work (US Only).

I don't understand this. Why can't nvarchar fields be used even if it is US Only? Some of my fields are nvarchars, and I haven't run into any inherent issues that I've noticed.