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 conversion error

I have a dts package that imports data from a comma delimited .csv file.

I'm getting a conversion invalid for datatypes on column pair 1 (source 'col0012' (DBTYPE_STR), destination column 'latitude' (DBTYPE_R8)).

So, the source file is populated as a string for 'col002' and I have that field specified as a float within my table. Float is the correct type for the value.

How can I make the conversion correctly during the dts execution.

Thank you.do you have any empty strings in the source file going to the float column?|||Also - does your CSV include column headings and, if so, have you ticked the box in the DTS to let SQL Server know that?sql

DTS - Excel conversion from Number to Database Char

I have to import data from Excel file to an SQL Server Database.
One of the Excel Worksheet columns it's number (with max value of 4550204008914630000), I will import the column to a char 21 database field. Using a DTS to do the work, when I import that column it will convert the data in something like 4.5502041E+18.
Can you give me some help for the DTS.

Thanks,
PauloDoes the table already exists? Or are you letting DTS create it?|||The Table already exists. It's created each time the DTS run.

This is the script used on the DTS:
/*
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Temp_freqnib]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Temp_freqnib]
GO

CREATE TABLE [dbo].[Temp_freqnib] (
[Cartao] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[numX] [char] (21) COLLATE Latin1_General_CI_AS NOT NULL ,
[desc] [varchar] (80) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
*/

It must be imported to the numX field.

Thanks,
Paulo|||my mistake...

The table exists only when the DTS runs, and it's created before the import of the data from excel.

Paulo

DT_DBTIME to DateTime conversion

I have a column of DT_DBTIME data type in my Flat File source. This column is mapped to DateTime column in the SQL server destination table. A problem is that time values '12:06:39' and '13:37:45' are converted to the dates '12/30/1899 12:06:39 PM' and '12/30/1899 1:37:45 PM' correspondingly.

When I try the similar conversion from the inside Server Management Studio :

SELECT CAST('12:06:39' as DateTime), CAST('13:37:45' as DateTime)

I get the following results:

'1900-01-01 12:06:39.000' and '1900-01-01 13:37:45.000'

As you can see, the date portion is different and I expect that January 1st, 1900 is correct for this conversion. So, my questions are:

1. Why I have "previous" day in the Integration Services transformation?

2. How to fix this problem?

Thank you,

Vlad

Don't know why it is happening, but it seems like you could use DateAdd in a derived column pretty easily to add 2 days.
|||

Microsoft SQL Server

Values with the datetime data type are stored internally by the Microsoft SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900.

SSIS

The DT_DATE data type is implemented using an 8-byte floating-point number. Days are represented by whole number increments, starting with 30 December 1899, and midnight as time zero.