[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SSIS Unicode Non Unicode Datatype issue

Posted on 2012-09-11
Medium Priority
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
  • 2
LVL 16

Accepted Solution

DcpKing earned 720 total points
ID: 38389557
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

ID: 38389567
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

ValentinoV earned 500 total points
ID: 38389731
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

ID: 38390688
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

834 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