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

  • Thomas LeBlanc

    SSCertifiable

    Points: 7973

    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

  • sqlpadawan_1

    SSCommitted

    Points: 1571

    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: 10210

    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
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Thomas LeBlanc

    SSCertifiable

    Points: 7973

    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

    SSCertifiable

    Points: 7973

    Thanks Nakul for comments.

    Good point about when to use Data Conversion component.

    Thomas LeBlanc, MVP Data Platform Consultant

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for today's lesson.

  • kbatta

    SSCrazy

    Points: 2274

    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

    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: 31482

    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: 184

    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: 2021

    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

    SSCertifiable

    Points: 7973

    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

    SSCertifiable

    Points: 7973

    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: 184

    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: 31482

    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.

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

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