• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2295
  • Last Modified:

Parse Json structure using SSIS and load into table in sql server

I have a table with a column which has data in JSON format

Example:
{"id":0,"name":null,"type":"Address","value":"\"02421\"","count":0}

I need to parse this and store the values in different columns in sql server using SSIS.

Can you please give some suggestions for parsing json structure using SSIS
0
MRPT
Asked:
MRPT
1 Solution
 
DcpKingCommented:
How do you parse it yourself?
Well, you start at a { and end at a }
Inside that there's a number of name-value pairs - here there are
          "id":0,"name":null,"type":"Address","value":"\"02421\"","count":0
which come out as
          "id":0,
          "name":null,
          "type":"Address",
          "value":"\"02421\"",
          "count":0
So you see that each data element (name-value pair) has a name (withing quotes), a colon, the value, and a comma (or the finishing brace). A value can be just what it is, or a string in quotes, and you'll have to cope with the escaped internal quote, but that won't be too hard.

I'd suggest that you write code to fill a table of three columns (first being an identity col to retain the sequence) and then fill your storage table.

hth

Mike
0
 
MRPTAuthor Commented:
Thank You
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now