none
SSIS Convertion Function from Integer to String

    Question

  • Hello all

    Ho can i convert Integer to String in SSIS Derived column.


    Thanks in Advance
    Raj Lakamana
    Thursday, December 1, 2005 3:31 PM

All replies

  • 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
    Friday, December 2, 2005 5:23 PM
  •  
    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

    Tuesday, December 12, 2006 10:52 PM
  •  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.
    Tuesday, December 12, 2006 11:09 PM
    Moderator
  • 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

    Tuesday, December 12, 2006 11:18 PM
  • Trim is a character function that you can use in the expression of a derived column transformation.  That might help.
    Tuesday, December 12, 2006 11:34 PM
    Moderator
  • You meant  " ltrim(rtrim(    )) " since there is no "trim" .

    It doesn't help..

    Any ideas??

     

    Wednesday, December 13, 2006 3:26 PM
  •  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."
    Wednesday, December 13, 2006 5:04 PM
    Moderator
  • 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

    Wednesday, December 13, 2006 6:11 PM
  •  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?
    Wednesday, December 13, 2006 6:19 PM
    Moderator
  •  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.
    Wednesday, December 13, 2006 6:20 PM
    Moderator
  • 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
     

     

    Wednesday, December 13, 2006 6:44 PM
  •  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?


    Wednesday, December 13, 2006 6:56 PM
    Moderator
  • 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"?
    Wednesday, December 13, 2006 6:57 PM
    Moderator
  •  

    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....

    Wednesday, December 13, 2006 8:05 PM
  • Because it's empty, or because it's NULL?
    Wednesday, December 13, 2006 9:58 PM
    Moderator
  • Not null. I just double checked it. it is equal to ' '.
    Thursday, December 14, 2006 12:57 AM
  •  MoonRiver000 wrote:
    Not null. I just double checked it. it is equal to ' '.


    That -1071607681 is simply telling you that you've got bad data and that it cannot be converted.

    Get rid of the "Data Conversion" transformation and replace it with a "Derived Column" transformation.  Use this as your expression: (DT_I4)column_name

    Redirect the error output to wherever you wish.

    If the column_name column contains an integer, it will convert.  If it doesn't (a '', for instance will not convert because it's not an integer), it will get redirected.

    Then, when you still find yourself redirecting most of the rows, try this as the expression: (column_name == "" || ISNULL(column_name)) ? 0 : (DT_I4)column_name

    If the incoming data is empty or NULL, then set it to zero (it has to be an integer if you're trying to convert to one, right!) else convert the column.  This will still error out any values in column_name that aren't integers to begin with ('ABC', for instance).
    Thursday, December 14, 2006 10:12 PM
    Moderator
  • If I want Char(1)  to be the destination Column datatype, what 's the difference?

    Thanks

    Friday, December 15, 2006 8:56 PM
  •  MoonRiver000 wrote:

    If I want Char(1) to be the destination Column datatype, what 's the difference?

    Thanks



    Do you want the destination to be integer, or char(1)?  Pick one.  If integer, did you try my previous suggestion of using the derived column to account for the various data quality issues?

    If char(1), you can't convert an integer that's bigger than char(1) say, 10. 
     
    I think I must be missing something because I can't seem to follow where the problem lies.  This is a fairly straight-forward conversion provided you follow a few rules...
    Friday, December 15, 2006 9:03 PM
    Moderator
  • Thanks. I knew it won't be bigger than 10 for char(1). I'll try all your suggestion when I get time. What's your office hours?

    Good Weekend!

    Friday, December 15, 2006 9:15 PM
  • Sir,

    I just tested it. some empty or NULL data were still redirected as wrong data even with your solution of (column_name == "" || ISNULL(column_name)) ? 0 : (DT_I4)column_name. I mean it converted part of them instead of all...Why?

    Thank you!

    Sunday, February 25, 2007 1:59 AM
  • ??
    Sunday, February 25, 2007 8:53 PM
  • Sir,

    I just tested it. some empty or NULL data were still redirected as wrong data even with your solution of (column_name == "" || ISNULL(column_name)) ? 0 : (DT_I4)column_name. I mean it converted part of them instead of all...Why?

    Thank you!

    Few Suggestion,

    Increase the size from (DT_I4) to (DT_I8). 

    Check the length of the "column_name" using TSQL (select len(col_name) from tableSample). It should be 0.

    Focus on other column as well.  

    Increase the column size of the destination table.

    0xC020907F

    -1071607681

    DTS_E_TXDATACONVERTSTATIC_CANTCONVERTVALUE

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

    Hope this helps.


    Regards, RSingh


    Wednesday, May 25, 2016 4:42 PM