Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2220
  • 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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