SQL Server 2005..

[Stoo]

Is anyone using this currently?

I'm having major issues transitioning our 2000 DTS packages over to 2005, 2005 just doesn't seem to want to import the data, bugging out every time with the error:

"cannot convert between unicode and non-unicode string data types."

In fact, I get the exact same error trying to do it from scratch with the import wizard to create a brand new SSIS package.

This is the data file I'm trying to import, it's vertical bar delimited, with mac style {cr} only row delimiters (this isn't an issue at all in SQL Server 2000).

This is the table it goes into:

	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SiteCode] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[SiteName] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[SiteType] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[Country] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[Region] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[Area] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[County] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[Place] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[PageNumber] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[SiteDesc] [ntext] COLLATE Latin1_General_CI_AS NULL,
	[SiteFeatures] [ntext] COLLATE Latin1_General_CI_AS NULL,
	[Airport] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[Note] [ntext] COLLATE Latin1_General_CI_AS NULL,
	[VTEnabled] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[IndoorOutdoor] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
	[IO_Text] [ntext] COLLATE Latin1_General_CI_AS NULL,
	[SpecialHead] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
	[SpecialText] [ntext] COLLATE Latin1_General_CI_AS NULL

Am I correct in thinking that 2005 is being far more specific with it's table formatting, which is then causing an issue when I try to import that data into the existing table?

[shaithis]

Just as a test.....have you tried importing them into a table with the fields defined as non-unicode? i.e. remove all the n's from ntext, nvarchar etc.

[Stoo]

Hmmm.. I beginning to suspect that it's the table at fault rather than the data.. can't check now until tomorrow, but it gives me a place to look.. cheers

Having said that, it was coming up with some weirdness on the data preview of the flat file connection.. (trying to start the next row in the end 2 columns..)

[TiG]

Erm are you importing this as a txt file, i've used 2005 a fair bit now, but still not got it 100% down from memory. Isn't there an option to open the file as a different format, (not Windows ANSI) but Unicode instead, this hopefully should handle the solution better?.

If not i can try at work tomorrow if you are still having issues.

TiG

[Stoo]

Yup, straight import as a text file (the text file in question is linked up there ^), I did try checking the unicode import option, but then it couldn't find the row/column delimiters..

I tried changing the code page to "MAC Roman" on one attempt too, but no dice..

I did copy the database across from the 2000 developer edition initially, then then I did a drop/create operation in the SSIS package, so the table structure should have been even more basic than the above SQL (I think the script table to clipboard operation added a few more bits - I posted it just as I was going out of the door last night).

I'll have another play with it when I get in today..

[TiG]

It doesn't look like its anything to do with the table to me, it looks like the data vs the codepage of the text you are importing. For a start i'll tell you now its not SQL Safe. That is i'd never consider having things like the following...

Cromer's

Cromer& #39;s

inside a database.

I've got it to import some data but as with SSIS you can't do manipulation of the import in the bulk insert task i'm afraid i'm stuck and i've not enough knowledge with SSIS yet to know the best way to get around this.

Don't know if thats helps but thats as far as i've got.

TiG

[Moby-Dick]

not got my sql 2k5 install up yet

[Stoo]

Ahh, I've not tried using the bulk insert task, I'm using the flatfile source > Data conversion > Data Flow Task method at the moment..

I've also tried it from the import/export SSIS wizard, and still no joy..

tbh, it looks like 2005 needs a good usability patching, 2000 was far more intuitive..

[Stoo]

After much messing around, I've finally got it to import *most* of the data, and managed to get it to dump out the problematic rows..

I've imported the text into excel, then imported the excel file, before it was only importing every other line (even though I've specified it, it can't see it half the time!), this time it's importing all but a few problematic rows.

I really don't know why 2005 is being such a pain in the butt, when 2000 was pretty much flawless... *sulk*