Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tcarnahan
Starting Member
23 Posts |
 Posted - 2011-03-02 : 17:37:36
|
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 and would really appreciate someone pointing my in the right direction.  |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-03 : 09:17:04
|
| Sure casting is allowed.If you got an error that 4000 is max allowed, then you were using NVARCHAR, and not VARCHAR.I would cast it to VARCHAR(MAX) however.What I find easiest is to do all of the casting in the SELECT statement and NOT to use a conversion task in the data pump. Also, make sure destination table already exists and that you're casting to the data types expected by the destination table. |
 |
|
|
tcarnahan
Starting Member
23 Posts |
Posted - 2011-03-03 : 13:04:34
|
| Russell ... Basically, I am trying to go from a SS2K NTEXT field to an MS-Access (.mdb) table. I am not sure whether I need for it to go to Text(255) or Memo. Truncation is OK . Whoever designed the SS table chose NTEXT, but rarely do the values get longer than 2000 characters.An article I read suggested using a Derived Column tranform. I tried it but still had problems. The edit dialog for this transform is not easy to understand. I figured how to drag my input field down into the Expression field. But looking at the "Type Cast" functions, I don't know which to use. I tried DT_WSTR length 2000, but that failed. I would have tried DT_STR but didn't know what they wanted in the "CodePage" parameter. If you go to the following link, you will see what I am looking at: http://members.cox.net/tcarnahan/images/SSIS%20Question.jpg (note: you might have to magnify the image to see it clearly)I tried casting in the SELECT statement but it complained that I could not convert unicode to non-unicode, so I assumed that CASTING was not allowed there. Were you talking about the DB Source or DB Destination?Thanks ahead of time for any help you can provide! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-03 : 14:07:58
|
| Ahaaa. I'm no expert on MS Access, but I would think Memo is the right data type. If truncation is really ok, then you can SELECT LEFT(column, 255). |
|
|
|
|
|
|
|
|
SSIS Conversion of SS2K NTEXT into SS2005 DB