{"id":605,"date":"2014-02-27T09:23:03","date_gmt":"2014-02-27T14:23:03","guid":{"rendered":"http:\/\/binaryworld.net\/blogs\/?p=605"},"modified":"2015-07-01T09:56:22","modified_gmt":"2015-07-01T13:56:22","slug":"consuming-json-data-in-sql-server-and-ssis-convert-json-toxml","status":"publish","type":"post","link":"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/","title":{"rendered":"Consuming JSON data in SQL Server and SSIS, convert JSON to XML"},"content":{"rendered":"

Just came across very interesting scenario where I had to consume JSON data in SQL Server<\/strong>. Yes I can write C#\/VB.net <\/strong>code and load JSON to SQL Server and go from there but what if I have no expertise in C#\/VB.net or what I have to do everything using SQL no other options are allowed. Well check below link and you will find some kool techniques for consuming and generating JSON in SQL Server. <\/p>\n

Import JSON data in SQL Server<\/h2>\n

Importing JSON into SQL Server requires you to Deformalize JSON structure and import selected columns. In this section we will learn how to use SSIS JSON Source to Import JSON File or HTTP URL Stream into SQL Server Table. <\/p>\n

    \n
  1. Download and Install SSIS JSON Source Connector <\/a><\/li>\n
  2. From your tool box Drag SSIS JSON Source<\/em><\/li>\n
  3. Specify JSON File or URL from where you want to consume JSON data<\/li>\n
  4. Click on Columns tab and select columns you want to select<\/li>\n
  5. Drag OLEDB Destination. Select SQL Server connection and Target Table where you want to load data<\/li>\n
  6. Map Input columns to Target SQL Server Table<\/li>\n
  7. Run the package<\/li>\n<\/ol>\n
    \"Read<\/div>\n
    <\/div>\n
    \"SSIS<\/div>\n
    \"SSIS<\/div>\n
    <\/div>\n
    \"How<\/div>\n

    Export JSON data in SQL Server<\/h2>\n

    Now lets look at how to export SQL Server data to JSON file<\/strong>. You can do this two different ways. Use Command Line to create JSON file <\/a> or use SSIS JSON File Export Task<\/a> to export to JSON<\/p>\n

      \n
    1. Download and Install SSIS Export JSON File Task<\/a><\/li>\n
    2. In Control Flow From SSIS tool box Drag SSIS Export JSON File Task<\/em><\/li>\n
    3. Specify Connection from where you want to read data<\/li>\n
    4. Specify tables or query for source. You can pick multiple tables<\/li>\n
    5. If you want custom layout of your JSON then enable Custom layout option on layout Tab (e.g. Nested nodes or array of value)<\/li>\n
    6. Specify Target path (folder or file name)<\/li>\n
    7. Run the package<\/li>\n<\/ol>\n
      \"SSIS
      SSIS Export JSON File Task – Generate JSON files for selected tables\/views<\/figcaption><\/figure>\n
      \"SSIS
      SSIS Export JSON File Task – Select Target Path and other options<\/figcaption><\/figure>\n
      \"SSIS
      SSIS Export JSON File Task – Layout editor for nested JSON elements<\/figcaption><\/figure>\n
      \"SSIS
      SSIS Export JSON File Task – Split JSON Data into Multiple files<\/figcaption><\/figure>\n
      \"SSIS
      SSIS Export JSON File Task – Date Formatting, Encoding Options<\/figcaption><\/figure>\n
      \"SSIS
      SSIS Export JSON File Task – Execution Log<\/figcaption><\/figure>\n

      Hope this is helpful<\/p>\n

      Cheers!!!<\/p>\n

      Consume XML or JSON in SQL SERVER – SSIS<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

      Just came across very interesting scenario where I had to consume JSON data in SQL Server. Yes I can write C#\/VB.net code and load JSON to SQL Server and go from there but what if I have no expertise in …<\/p>\n

      Consuming JSON data in SQL Server and SSIS, convert JSON to XML<\/span> Read More »<\/a><\/p>\n","protected":false},"author":1,"featured_media":606,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[101,158,30,85,70],"yoast_head":"\r\nConsuming JSON data in SQL Server and SSIS, convert JSON to XML - BinaryWorld Blog<\/title>\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:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Consuming JSON data in SQL Server and SSIS, convert JSON to XML - BinaryWorld Blog\" \/>\r\n<meta property=\"og:description\" content=\"Just came across very interesting scenario where I had to consume JSON data in SQL Server. Yes I can write C#\/VB.net code and load JSON to SQL Server and go from there but what if I have no expertise in … Consuming JSON data in SQL Server and SSIS, convert JSON to XML Read More »\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/\" \/>\r\n<meta property=\"og:site_name\" content=\"BinaryWorld Blog\" \/>\r\n<meta property=\"article:published_time\" content=\"2014-02-27T14:23:03+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2015-07-01T13:56:22+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg\" \/>\r\n\t<meta property=\"og:image:width\" content=\"150\" \/>\r\n\t<meta property=\"og:image:height\" content=\"150\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\r\n<meta name=\"author\" content=\"Binary World\" \/>\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=\"Binary World\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/\",\"url\":\"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/\",\"name\":\"Consuming JSON data in SQL Server and SSIS, convert JSON to XML - BinaryWorld Blog\",\"isPartOf\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg\",\"datePublished\":\"2014-02-27T14:23:03+00:00\",\"dateModified\":\"2015-07-01T13:56:22+00:00\",\"author\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0\"},\"breadcrumb\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/#primaryimage\",\"url\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg\",\"contentUrl\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg\",\"width\":150,\"height\":150,\"caption\":\"T SQL Date format convert function\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/binaryworld.net\/blogs\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Consuming JSON data in SQL Server and SSIS, convert JSON to XML\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#website\",\"url\":\"https:\/\/binaryworld.net\/blogs\/\",\"name\":\"BinaryWorld Blog\",\"description\":\"Tips and Tutorials for Microsoft SQL Server, SSIS, SSAS, Business Intelligence, C#, .net\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/binaryworld.net\/blogs\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0\",\"name\":\"Binary World\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g\",\"caption\":\"Binary World\"},\"description\":\"Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.\",\"url\":\"https:\/\/binaryworld.net\/blogs\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Consuming JSON data in SQL Server and SSIS, convert JSON to XML - BinaryWorld Blog","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:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/","og_locale":"en_US","og_type":"article","og_title":"Consuming JSON data in SQL Server and SSIS, convert JSON to XML - BinaryWorld Blog","og_description":"Just came across very interesting scenario where I had to consume JSON data in SQL Server. Yes I can write C#\/VB.net code and load JSON to SQL Server and go from there but what if I have no expertise in … Consuming JSON data in SQL Server and SSIS, convert JSON to XML Read More »","og_url":"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/","og_site_name":"BinaryWorld Blog","article_published_time":"2014-02-27T14:23:03+00:00","article_modified_time":"2015-07-01T13:56:22+00:00","og_image":[{"width":150,"height":150,"url":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg","type":"image\/jpeg"}],"author":"Binary World","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Binary World","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/","url":"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/","name":"Consuming JSON data in SQL Server and SSIS, convert JSON to XML - BinaryWorld Blog","isPartOf":{"@id":"https:\/\/binaryworld.net\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/#primaryimage"},"image":{"@id":"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/#primaryimage"},"thumbnailUrl":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg","datePublished":"2014-02-27T14:23:03+00:00","dateModified":"2015-07-01T13:56:22+00:00","author":{"@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0"},"breadcrumb":{"@id":"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/#primaryimage","url":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg","contentUrl":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg","width":150,"height":150,"caption":"T SQL Date format convert function"},{"@type":"BreadcrumbList","@id":"https:\/\/binaryworld.net\/blogs\/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/binaryworld.net\/blogs\/"},{"@type":"ListItem","position":2,"name":"Consuming JSON data in SQL Server and SSIS, convert JSON to XML"}]},{"@type":"WebSite","@id":"https:\/\/binaryworld.net\/blogs\/#website","url":"https:\/\/binaryworld.net\/blogs\/","name":"BinaryWorld Blog","description":"Tips and Tutorials for Microsoft SQL Server, SSIS, SSAS, Business Intelligence, C#, .net","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/binaryworld.net\/blogs\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0","name":"Binary World","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g","caption":"Binary World"},"description":"Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.","url":"https:\/\/binaryworld.net\/blogs\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts\/605"}],"collection":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/comments?post=605"}],"version-history":[{"count":0,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts\/605\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/media\/606"}],"wp:attachment":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/media?parent=605"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/categories?post=605"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/tags?post=605"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}