none
Need to convert Json format to real data using Script component

    Question

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

    Regards,

    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?

    DECLARE @json NVARCHAR(MAX)
    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) '$.AccountRef.name'
      )
    

    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

    Regards,

    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 MSDNFSF@microsoft.com.

    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 | http://wwww.cozyroc.com/

    Friday, April 21, 2017 3:43 PM
  • Hi Prathap21,

    If your issue is solved please mark the appropriate answer as Mark as answer. This will help other members to find a solution if they face the same issue.

    Thanks for your understanding and support.

    If you still have any questions, please feel free to ask.

    Best Regards,
    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 MSDNFSF@microsoft.com.

    Wednesday, April 26, 2017 8:49 AM