SSIS Unicode Non Unicode Datatype issue

Posted on 2012-09-11
Last Modified: 2012-09-16
Hi I am using a simple ssis package to transfer data from oledb source into excel destination but keep getting the error message. 'Cannot convert between unicode and non unicode string datatype'.

My steps were:

Inside control flow I dragged a dataflow task. I double clicked it and dragged pledb source and set up the connection source for my data base. Then I dragged the excel destination and set up the connection and map the columns after which I get this error message. I think I have to use a data transformation but do not have any clue pf what I should do in it.

Thanks in advance
Question by:Josh2442
    LVL 16

    Accepted Solution

    Your source is ordinary varchars and your destination (Excel) wants nvarchars. I believe the easiest way is to make your source nvarchar before it ever hits SSIS. Try changing the output of the stored proc, or write a view on the table that converts the varchars into nvarchars, and use one of those instead of your objects with just varchars.



    Author Comment

    Could you show me or explain how I can write a view on my source table that changes the varchar to nvarchar please!
    LVL 37

    Assisted Solution

    One of your options are the following: in the OLEDB source, use a SELECT statement instead of selecting the table from the dropdown list.

    In that SELECT statement you can then convert to the correct datatype.  Here's an example with just one field, converting to unicode:

    SELECT CONVERT(nvarchar(100), SomeVarcharField) as ConvertedVarcharField
    FROM SomeSchema.SomeTable

    Open in new window

    Of course you'll need to adapt this to your situation.  Select only the fields that you need in the output and take the field lengths into account (in the example above the field length is 100).

    Another option is using the Data Conversion transformation.  Using the same example, you'd write an expression as follows:

    (DT_WSTR, 100)SomeVarcharField

    Open in new window

    And the Derived Column Name would be ConvertedVarcharField while "Derived column" should be set to "add as new column".
    LVL 16

    Expert Comment

    Old Table:
    create table dbo.old(id int, Field1 varchar(15), Field2 varchar(200))

    create view dbo.vnold
    select id, cast(Field1 as nvarchar(15)) as 'Field1', cast(Field2 as nvarchar(200)) as 'Field2'
    from dbo.old

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now