Posted by Erland Sommarskog on 01/10/06 00:25

(JayCallas@hotmail.com) writes:
> I have created an SSIS (think that is what DTS is called now) package
> to run on a SQL 2005 server. It's job is to connect to a SQL 2000
> server, execute a stored procedure, and insert the returned data into a
> table of the '05 box.
>
> In both tables (source and destination), the string columns are defined
> as VARCHAR. But when I run my package, i get back the following error
> 'Column "xxx" cannot convert between unicode and non-unicode string
> data types'.
>
> While they DO have different collation values --
> SQL_Latin1_General_CP1_CI_AS on sql 2000 database and
> Latin1_General_CI_AI on the sql 2005 database (columns are set to use
> database default value) -- it was my understanding (or
> mis-understanding) that collation values do not affect unicode issues.
>
> Is there anything I can look at to further find the problem or even fix
> it?

Beside testing the Copy Database Wizard, I have not used SSIS (or DTS),
so I don't know exactly what is going on. But it looks a little corny
to me. Had the collations had different code pages, it would have been
another matter, as then there could be characters in the source column
that could not be represented in the target colunm.

Had I known SSIS better, I might have been confident to recommend you
to file a bug on http://lab.msdn.microsoft.com/ProductFeedback/. But
now I would suggest that you should ask the people in
microsoft.public.sqlserver.integrationsvcs first.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]