Need to convert Json format to real data using Script component


  • Hi all,

    I'm a newbie. I'm facing few issue while extracting data from my Source(Quickbooks) to my destination(SQL).

    My data are storing in JSON format(In my destination)

    I need my data to be in a real format not in JSON format. Please assist me <g class="gr_ gr_368 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="368" id="368">to fix this issue</g>.


    Prathap S

    Prathap S

    Thursday, April 20, 2017 6:40 PM

All replies

  • I am having a hard time reading your json from the fragment you have posted here. But would this work for you?

    SET @json =  
    N'[{"BudgetDate" : "2016-04-01", "Amount" : "10000.00",
    "AccountRef":{"value":"8","name":"Discounts Given"}}]'  
    SELECT *  
    FROM OPENJSON(@json)  
      WITH (BudgetDate date 'strict $.BudgetDate',
      Amount money 'strict $.Amount',
       value int '$.AccountRef.value', name varchar(20) '$'

    Thursday, April 20, 2017 7:53 PM
  • If Hilary's answer is no good for you, then there are plenty of examples on the net, just google something like "c# ssis json stream". In essence, you need to declare an object that meets your json spec, then via a streamreader, associate your input to your json object and voila, you can access the components. From there, have an output from your script that represents each row you want
    Thursday, April 20, 2017 8:21 PM
  • Hi Prathap21,

    In addition, please use Inputs and Outputs feature to set appropriate data type in the script component, then write codes to parse the Json data source. Then load the results to SQL Server table.

    For step by step to parse Json data source using script component in a data flow task, please refer to: Parsing JSON Data Sources using SSIS


    Pirlo Zhang

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Friday, April 21, 2017 9:17 AM
  • Hi Prathap,

    Check the commercial COZYROC QuickBooks Source component. It gives you ability to read from both QuickBooks Desktop (Premise) and Online editions. No programming skills are required to use.

    SSIS Tasks Components Scripts Services |

    Friday, April 21, 2017 3:43 PM