November 21, 2008 at 8:51 am
hi guys,
I am trying to convert the string (length = 255) from flat file source to DT_STR (length = 100) for the destination db column (varchar(100) , null) using data conversion transformation but getting the following error message,
The data conversion for column "sAMAccountName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Is there any other way to do this? i guess derived column but i am not good with expressions
Can any one please help me with it ?
Sameer.
November 21, 2008 at 1:48 pm
You might try a derived column using SUBSTRING(yourvalue, 1 100) which will truncate to 100 chars, followed by your conversion to correct string type
Kind Regards, Will
November 24, 2008 at 4:13 am
hi Will,
I tried using the expression,
(DT_STR, 100, 1252)SUBSTRING([sAMAccountName], 1, 100)
But it doesn't work. I am not familiar with the SSIS functions but the resultant string in this case is of same length 255. Can you help?
November 24, 2008 at 12:30 pm
Well, you could try
LTRIM([sAMAccountName], 100)
Followed by a Data Conversion Task to String
i.e. two separate tasks
Kind Regards, Will
November 25, 2008 at 2:43 am
does it not just remove the spaces? according to the syntax i looked at
http://msdn.microsoft.com/en-us/library/ms141781.aspx
LTRIM works only with the DT_WSTR data type. A character_expression argument that is a string literal or a data column with the DT_STR data type is implicitly cast to the DT_WSTR data type before LTRIM performs its operation.
Are you sure it will work? I can see that it will convert the DT_STR to DT_WSTR for which you said - use data conversion afterwards -
Being new to SSIS I have another question. Does converting the length from 255 to 100 only means removing the spaces? I am not sure wether its the right way or not. What if i want to downsize it to 20?
November 25, 2008 at 3:22 am
LTRIM([sAMAccountName], 100) is not right syntax. I guess the right right syntax would be LTRIM([sAMAccountName]), which will remove all the spaces.
e.g String " Hello" would be returned as "Hello"
November 26, 2008 at 5:58 am
Try
LEFT([sAMAccountName], 100)
This will take the leftmost 100 characters
November 26, 2008 at 7:27 am
Thanks Rocky. SUBSTRING does the same, its just more efficient way coz you can set the start point and length. I figured out that my problem is to get the data trough the pipeline i.e from source file to the destination. Which can be resolved by changing the data types in the source file connection manager.
Issue I am facing now is that I am getting some kind of infinite string length (i.e I guess it is exceeding the DT_STR length which is 4000) and because of that I am getting the following error messages,
Error: 0xC02020A1 at DTSTask_DTSDataPumpTask_1, Flat File Source [1]: Data conversion failed. The data conversion for column "memberOf" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at DTSTask_DTSDataPumpTask_1, Flat File Source [1]: The "output column "memberOf" (20619)" failed because truncation occurred, and the truncation row disposition on "output column "memberOf" (20619)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
How can I deal with this? All I am trying to do is to get to through the pipeline and once it is in the derived column task I can use the SUBSTRING to cut the length.
Can any1 please help me with it ?
Rgds,
Sameer.
November 26, 2008 at 9:01 am
two questions
- have you gone in to the advanced settings of the flat file connector and made sure that the length for the input field is long enough ( I know you mentioned 100 but it wasn't clear if you had made that change from the default of 50). And have you tried setting this to soemthing greater than 100?
- is it possible that it's not truncation but the problem is in the code page? ie you have characters from say an asian character set but you're running a standard char set?
Steve.
November 26, 2008 at 9:41 am
Sorry Steve I guess you mixed up both question. I found a way to deal with my previous problem but now i am facing realy wierd issue.
In this case the length of source column is 14301 but DT_STR (max) can only be 8000. The issue is how to get this kind of length through the source file connection - that is where i am getting error.
I heared that I can write VB script to pre-process the file but I have never worked on VB and i hope that there is any other way of doing it.
Its very urgent Can you please help?
November 26, 2008 at 10:00 am
Let me answer my own question here :).
One way of dealing with longer strings is to change the data type to DT_TEXT or DT_NTEXT in advance tab of source file connection manager.
November 26, 2008 at 10:01 am
and you've tried text stream [DT_TEXT] instead of string?
Steve.
November 26, 2008 at 10:04 am
looks like you already got there 🙂
Steve.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply