DTS erroring on index in unicode conversion

I have undertaken the following process to convert a database to unicode support. This is sql 2000 SP4

- Create a new database dbnew
- Script the old database dbold with all objects, everything, and dependencies
- Global replace varchar with nvarchar (etc etc) in the script
- Execute the script to create all objects into dbnew
- (Objects all exist fine)
- Startup DTS and choose olddb as the source, newdb as the destination
- On DTS step 3 choose "Copy Objects and Data between Sql Server Databases"
- Untick "Create destination objects"
- Change copy data to append data (all tables in dbnew are empty)
- Tick copy all objects
- Untick "Use default options" and clear every option (so hopefully we are only copying data)
- Click next and run

DTS gets through the first "phase" to 100% but then it fails on a duplicate key error on a table that has a unique key on its (now nvarchar) description field

Yet in Query Analyser I can do "insert into failingtable select * from olddb..failingtable" and the data comes across fine.

So why is it failing in DTS ? And are there any other options or settings I can try ?

thanks

One development on this..

The tables that are getting across are showing the nvarchar data as chinese symbols (where the source db was varchar not nvarchar, so just A-Z ascii etc). So I think this problem translates to how to get DTS to copy varchar data into nvarchar fields

I have been reading this article

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp

Which implies that everything is ok copying varchar to nvarchar, not so in my case. I think possible m$ only tested their software with US collection sequence and not UK default. ? Otherwise I'm lost.

sql

DTS (international question)

Quick background...
I have a file that is currently in Bulgarian (not unicode) that we want to import into a development environment for testing. However, the only way I can view the file as "non-gibberish" is for me to switch my local settings (on the OS) to Bulgarian. Then of course the file is readable in Bulgarian.

Now they have sent the following snipet of code asking if I can somehow add this to the DTS package? The purpose of this code is to...

They're sending me a C# routine to transpose the 10 or so Bulgarian-specific characters in the text fields.

They hope we can include this routine in the DTS package.

Here is the code...

private string StrFix( string InStr )
{
string Result = InStr;
Result = Result.Replace( '\x2591', '' );
Result = Result.Replace( '\x2592', '' );
Result = Result.Replace( '\x2593', '' );
Result = Result.Replace( '\x2502', '' );
Result = Result.Replace( '\x2524', '' );
Result = Result.Replace( '\x2561', '' );
Result = Result.Replace( '\x2562', '' );
Result = Result.Replace( '\x2556', '' );
Result = Result.Replace( '\x2555', '' );
Result = Result.Replace( '\x2563', '' );
Result = Result.Replace( '\x2551', '' );
Result = Result.Replace( '\x255d', '' );
Result = Result.Replace( '\x255b', '' );
Result = Result.Replace( '\x2510', '' );
Result = Result.Replace( '\x2552', '' );
return Result;
}

My questions... (as I'm not familiar with C#)

1. Do you guys see anything I'm not?
2. Is it possible to add this code to DTS package and have it change the 10 characters?
3. Wouldn't this be easier if their sample was in unicode?

Thanks in advanceThe question boils down to "which Bulgarian" are they using? When I hear Bulgarian associated with data files, I assume they mean Code Page 1251 which is Cyrillic. From the C# code, it looks like they are expecting one of the roman code pages, like 1250 or 1257. Those two code pages aren't very compatible.

If you can figure out which code page they can use to view the data as they expect to see it, you can use that same code page with either BCP or the BULK INSERT command to import the data from the file into a Unicode column and life ought to be lovely.

-PatP