• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1100
  • Last Modified:

unicode and non-unicode string

cannot convert between unicode and non-unicode string.  The custdesc in sql table is ntext and casted as nvarchar(1000) in sql cmmand that pulls info into excel.   I have changed the excel  create table sql to
custdesc nvarchar(1000)  ,custdesc ntext null .  it does not seem to be working..any inputs?
0
ohemaa
Asked:
ohemaa
  • 3
  • 2
3 Solutions
 
BrandonGalderisiCommented:
Not sure what EXACTLY what you're doing, but it seems like you are trying to export data from SQL 2005 to excel?

Well for starters you should be using nvarchar(max) instead of ntext in your SQL 2005 tables.

text and ntext and scheduled to be removed in future versions: http://msdn.microsoft.com/en-us/library/ms187993(SQL.90).aspx

Perhaps you could create a view that casts your ntext to nvarchar(max).

create view vw_Customer
as
select cust.name, cast(cust.custdesc as nvarchar(max)) as custdesc
from customer cust
GO

Then try using the view as your source.
0
 
HoggZillaCommented:
Using the Advanced Edit, check the column datatypes for both SQL and Excel. Even if you CAST in the Source SQL it might be confused? Also, SSIS is very sensitive about nvarchar and varchar (unicode and non-unicode). It also hates NTEXT, there are some other workarounds.
Post your SQL Source and the Create Table for your Excel Destination for more input.
0
 
ohemaaAuthor Commented:
Checking Advanced Editor inputs outputs pro.

SQl inputs
Name      CustomerInfo
Datatype  String[Dt_str]
Length     (1000)

Excel outputs

Name      CustomerInfo
Datatype  String[Dt_str]
Length    (1000)
 but I still get customerinfo cannot convert between unicode and non unicode string data types
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
HoggZillaCommented:
There is another simple route you can try. Create a new package using the wizard allowing SSIS to create the Excel Destination table.
You could also try changing the database datatypes from nvarchar to varchar.
0
 
ohemaaAuthor Commented:
solution from google.  add data conversion and convert customerinfo to unicode
0
 
HoggZillaCommented:
OK, a Data Conversion task could work but I am not sure why you are selecting a column from SQL with NTEXT and it becomes a DT_STRING? If the column in SQL is NTEXT and you select it asis, it will come into SSIS as "Unicode text stream [DT_NTEXT]"
Since Excel only understands 6 datatypes from SSIS, see below, and DT_NTEXT is one of them, you should not be getting a unicode coversion error.
SSIS to Excel Datatypes:
Numeric  double-precision float (DT_R8)
Currency  currency (DT_CY)
Boolean  Boolean (DT_BOOL)
Date/time  date (DT_DATE)
String  Unicode string, length 255 (DT_WSTR)
Memo  Unicode text stream (DT_NTEXT)
 
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now