?
Solved

ERROR when importing in SSIS  - cannot convert between unicode and non-unicode string data types

Posted on 2011-10-24
10
Medium Priority
?
527 Views
Last Modified: 2013-11-10
Hi,

I keep getting this error where it takes the excel data and tries to insert into SQL server table.

The Excel source is selecting hard coded text into the two columns.
--------------
So sample SQL for Data Flow Excel Source is:

SELECT '12345' as accountnum,
      'the test account' as fundname,
      * from [SHEET!$A:$B]

Error Message:
"...columns cannot convert between unicode and non-unicode string data types."
--------------
I try to add a "data conversion" dataflow object and choose  DT_STR (string) and DT_WSTR (Unicode string).

The SQL table two columns are varchar(20) and varchar(max). I have no idea how to fix this, i tried changing the columns to nvar on the SQL table, but that didn't work either.

Please help!
0
Comment
Question by:TeknikDev
  • 5
  • 4
10 Comments
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 2000 total points
ID: 37021406
0
 

Author Comment

by:TeknikDev
ID: 37021655
Thanks, but I did do what it says. I noted that I used the data conversion in the data flow to try to force the conversion but failed....
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37021664
That's odd, I can't think there's other solution, If that failed for you and also changing the data type in the DB to NVARchar !!
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:TeknikDev
ID: 37021686
I hope someone has a solution. I'll try deleting the data conversion data flow and see tomorrow when I'm back in the office.
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37021702
I'm not sure if you can use convert or cast

SELECT  convert(varchar(50), '12345') as accountnum,
      'the test account' as fundname,
      * from [SHEET!$A:$B]
0
 

Author Comment

by:TeknikDev
ID: 37021704
thats a good idea, i did that as well, but not using varchar. I'll give that a shot tomorrow., thanks for your help.
0
 
LVL 21

Assisted Solution

by:Jason Yousef, MS
Jason Yousef, MS earned 2000 total points
ID: 37021718
You're welcome, i've joined your other question, please let me know how it goes. Thanks
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 37022431
0
 

Author Comment

by:TeknikDev
ID: 37024251
Darn it! I get  "Undefined function 'CONVERT' in expression (Microsoft JET Database Engine) error!

SQL:
SELECT     CONVERT([varchar](255), 'TEST') AS Name, CONVERT([varchar](20), '8292') AS AcctNum, CONVERT([varchar](255), 'Chicago') AS Location from [SHEET$A:$D]

That Data Conversion trick didn't work either. I manage to get around it by defaulting the column values in the table since it was unique and also a script that updates the table following the other columns are inserted so it isn't using excel as the source.

Thanks for all your help guys!
0
 

Author Closing Comment

by:TeknikDev
ID: 37024264
My solution worked for me and can be used as an alternative.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

840 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