![\"logo](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png\")
<\/a>Hello, this time we will show how to parse JSON in SQL Server 2014 or other versions. we will use a simple tool easy to use and we do not need programming knowledge.<\/p>\n<\/h2>\n<\/h2>\nSolution<\/h2>\n
Solution<\/h2>\n
In this article we will learn how to parse JSON in\u00a0SQL Server using a Free tool that can be installed with SSIS that can be downloaded from the internet. If you do not have any experience in SSIS, do not worry. This is a great opportunity to learn. This article will not take long.<\/p>\n
Requirement<\/h2>\n\n- First, make sure that you have SQL Server.<\/li>\n
- Also, check to install SSIS (it is included with the SQL Server installer).<\/li>\n
- And finally, make sure to install\u00a0SSDT<\/a><\/li>\n<\/ul>\n
Getting Started<\/h2>\n
SQL Server 2016 and later versions include JSON support. You can work with JSON in T-SQL like you did with XML. However, it requires some programming knowledge to work with it and it is not simple if you do not have JSON or XML experience.<\/p>\n
In this example, we will show 2 examples.<\/p>\n
\n- First, we will show a simple example of how to parse a JSON file and get values in variables.<\/li>\n
- Secondly, we will show how to get JSON values from a variable and store the variable values in SQL Server.<\/li>\n<\/ol>\n
1. Example to get values from JSON into variables<\/h4>\n
Let’s start the first demo<\/p>\n
\n- First of all, we will first download a free tool named SSIS JSON Parser Task.<\/a> This task will help us to parse the JSON data. Once installed, drag and drop to the design pane. With the installer, you will also have another free tool. The ZS Logging Task\n
![\"ZS](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/zs-json-parser-task.jpg\")
<\/a>Drag and drop json parser task<\/p><\/div><\/li>\n
- Secondly, we will create two SSIS variables first that will store the JSON values. In SSDT, go to the SSIS<\/strong> menu and select Variables.\u00a0<\/strong>Create two variables. CustomerID and CustomerName of type String:\n
![\"Customerid](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/SSIS-variables-json.jpg\")
<\/a>ssis variable<\/p><\/div><\/li>\n
- Also, we will use the following JSON text:\n
{\r\n\"CustomerID\": \"ALFKI\",\r\n\"CustomerName\": \"Alfredo Koli\",\r\n\"Age\": 55,\r\n\"LocationInfo\": {\"City\":\"Newyork\", \"State\":\"NY\", \"Zip\":\"23234\"},\r\n\"OrderIDList\": [111,222,333,444,555]\r\n}\r\n<\/pre>\n<\/li>\n - Using the ZS JSON parser task, map CustomerID and CustomerName to the user variables created before:\n
![\"Map](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-map-json-variables.jpg\")
<\/a>Mapping JSON values into variables<\/p><\/div><\/li>\n
- For demonstration\u00a0purposes, the\u00a0age will be set to LOG-TO-OUTPUT. This means that the values will be displayed in the SSIS Output pane:\n
![\"Log](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/log-ssis-variable-output-value.jpg\")
<\/a>Send value to log in SSIS<\/p><\/div><\/li>\n
- In addition, we will verify the values of the variables. To do that, we will use another free tool.\u00a0 The ZS Logging Task that comes with the ZS JSON Parser task. This tool can display the variable values in the SSIS output (ExecutionLog) in a\u00a0MessageBox a file or a\u00a0variable. In this example, we will show the variable values in the SSIS output:\n
![\"Send](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-check-variable-values-log.jpg\")
<\/a>Set values to ExecutionLog<\/p><\/div><\/li>\n
- Finally, run the packages and check the output. If you cannot see the output, go to View<\/strong> in the menu and select Output.<\/strong><\/strong>\n
![\"SSIS](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-output-variable-values.jpg\")
<\/a>Check SSIS variables in log<\/p><\/div>\n
Also, the JSON Parser task shows that the age is 55. You can verify this value in the JSON data in step 3. Also, the Logging task is showing the CustomerId and the CustomerName values that can be verified and compared with the step 3 that contains the JSON objects and arrays.<\/li>\n<\/ol>\n
2. Second example. Storing and showing how to parse JSON in SQL Server<\/h3>\n
In the first example, we learned how to parse JSON values and store them in SSIS variables and how to send values to the output. In this new example, we will show how to parse JSON values in SQL Server.<\/p>\n
\n- First of all, will be to create a simple table in SQL Server to store JSON values:\n
CREATE TABLE [dbo].[book](\r\n[author] [nchar](50) NULL,\r\n[isbn] [nchar](20) NULL,\r\n[price] [decimal](18, 0) NULL,\r\n[category] [nchar](20) NULL,\r\n[id] [smallint] IDENTITY(1,1) NOT NULL,\r\nCONSTRAINT [PK_book] PRIMARY KEY CLUSTERED \r\n(\r\n[id] ASC\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, \r\nALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\r\n) ON [PRIMARY]\r\nGO<\/pre>\n<\/li>\n
- Secondly, we will use the following JSON data.\n
{ \"store\": {\r\n\"book\": [\r\n{ \"category\": \"reference\",\r\n\"author\": \"Nigel Rees\",\r\n\"title\": \"Sayings of the Century\",\r\n\"price\": 8.95\r\n},\r\n{ \"category\": \"fiction\",\r\n\"author\": \"Evelyn Waugh\",\r\n\"title\": \"Sword of Honour\",\r\n\"price\": 12.99\r\n},\r\n{ \"category\": \"fiction\",\r\n\"author\": \"Herman Melville\",\r\n\"title\": \"Moby Dick\",\r\n\"isbn\": \"0-553-21311-3\",\r\n\"price\": 8.99\r\n},\r\n{ \"category\": \"fiction\",\r\n\"author\": \"J. R. R. Tolkien\",\r\n\"title\": \"The Lord of the Rings\",\r\n\"isbn\": \"0-395-19395-8\",\r\n\"price\": 22.99\r\n}\r\n],\r\n\"bicycle\": {\r\n\"color\": \"red\",\r\n\"price\": 19.95\r\n}\r\n}\r\n}<\/pre>\n <\/li>\n
- Also, will create the variables first. The inputjson will contain the JSON data from the previous step:\n
![\"ssis](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/variables-json-file.jpg\")
<\/a>ssis variables<\/p><\/div><\/li>\n
- Once it is done,\u00a0 will be to insert a variable this time. We will use the\u00a0Insert Variable option and insert the inputjson variable that contains the JSON values. We will also use a filter to show just the 3rd book information (the first element index is 0). Path filters in JSON are out of the scope of this article, but you can read a nice article about the topic here<\/a>. Press the Parse Field to parse the values. Finally, map the author, category, isbn and price variables:\n
![\"Filter](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/json-parser-path-filter-ssis.jpg\")
<\/a>Using filters<\/p><\/div><\/li>\n
- After that,\u00a0 drag and drop the Execute SQL Task and join it with the JSON Parser to receive the variable values. Specify a connection with the server name and database name where the table of step 1 was created. In SQLStatement, write the following query:\n
INSERT INTO [dbo].[book]\r\n(\r\n[author],\r\n[isbn],\r\n[price],\r\n[category]\r\n)\r\nVALUES\r\n(?,?,?,?\r\n)<\/pre>\nThis query will insert the 4 SSIS parameters in SQL Server:<\/p>\n
![\"Insert](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/execute-sql-task-ssis.jpg\")
<\/a>Execute SQL Task<\/p><\/div><\/li>\n
- Also, in parameter mapping, specify the variable name, direction, data type and Paremeter name:\n
![\"Map](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/execute-sql-task-parameter-mapping.jpg\")
<\/a>Map variables into SQL<\/p><\/div><\/li>\n
- Finally, run the package and you will be able to see the value stored in SQL Server book table:\n
![\"check](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/result.jpg\")
<\/a>Check results<\/p><\/div><\/li>\n<\/ol>\n
Conclusion<\/h2>\n
As a conclusion, we can say that it is possible to parse JSON in SQL Server using SSIS additional tasks and it does not require to know T-SQL or code to get JSON values.<\/p>\n
References<\/h2>\n
Finally, you can use the following links for more information about REST API and SQL Server:<\/p>\n
\n- JSON data in SQL Server<\/a><\/li>\n
- JSONPath – XPath for JSON<\/a><\/li>\n<\/ul>\n
keywords: parse JSON in SQL Server<\/p>\n","protected":false},"excerpt":{"rendered":"
Parse JSON using SQL Server 2014 or lower Hello, this time we will show how to parse JSON in SQL Server 2014 or other versions. we will use a simple tool easy to use and we do not need programming knowledge. Solution In this article we will learn how to parse JSON in\u00a0SQL Server using […]<\/p>\n","protected":false},"author":5,"featured_media":3132,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[265],"tags":[],"yoast_head":"\r\n
How to parse JSON in SQL Server 2014, 2012 and 2008<\/title>\r\n<meta name=\"description\" content=\"In this article, we will show how to parse JSON in SQL Server 2014, 2012 and 2008 using SSIS and how to store the values in SSIS variable.\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to parse JSON in SQL Server 2014, 2012 and 2008\" \/>\r\n<meta property=\"og:description\" content=\"In this article, we will show how to parse JSON in SQL Server 2014, 2012 and 2008 using SSIS and how to store the values in SSIS variable.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:published_time\" content=\"2018-03-23T23:34:44+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2018-04-10T23:46:25+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"100\" \/>\r\n\t<meta property=\"og:image:height\" content=\"100\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys Team\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/\",\"url\":\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/\",\"name\":\"How to parse JSON in SQL Server 2014, 2012 and 2008\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png\",\"datePublished\":\"2018-03-23T23:34:44+00:00\",\"dateModified\":\"2018-04-10T23:46:25+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/91b041e2dcf7ece5f068893c1a68ac6e\"},\"description\":\"In this article, we will show how to parse JSON in SQL Server 2014, 2012 and 2008 using SSIS and how to store the values in SSIS variable.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png\",\"width\":100,\"height\":100},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Parse JSON using SQL Server 2014 or lower\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/91b041e2dcf7ece5f068893c1a68ac6e\",\"name\":\"ZappySys Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g\",\"caption\":\"ZappySys Team\"},\"sameAs\":[\"https:\/\/zappysys.com\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/dcalbimonte\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to parse JSON in SQL Server 2014, 2012 and 2008","description":"In this article, we will show how to parse JSON in SQL Server 2014, 2012 and 2008 using SSIS and how to store the values in SSIS variable.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"How to parse JSON in SQL Server 2014, 2012 and 2008","og_description":"In this article, we will show how to parse JSON in SQL Server 2014, 2012 and 2008 using SSIS and how to store the values in SSIS variable.","og_url":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/","og_site_name":"ZappySys Blog","article_published_time":"2018-03-23T23:34:44+00:00","article_modified_time":"2018-04-10T23:46:25+00:00","og_image":[{"width":100,"height":100,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png","type":"image\/png"}],"author":"ZappySys Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"ZappySys Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/","url":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/","name":"How to parse JSON in SQL Server 2014, 2012 and 2008","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png","datePublished":"2018-03-23T23:34:44+00:00","dateModified":"2018-04-10T23:46:25+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/91b041e2dcf7ece5f068893c1a68ac6e"},"description":"In this article, we will show how to parse JSON in SQL Server 2014, 2012 and 2008 using SSIS and how to store the values in SSIS variable.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/parse-json-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png","width":100,"height":100},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Parse JSON using SQL Server 2014 or lower"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/91b041e2dcf7ece5f068893c1a68ac6e","name":"ZappySys Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g","caption":"ZappySys Team"},"sameAs":["https:\/\/zappysys.com"],"url":"https:\/\/zappysys.com\/blog\/author\/dcalbimonte\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/3126"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=3126"}],"version-history":[{"count":20,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/3126\/revisions"}],"predecessor-version":[{"id":3183,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/3126\/revisions\/3183"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/3132"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=3126"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=3126"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=3126"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
Getting Started<\/h2>\n
SQL Server 2016 and later versions include JSON support. You can work with JSON in T-SQL like you did with XML. However, it requires some programming knowledge to work with it and it is not simple if you do not have JSON or XML experience.<\/p>\n
In this example, we will show 2 examples.<\/p>\n
- \n
- First, we will show a simple example of how to parse a JSON file and get values in variables.<\/li>\n
- Secondly, we will show how to get JSON values from a variable and store the variable values in SQL Server.<\/li>\n<\/ol>\n
1. Example to get values from JSON into variables<\/h4>\n
Let’s start the first demo<\/p>\n
- \n
- First of all, we will first download a free tool named SSIS JSON Parser Task.<\/a> This task will help us to parse the JSON data. Once installed, drag and drop to the design pane. With the installer, you will also have another free tool. The ZS Logging Task\n<\/a>
Drag and drop json parser task<\/p><\/div><\/li>\n
- Secondly, we will create two SSIS variables first that will store the JSON values. In SSDT, go to the SSIS<\/strong> menu and select Variables.\u00a0<\/strong>Create two variables. CustomerID and CustomerName of type String:\n
<\/a>ssis variable<\/p><\/div><\/li>\n
- Also, we will use the following JSON text:\n
{\r\n\"CustomerID\": \"ALFKI\",\r\n\"CustomerName\": \"Alfredo Koli\",\r\n\"Age\": 55,\r\n\"LocationInfo\": {\"City\":\"Newyork\", \"State\":\"NY\", \"Zip\":\"23234\"},\r\n\"OrderIDList\": [111,222,333,444,555]\r\n}\r\n<\/pre>\n<\/li>\n- Using the ZS JSON parser task, map CustomerID and CustomerName to the user variables created before:\n
<\/a>Mapping JSON values into variables<\/p><\/div><\/li>\n
- For demonstration\u00a0purposes, the\u00a0age will be set to LOG-TO-OUTPUT. This means that the values will be displayed in the SSIS Output pane:\n
<\/a>Send value to log in SSIS<\/p><\/div><\/li>\n
- In addition, we will verify the values of the variables. To do that, we will use another free tool.\u00a0 The ZS Logging Task that comes with the ZS JSON Parser task. This tool can display the variable values in the SSIS output (ExecutionLog) in a\u00a0MessageBox a file or a\u00a0variable. In this example, we will show the variable values in the SSIS output:\n
<\/a>Set values to ExecutionLog<\/p><\/div><\/li>\n
- Finally, run the packages and check the output. If you cannot see the output, go to View<\/strong> in the menu and select Output.<\/strong><\/strong>\n
<\/a>Check SSIS variables in log<\/p><\/div>\n
Also, the JSON Parser task shows that the age is 55. You can verify this value in the JSON data in step 3. Also, the Logging task is showing the CustomerId and the CustomerName values that can be verified and compared with the step 3 that contains the JSON objects and arrays.<\/li>\n<\/ol>\n
2. Second example. Storing and showing how to parse JSON in SQL Server<\/h3>\n
In the first example, we learned how to parse JSON values and store them in SSIS variables and how to send values to the output. In this new example, we will show how to parse JSON values in SQL Server.<\/p>\n
- \n
- First of all, will be to create a simple table in SQL Server to store JSON values:\n
CREATE TABLE [dbo].[book](\r\n[author] [nchar](50) NULL,\r\n[isbn] [nchar](20) NULL,\r\n[price] [decimal](18, 0) NULL,\r\n[category] [nchar](20) NULL,\r\n[id] [smallint] IDENTITY(1,1) NOT NULL,\r\nCONSTRAINT [PK_book] PRIMARY KEY CLUSTERED \r\n(\r\n[id] ASC\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, \r\nALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\r\n) ON [PRIMARY]\r\nGO<\/pre>\n<\/li>\n
- Secondly, we will use the following JSON data.\n
{ \"store\": {\r\n\"book\": [\r\n{ \"category\": \"reference\",\r\n\"author\": \"Nigel Rees\",\r\n\"title\": \"Sayings of the Century\",\r\n\"price\": 8.95\r\n},\r\n{ \"category\": \"fiction\",\r\n\"author\": \"Evelyn Waugh\",\r\n\"title\": \"Sword of Honour\",\r\n\"price\": 12.99\r\n},\r\n{ \"category\": \"fiction\",\r\n\"author\": \"Herman Melville\",\r\n\"title\": \"Moby Dick\",\r\n\"isbn\": \"0-553-21311-3\",\r\n\"price\": 8.99\r\n},\r\n{ \"category\": \"fiction\",\r\n\"author\": \"J. R. R. Tolkien\",\r\n\"title\": \"The Lord of the Rings\",\r\n\"isbn\": \"0-395-19395-8\",\r\n\"price\": 22.99\r\n}\r\n],\r\n\"bicycle\": {\r\n\"color\": \"red\",\r\n\"price\": 19.95\r\n}\r\n}\r\n}<\/pre>\n<\/li>\n
- Also, will create the variables first. The inputjson will contain the JSON data from the previous step:\n<\/a>
ssis variables<\/p><\/div><\/li>\n
- Once it is done,\u00a0 will be to insert a variable this time. We will use the\u00a0Insert Variable option and insert the inputjson variable that contains the JSON values. We will also use a filter to show just the 3rd book information (the first element index is 0). Path filters in JSON are out of the scope of this article, but you can read a nice article about the topic here<\/a>. Press the Parse Field to parse the values. Finally, map the author, category, isbn and price variables:\n
<\/a>Using filters<\/p><\/div><\/li>\n
- After that,\u00a0 drag and drop the Execute SQL Task and join it with the JSON Parser to receive the variable values. Specify a connection with the server name and database name where the table of step 1 was created. In SQLStatement, write the following query:\n
INSERT INTO [dbo].[book]\r\n(\r\n[author],\r\n[isbn],\r\n[price],\r\n[category]\r\n)\r\nVALUES\r\n(?,?,?,?\r\n)<\/pre>\n
This query will insert the 4 SSIS parameters in SQL Server:<\/p>\n
<\/a>Execute SQL Task<\/p><\/div><\/li>\n
- Also, in parameter mapping, specify the variable name, direction, data type and Paremeter name:\n
<\/a>Map variables into SQL<\/p><\/div><\/li>\n
- Finally, run the package and you will be able to see the value stored in SQL Server book table:\n
<\/a>Check results<\/p><\/div><\/li>\n<\/ol>\n
Conclusion<\/h2>\n
As a conclusion, we can say that it is possible to parse JSON in SQL Server using SSIS additional tasks and it does not require to know T-SQL or code to get JSON values.<\/p>\n
References<\/h2>\n
Finally, you can use the following links for more information about REST API and SQL Server:<\/p>\n
- \n
- JSON data in SQL Server<\/a><\/li>\n
- JSONPath – XPath for JSON<\/a><\/li>\n<\/ul>\n
keywords: parse JSON in SQL Server<\/p>\n","protected":false},"excerpt":{"rendered":"
Parse JSON using SQL Server 2014 or lower Hello, this time we will show how to parse JSON in SQL Server 2014 or other versions. we will use a simple tool easy to use and we do not need programming knowledge. Solution In this article we will learn how to parse JSON in\u00a0SQL Server using […]<\/p>\n","protected":false},"author":5,"featured_media":3132,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[265],"tags":[],"yoast_head":"\r\n
How to parse JSON in SQL Server 2014, 2012 and 2008<\/title>\r\n<meta name=\"description\" content=\"In this article, we will show how to parse JSON in SQL Server 2014, 2012 and 2008 using SSIS and how to store the values in SSIS variable.\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to parse JSON in SQL Server 2014, 2012 and 2008\" \/>\r\n<meta property=\"og:description\" content=\"In this article, we will show how to parse JSON in SQL Server 2014, 2012 and 2008 using SSIS and how to store the values in SSIS variable.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:published_time\" content=\"2018-03-23T23:34:44+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2018-04-10T23:46:25+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"100\" \/>\r\n\t<meta property=\"og:image:height\" content=\"100\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys Team\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/\",\"url\":\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/\",\"name\":\"How to parse JSON in SQL Server 2014, 2012 and 2008\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png\",\"datePublished\":\"2018-03-23T23:34:44+00:00\",\"dateModified\":\"2018-04-10T23:46:25+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/91b041e2dcf7ece5f068893c1a68ac6e\"},\"description\":\"In this article, we will show how to parse JSON in SQL Server 2014, 2012 and 2008 using SSIS and how to store the values in SSIS variable.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png\",\"width\":100,\"height\":100},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Parse JSON using SQL Server 2014 or lower\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/91b041e2dcf7ece5f068893c1a68ac6e\",\"name\":\"ZappySys Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g\",\"caption\":\"ZappySys Team\"},\"sameAs\":[\"https:\/\/zappysys.com\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/dcalbimonte\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to parse JSON in SQL Server 2014, 2012 and 2008","description":"In this article, we will show how to parse JSON in SQL Server 2014, 2012 and 2008 using SSIS and how to store the values in SSIS variable.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"How to parse JSON in SQL Server 2014, 2012 and 2008","og_description":"In this article, we will show how to parse JSON in SQL Server 2014, 2012 and 2008 using SSIS and how to store the values in SSIS variable.","og_url":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/","og_site_name":"ZappySys Blog","article_published_time":"2018-03-23T23:34:44+00:00","article_modified_time":"2018-04-10T23:46:25+00:00","og_image":[{"width":100,"height":100,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png","type":"image\/png"}],"author":"ZappySys Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"ZappySys Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/","url":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/","name":"How to parse JSON in SQL Server 2014, 2012 and 2008","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png","datePublished":"2018-03-23T23:34:44+00:00","dateModified":"2018-04-10T23:46:25+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/91b041e2dcf7ece5f068893c1a68ac6e"},"description":"In this article, we will show how to parse JSON in SQL Server 2014, 2012 and 2008 using SSIS and how to store the values in SSIS variable.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/parse-json-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/03\/ssis-json-parser-task.png","width":100,"height":100},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/parse-json-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Parse JSON using SQL Server 2014 or lower"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/91b041e2dcf7ece5f068893c1a68ac6e","name":"ZappySys Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g","caption":"ZappySys Team"},"sameAs":["https:\/\/zappysys.com"],"url":"https:\/\/zappysys.com\/blog\/author\/dcalbimonte\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/3126"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=3126"}],"version-history":[{"count":20,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/3126\/revisions"}],"predecessor-version":[{"id":3183,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/3126\/revisions\/3183"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/3132"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=3126"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=3126"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=3126"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} - JSONPath – XPath for JSON<\/a><\/li>\n<\/ul>\n
- Once it is done,\u00a0 will be to insert a variable this time. We will use the\u00a0Insert Variable option and insert the inputjson variable that contains the JSON values. We will also use a filter to show just the 3rd book information (the first element index is 0). Path filters in JSON are out of the scope of this article, but you can read a nice article about the topic here<\/a>. Press the Parse Field to parse the values. Finally, map the author, category, isbn and price variables:\n
- Secondly, we will create two SSIS variables first that will store the JSON values. In SSDT, go to the SSIS<\/strong> menu and select Variables.\u00a0<\/strong>Create two variables. CustomerID and CustomerName of type String:\n
- First of all, we will first download a free tool named SSIS JSON Parser Task.<\/a> This task will help us to parse the JSON data. Once installed, drag and drop to the design pane. With the installer, you will also have another free tool. The ZS Logging Task\n