none
Converting nchar to int (or numeric/decimal)

    Question

  • Hi ,

      I have a column in my extract table as nchar(3) and in the destination (the same column with diff name ) it is decimal(3,0) .....i tried to use dataconversion transformation.....i even tried to use cast/convert fn's  in the SQL Command (which i use in the "Source Transformation" to get the columns from the extract table).

     

    I tried all the ways i can and still i get the same error..:


    [OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR.
     An OLE DB error has occurred. Error code: 0x80040E07. An OLE
    DB record is available.  Source: "Microsoft OLE DB Provider
    for SQL Server"  Hresult: 0x80040E07  Description: "Error
    converting data type nvarchar to numeric.".

     

    Can we actually do it...?? any help would be appreciated.

     

    thanks

    ravi

    Wednesday, June 20, 2007 3:00 PM

Answers

  • Try a derived column to cast the records.  (Why not make them integers?)

    (DT_I4)[Column]


    Wednesday, June 20, 2007 3:14 PM
    Moderator
  • Works fine for me:

     

    Code Snippet

    CREATE TABLE #temp(test nchar(3))

    INSERT INTO #temp SELECT '111'

    INSERT INTO #temp SELECT '211'

    INSERT INTO #temp SELECT '311'

    SELECT SUM(cast(test as int))FROM #temp

    DROP TABLE #temp

     

    Adamus

    Wednesday, June 20, 2007 3:21 PM

All replies

  • you have non-numeric data in the source column that fails the conversion. Have you tried to configure the error output of the conversion transformation to re-direct error so the 'bad' rows are sent to a diffrent output where youc can inspect them?
    Wednesday, June 20, 2007 3:04 PM
    Moderator
  •  

    Hi,

     No ..No....Its Numeric in the source though the data type is nchar(3).

    The thing is actually the column in the extract table has records as 110,150,160,170 and so on .....but its data type is nchar(3).The reason is this extract table comes from AS400 and while extracting As400 doesnt allow to send  records  if the column in the  extract table on Sql Server is defined other than nchar...so have to go with nchar or nvarchar.

     

    thanks

    ravi

    Wednesday, June 20, 2007 3:10 PM
  • Try a derived column to cast the records.  (Why not make them integers?)

    (DT_I4)[Column]


    Wednesday, June 20, 2007 3:14 PM
    Moderator
  • Works fine for me:

     

    Code Snippet

    CREATE TABLE #temp(test nchar(3))

    INSERT INTO #temp SELECT '111'

    INSERT INTO #temp SELECT '211'

    INSERT INTO #temp SELECT '311'

    SELECT SUM(cast(test as int))FROM #temp

    DROP TABLE #temp

     

    Adamus

    Wednesday, June 20, 2007 3:21 PM
  •  Adamus Turner wrote:

    Works fine for me:

    Code Snippet

    CREATE TABLE #temp(test nchar(3))

    INSERT INTO #temp SELECT '111'

    INSERT INTO #temp SELECT '211'

    INSERT INTO #temp SELECT '311'

    SELECT SUM(cast(test as int))FROM #temp

    DROP TABLE #temp

    Adamus



    Yeah, but Ravi's source is AS400, not SQL Server.  Though, the other option is to stage the data into SQL Server as it is in AS400, and then use a cast statement when pulling records from the staging table as Adamus has illustrated here.
    Wednesday, June 20, 2007 3:24 PM
    Moderator
  •  

    Hey phil,

    I extract the table from AS400 to extract_table on my extract database which is SQL Server DB , from there when i need to make some transformations then my column on the staging is decimal(3,0)....where as on the extract table it is nchar(3).

     I tried similarly by declaring some variables and casting and converting ...it doensnt work..or am i doing some mistake...:

     

    DECLARE @X nchar(3)

    DECLARE @Y int

    SET @X = 'GGG'

    SELECT @X

    SET @Y = Cast(@X as int)

    SELECT @Y

     

     

    thanks for all you views/immediate response's...

     

    let me know if i am doing a mistake here..

     

    thanks,

    ravi

     


     

    Wednesday, June 20, 2007 3:39 PM
  • Then Adamus' example should apply.
    Wednesday, June 20, 2007 3:57 PM
    Moderator
  • Hi ,

       I think i figured what it was i mean i have like around 200,000 records being pulled in to the extract there ....from which were few  records which were nonnumeric in the nchar(3) column i.e apart from '100' , '510' ......there were records with 'MVR'  which was causing all the problem... guess...

     

     

    thanks for the quick responses...

     

    thanks..!!

    Ravi

    Wednesday, June 20, 2007 4:57 PM
  • Then check isnumeric before the cast and set non-numerics to 0:

     

    Code Snippet

    CREATE TABLE #temp(test nchar(3))

    INSERT INTO #temp SELECT '111'

    INSERT INTO #temp SELECT 'AAA'

    INSERT INTO #temp SELECT '311'

     

    SELECT CASE ISNUMERIC(test) WHEN 1 THEN CAST(test as int) ELSE 0 END

    FROM #temp

     

    DROP TABLE #temp

     

    Adamus

    Wednesday, June 20, 2007 5:59 PM
  • Just a tweak.  You may want to set them to NULL, versus a 0.  NULLs would be excluded from any math/aggregation totals you may be creating, such as an average.  Using a 0 would allow that value to be used.  So it's up to you.

    In SSIS, you can use a conditional split to move numeric records to your destination table, and non-numerics to the trash, if you desire.  Or, you can assign new values to the non-numerics.  Up to you.
    Wednesday, June 20, 2007 6:12 PM
    Moderator