Conversion Error SS2K NTEXT to SS2005

  • 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. 🙂

  • 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.

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

  • 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! 🙂

  • 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?

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

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