Conversion Error SS2K NTEXT to SS2005

Question

Post reply

Conversion Error SS2K NTEXT to SS2005

Tom Carnahan

SSCommitted

Points: 1788
More actions
March 2, 2011 at 3:32 pm

#246414

SSIS is devilishly frustrating when you have been doing DTS most of your time.
I have a SS2K database and I am using SSIS to extract the data from a table in the first database and import it to the second. I have one SS2k table with several NTEXT fields that I have been having difficulty trying to convert.
I tried CASTING the ntext in my query that extracts the data from the first database, but I learned that is not allowed. Then I tried a straight "COPY COLUMN" ... that did not work. Then I read that you can use "DATA CONVERSION". I have been trying it, with no luck.
For one, I am not sure how to form the "expression". Originally I thought I could type in "CAST(<old field> as varchar(8000). It complained that I could only use 4000. I experimented with pulling the column down to the expression block and pulling a "type cast" into the block with it. That seemed to work until I ran my package.
Question: 1) Am I going about this the correct way?
2) If so, what "type cast" function should I use?
Extremely frustrated :crazy: and would really appreciate someone pointing my in the right direction. 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

Text = varchar

Ntext = Nvarchar

Try casting to nvarchar(4000).

edit: Or if you're doing the conversion in SSIS, make sure you're converting to double-byte chars and not single-byte.

Tom Carnahan SSCommitted Points: 1788 More actions · Answer 2

Thanks! I will give it a try tomorrow when I get to work.

Tom Carnahan SSCommitted Points: 1788 More actions · Answer 3

I am trying to use the SSIS Derived Column transformation to convert FROM NTEXT.

Here is an image of what I am seeing:

Basically, I am trying to go from SS Ntext to MS-Access ??? data type (memo? text (255)? )

Thanks for any help you can give! 🙂

Tom Carnahan SSCommitted Points: 1788 More actions · Answer 4

Someone suggested that I forget the Derived Column and Data Conversion transforms for directly casting in the SELECT statement. I did that and it looks like this:

SELECT LEFT(CAST(HTML_Original as nvarchar(4000)) ,255)

AS [<my Former_NTEXT_Column>]

FROM <my SS2K table>

I did it this way because I am trying to port this field to an MS-Access (.mdb) table with a field typed as TEXT(255). In this case, truncation of the field is not an issue.

Here is a link to what I am seeing (magnify your screen to 200% to see clearly):

http://members.cox.net/tcarnahan/images/SSIS_Data_Conversion_Problem.JPG

This link did not work for me so I am also attaching an image file.

As you can see in the outcome, it didn't work. Here are the error messages:

[Destination - Access [774]] Error: An OLE DB error has occurred. Error code: 0x80040E21.
[Destination - Access [774]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
[DTS.Pipeline] Error: component "Destination - Access" (774) failed the pre-execute phase and returned error code 0xC0202025.

Any suggestions?