March 2, 2011 at 3:32 pm
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. 🙂
March 2, 2011 at 3:42 pm
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.
March 2, 2011 at 7:07 pm
Thanks! I will give it a try tomorrow when I get to work.
March 3, 2011 at 7:17 am
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! 🙂
March 3, 2011 at 11:50 am
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