Urgent: SSIS data Convertion ??

Urgent: SSIS data Convertion ??

• joshlrogers

  MoonRiver000 wrote:

  You meant " ltrim(rtrim( )) " since there is no "trim" . It doesn't help.. Any ideas

  
  
  There is too a TRIM() function in the derived column transformation. It's under "String Functions."
  
  
  

• Eswans2000

  When using 'Data Convertion' between 'Source table' and 'Destination table' by SSIS. Some data could not be transfered to 'Int' or char(1) from Varchar(200). In fact, 98% of them should be int or char(1) or null. I tried to 'redirect row' the rest 2% to a wrong table. However, all data went to the wrong table during 'Data Convertion' and nothing went to the destination table.

  I checked the ErrorCode is :
  

  0xC020907F

  -1071607681

  DTS_E_TXDATACONVERTSTATIC_CANTCONVERTVALUE

  The data value cannot be converted for reasons other than sign mismatch or data overflow.

   I already used  ltrim(rtrim()) in the source table. I checked the wrong columns. Most data are empty... At least those data should go through and the rest go to wrong table

  Why and how can I modify it  

   

  How can I get the right person to figure it out immediately

  Thanks

   

   

  
  
  

• Abbasi

   

  When  using 'Data Convertion' between 'Source table' and 'Destination table' by SSIS, some data could not be transfered to 'Int' or char(1) from Varchar(200). In fact, 98% of them should be int or char(1) or null.  I tried to 'redirect  row' the rest 2% to a wrong table. However, all data went to the wrong table during 'Data Convertion'  and nothing went to the destination table.

   

  I checked the ErrorCode is :
   

  0xC020907F

  -1071607681

  DTS_E_TXDATACONVERTSTATIC_CANTCONVERTVALUE

  The data value cannot be converted for reasons other than sign mismatch or data overflow.

   

   

   

  Why and how can I modify it

   

  Any related example

   

  Thanks,

  Moonriver

  
  

  
  
  

• Lightening

  Sorry,I don't understand. Could you tell me in detail

  By the way, I found most of the wrong message is '

  The data value cannot be converted for reasons other than sign mismatch or data overflow.

  '

  What is that

  Thanks

  
  
  

• WelshBird

  MoonRiver000 wrote:

  When using 'Data Convertion' between 'Source table' and 'Destination table' by SSIS. Some data could not be transfered to 'Int' or char(1) from Varchar(200). In fact, 98% of them should be int or char(1) or null. I tried to 'redirect row' the rest 5% to a wrong table. However, all data went to the wrong table during 'Data Convertion' and nothing went to the destination table. I checked the ErrorCode is : 0xC020907F -1071607681 DTS_E_TXDATACONVERTSTATIC_CANTCONVERTVALUE The data value cannot be converted for reasons other than sign mismatch or data overflow. Why and how can I modify it Thanks, Moonriver

  
  
  Ah, well, you can try trimming the field first in a derived column, and then in another derived column try your conversion.
  
  
  

• rv_saraswathi

  Yes, you mentioned 'derived column '. But I already used ltrim(rtrim( )) before 'Data Convertion'. The same problems happened.

   

  What is "The data value cannot be converted for reasons other than sign mismatch or data overflow."

   

   

  Thanks

  
  
  

• SP534

  Is the source table in SQL Server Yes

  the destination table in SQL Server Yes

  Are you sure the data doesn't have special characters in it Did not find

  What code set (1252 ) are you using in your source connection 1252 except datetime and int column for 0. I used SQL Command in the source table.

  Just because when you look at the data it looks fine, doesn't mean it really is.
  
  Yes, assuming your data is convertible, and is not corrupt, SOME data should flow through. But then, I ask again, how are you converting the data Are you just letting the destination convert it, or do you have a Data Conversion task A derived column task using 'Data Convertion' between 'Source table' and 'Destination table' by SSIS

  try selecting your column in the source like this:
  
  select column, len(ltrim(rtrim(column))) from table: Checked the Error rows with the exact ErrorColumn: Column looks empty and len(ltrim(rtrim(column))) is 0
  Does len() show the correct length for the data you see in "column" Mostly, Error happened because of the empty....

  
  
  

• rockworld

  MoonRiver000 wrote:

  Yes, you mentioned 'derived column '. But I already used ltrim(rtrim( )) before 'Data Convertion'. The same problems happened. What is "The data value cannot be converted for reasons other than sign mismatch or data overflow." Thanks

  
  
  How are you converting the data What is the source column data type What is the destination column type Are there NULLs in the data
  
  
  

• Martin Masse

  Trim is a character function that you can use in the expression of a derived column transformation. That might help.
  
  
  

• matsushita

  You meant " ltrim(rtrim( )) " since there is no "trim" .

  It doesn't help..

  Any ideas

  
  
  

• Polarbear541

  try selecting your column in the source like this:
  
  select column, len(ltrim(rtrim(column))) from table
  
  Does len() show the correct length for the data you see in "column"
  
  
  

• Frank104

  I am assuming that you have already tried casting etc.
  
  I can suggest some workaround...
  
  What is the source of the Integer
  If it is coming from a table, you can change the "Data access mode" from "table or view" to "Sql command" and format your command like "SELECT col1, col2, convert(nvarchar(20), my_integer) as col3 from src_table..." 
  
  If it is coming via lookup, do the same by modifying the query in the advanced tab.
  
  HTH,
  Nitesh
  
  

• MA2005

  MoonRiver000 wrote:

  What is "The data value cannot be converted for reasons other than sign mismatch or data overflow." Thanks

  
  
  Corrupt data, perhaps. Maybe there's a special character that it doesn't know how to handle.
  
  
  

• Bucket

  MoonRiver000 wrote:

  When using 'Data Convertion' between 'Source table' and 'Destination table' by SSIS. Some data could not be transfered to 'Int' or char(1) from Varchar(200). In fact, 98% of them should be int or char(1) or null. I tried to 'redirect row' the rest 2% to a wrong table. However, all data went to the wrong table during 'Data Convertion' and nothing went to the destination table. I checked the ErrorCode is : 0xC020907F -1071607681 DTS_E_TXDATACONVERTSTATIC_CANTCONVERTVALUE The data value cannot be converted for reasons other than sign mismatch or data overflow. I already used ltrim(rtrim()) in the source table. I checked the wrong columns. Most data are empty... At least those data should go through and the rest go to wrong table Why and how can I modify it How can I get the right person to figure it out immediately Thanks

  
  
  For Pete's sake... I've already read that. No need to repost. You likely have corrupt data. Is the source table in SQL Server Is the destination table in SQL Server Are you sure the data doesn't have special characters in it What code set (1252 ) are you using in your source connection Just because when you look at the data it looks fine, doesn't mean it really is.
  
  Yes, assuming your data is convertible, and is not corrupt, SOME data should flow through. But then, I ask again, how are you converting the data Are you just letting the destination convert it, or do you have a Data Conversion task A derived column task
  
  
  
  
  
• Urgent: SSIS data Convertion ??