locked
Lookup no match to json RRS feed

  • Question

  • Hi,



    Is any way to redirect the rows from lookup transformation to json output without using newtonsoft? And from json to sql table having just 1 column to store json output. 



    Please suggest.

    Thanks
    Tuesday, January 29, 2019 4:54 PM

Answers

  • Hi Papil1,

    I am glad that you made a progress.

    I expanded on the original proposal to generate JSON on the row level. Here it:

    DECLARE @tbl TABLE (ID INT, City VARCHAR(50), JSON_Column VARCHAR(1024) NULL); INSERT INTO @tbl (ID, City) VALUES (1, 'Miami') , (2, 'Orlando') , (3, 'Tampa');

    -- before SELECT * FROM @tbl; DECLARE @RowCount INT = (SELECT COUNT(*) FROM @tbl); WHILE @RowCount > 0 BEGIN UPDATE @tbl SET JSON_Column = (SELECT * -- list here all columns that need to be in JSON FROM @tbl ORDER BY ID DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY FOR JSON PATH, ROOT('Cities')) WHERE ID = (SELECT ID FROM @tbl ORDER BY ID DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY); SET @RowCount -= 1; END -- after SELECT * FROM @tbl;


    Outcome:
    ID	City	JSON_Column
    1	Miami	{"Cities":[{"ID":1,"City":"Miami"}]}
    2	Orlando	{"Cities":[{"ID":2,"City":"Orlando"}]}
    3	Tampa	{"Cities":[{"ID":3,"City":"Tampa"}]}


    • Edited by Yitzhak Khabinsky Wednesday, February 6, 2019 6:32 AM
    • Marked as answer by Papil1 Friday, February 8, 2019 8:04 PM
    Wednesday, February 6, 2019 6:30 AM

All replies

  • Hi Papil1,

    SQL Server 2016 added support for JSON.

    What version of your MS SQL Server?

    Tuesday, January 29, 2019 5:01 PM
  • version is 2017.
    Tuesday, January 29, 2019 5:08 PM
  • Hi Papil1,

    You may load your normal SSIS output into a temporary table and after that convert it into JSON.

    Please see below:

    DECLARE @tbl TABLE (ID INT, City VARCHAR(50)); INSERT INTO @tbl (ID, City) VALUES (1, 'Miami') , (2, 'Orlando') , (3, 'Tampa'); SELECT * FROM @tbl;

    -- INSERT INTO tableName SELECT * FROM @tbl FOR JSON PATH;


    Output:
    ID	City
    1	Miami
    2	Orlando
    3	Tampa
    
    
    JSON_F52E2B61-18A1-11d1-B105-00805F49916B
    [{"ID":1,"City":"Miami"},{"ID":2,"City":"Orlando"},{"ID":3,"City":"Tampa"}]



    Tuesday, January 29, 2019 5:16 PM
  • I have lookup transformation and the no match rows from that- i want to put into json. Not sure how above temp table will help with that?
    Tuesday, January 29, 2019 5:20 PM
  • When you are happy with  the JSON just uncomment one single line:

    -- INSERT INTO tableName

    And it will insert the desired JSON into its final destination.

    Tuesday, January 29, 2019 5:23 PM
  • Could you explain little more like which transformation i use in SSIS to perform the above steps? I want to do this step after "lookup" no match.

    Tuesday, January 29, 2019 6:34 PM
  • Hi Papil1,

    You would need to do the following:

    1. Use OLE DB Destination to insert Lookup Task no-match output data into a temp table.
    2. Use Execute SQL Task to call a stored procedure to convert temp table data into JSON format and insert it into a real destination table.




    Tuesday, January 29, 2019 6:45 PM
  • Hi Papil, 

    I think you can use Script Component to concatenate a JSON string, and use OLE DB Command transformation to insert the value into target table. 

    Hope these links help: 

    SSIS Script Component as Transformation

    Using OLE DB Command to insert new records


    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, January 30, 2019 6:18 AM
  • I am getting error using temp table. It says destination table not provided. I have set the retainsameconnection to True and Delay Validation to true but same issue. I was using below link to create temp table-

    http://www.techbrothersit.com/2014/04/ssis-how-to-create-use-temp-table-in.html

    Please help.I have little idea about C# so if someone can help with code that would be helpful. Thanks

    Tuesday, February 5, 2019 8:27 PM
  • Hi Papil1,

    • Just create manually permanent temp/staging DB table for the Lookup Task no-match output data.
    • I already proposed you how to get JSON in the DB table earlier in this post.
      It is one single SQL statement.
      You don't need any c#.

    Tuesday, February 5, 2019 8:31 PM
  • I dont want to create extra tables for this task. Can a table variable help for the same?
    Tuesday, February 5, 2019 8:39 PM
  • Hi Papil1,

    Here is your statements verbatim:

    • API: "...json output without using newtonsoft…"
    • c#: "...I have little idea about C#..."
    • DB: "... I dont want to create extra tables for this task..."

    Your solution is very simple, use one single real DB table for the Lookup Task no-match output data.

    And convert that table data into JSON format by using one single SQL statement.


    Tuesday, February 5, 2019 8:44 PM
  • Hi Papil1,

    • Just create manually permanent temp/staging DB table for the Lookup Task no-match output data.
    • I already proposed you how to get JSON in the DB table earlier in this post.
      It is one single SQL statement.
      You don't need any c#.

    json is getting big and its splitting into multiple rows in destination table. dest. column is set to varchar max.

    Any thoughts how can i store the json row wise?

    Wednesday, February 6, 2019 2:15 AM
  • Hi Papil1,

    I am glad that you made a progress.

    I expanded on the original proposal to generate JSON on the row level. Here it:

    DECLARE @tbl TABLE (ID INT, City VARCHAR(50), JSON_Column VARCHAR(1024) NULL); INSERT INTO @tbl (ID, City) VALUES (1, 'Miami') , (2, 'Orlando') , (3, 'Tampa');

    -- before SELECT * FROM @tbl; DECLARE @RowCount INT = (SELECT COUNT(*) FROM @tbl); WHILE @RowCount > 0 BEGIN UPDATE @tbl SET JSON_Column = (SELECT * -- list here all columns that need to be in JSON FROM @tbl ORDER BY ID DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY FOR JSON PATH, ROOT('Cities')) WHERE ID = (SELECT ID FROM @tbl ORDER BY ID DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY); SET @RowCount -= 1; END -- after SELECT * FROM @tbl;


    Outcome:
    ID	City	JSON_Column
    1	Miami	{"Cities":[{"ID":1,"City":"Miami"}]}
    2	Orlando	{"Cities":[{"ID":2,"City":"Orlando"}]}
    3	Tampa	{"Cities":[{"ID":3,"City":"Tampa"}]}


    • Edited by Yitzhak Khabinsky Wednesday, February 6, 2019 6:32 AM
    • Marked as answer by Papil1 Friday, February 8, 2019 8:04 PM
    Wednesday, February 6, 2019 6:30 AM