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 |
|
Brittney10
Posting Yak Master
154 Posts |
 Posted - 2013-03-08 : 15:32:08
|
| I have a column that contains 8000+ characters, so I've defined the column as DT_Text in my flat file connection. My issue is that I'm trying to get the field into a nvarchar(4000) AND if the column is greater than 4000 characters, I need to get the rightmost 4000 characters only and cut off the rest. When I define the column as DT_STR 4000 (or even 8000) it seems to grab the 4000 (or 8000 characters) at random from the text string.Any suggestions would be helpful. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-09 : 01:18:02
|
| why not apply a derived column transform and use an expression likeSUBSTRING(column,1,4000)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Brittney10
Posting Yak Master
154 Posts |
Posted - 2013-03-11 : 10:18:43
|
| When I apply SUBSTRING(Column, 1,4000) in a derived column...I get the error - "The function SUBSTRING does not support the data type DT_Text for parameter number 1." |
|
|
|
Brittney10
Posting Yak Master
154 Posts |
Posted - 2013-03-11 : 11:19:58
|
| The assumption is that since SUBSTRING can be used on TEXT data type columns in T-SQL then it can be used on TEXT data type columns in SSIS - this assumption is incorrect. I will have to stage the data in SQL and scrub the data there. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-11 : 12:53:33
|
| then cast it and do substringSUBSTRING((DT_WSTR,4000)column,1,4000)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
DT_TEXT