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

Create Account

Microsoft SQL Server

Questions

Followers

Top Experts

TeknikDev🇺🇸

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

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!

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

ASKER CERTIFIED SOLUTION

Jason Yousef🇺🇸

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

TeknikDev🇺🇸

ASKER

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....

Jason Yousef🇺🇸

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 !!

TeknikDev🇺🇸

ASKER

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.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

Jason Yousef🇺🇸

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]

TeknikDev🇺🇸

ASKER

thats a good idea, i did that as well, but not using varchar. I'll give that a shot tomorrow., thanks for your help.

SOLUTION

Jason Yousef🇺🇸

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

Alpesh Patel🇮🇳

Untitled.png

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

TeknikDev🇺🇸

ASKER

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!

TeknikDev🇺🇸

ASKER

My solution worked for me and can be used as an alternative.

Microsoft SQL Server

Questions

Followers

Top Experts

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.