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 Dynamic Properties task to SSIS

Hi ,

What is the equivalent of DTS Dynamic Properties task into SSIS ?

How do I convert this task to SSIS ?

Thanks,

Vikas

But it used to work in DTS (2) - Changing connections using the Dynamic Properties Task
(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4157.aspx)

There is loads of information out there about this if you bother to look: http://www.google.com/search?hl=en&q=ssis+dynamic+properties+task

-Jamie

sql

DTS and macro excel

hi,
I have already set up a DTS package to convert my data to an excel file and I would like to alter the format of my data through my DTS without having to write a macro.
Do you know how to do this?need more details.|||From the DTS I get a plain text in my excel sheet. I have written a macro that takes all the sheets in the workbook and converts the first line of every sheet. The formating is to put a background color to and just make the cells and bit bigger.

Thanks a lot

DTs and Buisness Intellegence

i have a bunch of dts packages..from sql2k
is there a utility to convert them to sql2005

any suggestions
daveOn Jan 30, 10:01 am, "DAVE" <dvs_...@.sbcglobal.netwrote:

Quote:

Originally Posted by

i have a bunch of dts packages..from sql2k
is there a utility to convert them to sql2005
>
any suggestions
dave

NO.
Minimal conversion is built in...depends on complexity

SQL Server 2005 Books Online:
Using the Package Migration Wizard
Known Package Migration Issues

DTS (Oracle to SQL Server 2000)

I am trying to convert an application database from Oracle to SQL Server 2000. I am using Data Transformation Services utility provided by SQL Server. I am converting data on table-by-table basis. However, whenever I convert DATE fields (form Oracle) into DATETIME fields (to SQL Server 2000), my tasks fails. Does anyone knows the reason and, more important, a solution to this problem ?Hi

I'm also working with an Oracle Database.

In my configuration it works.
I used a Transform data task and the transformation I used is a copy column.

So

Oracle:
numeric/field1, datetime/field2 >> source in Transform data task

SQL Server 2000:
numeric/field1, datetime/field2 >> destination in Transform data task

Transformations: let SQL Server do automapping, copy column.

Greetings
Jon@.s