Post reply

get required format values in ssis

  • February 2, 2017 at 12:18 am

    #321232

    Hi I have doubt in ssis

    source : emp
    id |currency
    1 |<?xml version="1.0" encoding="utf-16"?><double>0.7915</double>
    2 |<?xml version="1.0" encoding="utf-16"?><double>-1</double>

    based on this table I want output like below

    target : empdest

    id | currency
    1 | 0.79152  
    2 |     -1

    I tried like below in derived column expression

    SUBSTRING(currency,50,(FINDSTRING(currency),"</",1)))SUBSTRING(currency,50,(FINDSTRING(currency),"</",1)))

    above expression not give expected result .

    please tell me how to write expression in ssis to solve this issue in ssis[/code]target : empdestI tried like below in derived column expressionabove expression not give expected result .please tell me how to write expression in ssis to solve this issue in ssis

  • February 2, 2017 at 2:28 am

    #1926269

    How are you getting the value from your table. Could you not instead retreive the value straight from your SQL table? For example:
    CREATE TABLE #Sample (ID INT IDENTITY(1,1),
            XMLValue XML)
    GO

    INSERT INTO #Sample (XMLValue)
    VALUES ('<?xml version="1.0"?><double>0.7915</double>'),
       ('<?xml version="1.0"?><double>-1</double>');
    GO

    SELECT *,
       XMLValue.value('/double[1]', 'varchar(50)') AS Currency
    FROM #Sample;

    GO
    DROP TABLE #Sample

    NOte that however much you try, ID one is not going to have a value of 0.7952. That precision isn't in your XML, so has already been lost. You can't gain back lost precision.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • February 2, 2017 at 3:32 am

    #1926281

    Hi THom A, above given information is helpful but my source data  some times comes with table some times data came with text files.
    as per given information is helpfully with using of table.I need same  logic using  expression in derived column because of some time source data comes text files. please help me how to write expression to resolve this issue in ssis.

  • February 2, 2017 at 3:42 am

    #1926282

    If you have to do it with an expression, this should work:
    SUBSTRING( @[User::XML], FINDSTRING( @[User::XML],"<double>", 1) + 8, FINDSTRING( @[User::XML],"</double>", 1) - (FINDSTRING( @[User::XML],"<double>", 1) + 8))
    Where @[User::XML] is the value of your XML string.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply