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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 !!
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.
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]
SELECT convert(varchar(50), '12345') as accountnum,
'the test account' as fundname,
* from [SHEET!$A:$B]
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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!
ASKER
My solution worked for me and can be used as an alternative.
ASKER