{"id":209,"date":"2015-12-07T19:38:44","date_gmt":"2015-12-07T19:38:44","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=209"},"modified":"2023-01-08T21:06:29","modified_gmt":"2023-01-08T21:06:29","slug":"export-json-from-sql-server-using-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/","title":{"rendered":"5 Ways to Export JSON from SQL Server using SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

SQL Server 2012\u00a0and all previous versions lacking native JSON support, same theory is true for their ETL Platform SSIS. If you are one of them who wants to export JSON from SQL Server or any other RDBMS like MySQL, Oracle then you can easily\u00a0output complex\u00a0JSON using any of the following components.<\/p>\n

Five ways of creating JSON inside SSIS<\/h2>\n

ZappySys toolset provides multiple ways creating JSON inside SSIS. You can choose method suitable for your scenario. Each method has pros and cons so choose it as per your need.<\/p>\n

JSON File Destination<\/h3>\n

\"SSISSSIS JSON Destination<\/a>\u00a0can be used to generate simple or complex JSON files out of relational data source such as SQL Server, Oracle, MySQL. In few clicks you can create desired JSON Shape from single or multiple tables (Parent\/Child Nested Documents).<\/p>\n

PROS:<\/strong> Multiple data flow components can be attached. Which means unlike Export JSON Task<\/a> you are not limited to OLEDB or ODBC Sources only.<\/p>\n

CONS:<\/strong> When you remove upstream component it may break Dataset mappings. Also server side Lookup Option is not available. File Splitting Options not available.<\/p>\n

Here are some articles<\/a> for use case of this component.<\/p>\n

\"JSON<\/a>

JSON File Destination – Generate JSON in SSIS from multiple inputs<\/p><\/div>\n

JSON Generator Transform<\/h3>\n

\"JSON<\/span><\/h3>\n

SSIS JSON Generator Transform<\/a>\u00a0can be used to generate single or multiple JSON documents from any type of data sources (e.g. SQL Server, MySQL, Flat File, Excel) inside data flow task. It takes multiple inputs and outputs JSON String Output Column which can be used to feed JSON to other downstream system.<\/p>\n

PROS:<\/strong> Multiple data flow components can be attached. Which means unlike Export JSON Task<\/a> you are not limited to OLEDB or ODBC Sources only.<\/p>\n

CONS:<\/strong> When you remove upstream component it may break Dataset mappings. Also server side Lookup Option is not available. File Splitting Options not available. Output is JSON String Column.<\/p>\n

Here are some articles<\/a> for use case of this transform.<\/p>\n

\"Preview<\/a>

Preview JSON – Generate for BigQuery Table Insert<\/p><\/div>\n

Export JSON Task<\/h3>\n

\"SSIS<\/em>SSIS Export JSON File Task<\/a>\u00a0can be used to generate simple or complex JSON files out of relational data source such as SQL Server, Oracle, MySQL. It also support exporting directly to cloud (e.g. Azure Blob Storage, Amazon AWS S3, Secure FTP). In few clicks you can create desired JSON Shape from single or multiple tables (Parent\/Child Nested Documents)<\/p>\n

PROS:<\/strong> Simple to use with flexible options to export to file, variable, AWS S3, Azure Blob, FTP \/ SFTP location. Allows to split records. File Splitting Options available (Split By Size\u00a0 \/ Row Count).<\/p>\n

CONS:<\/strong> You can only use limited data sources as Dataset input (e.g. OLEDB \/ ODBC or SQL Server).<\/p>\n

Here are some articles<\/a> for use case of this task.<\/p>\n

Template Transform<\/h3>\n

\"SSISTemplate Transform can be used to create dynamic strings inside data flow. You can use upstream columns as placeholders or use SSIS variables anywhere in the template to create desired text (e.g. XML \/ JSON document).<\/p>\n

PROS:<\/strong> Very Simple to use.<\/p>\n

CONS:<\/strong> Cannot use multiple inputs. It may not be used to create Complex JSON and options like do not output NULL attributes is not possible.<\/p>\n

 <\/p>\n

Here are some articles<\/a> for use case of this transform.<\/p>\n

\"Create<\/a>

Create JSON for API Request (POST) using SSIS Template Transform<\/p><\/div>\n

Logging Task (Control Flow Approach)<\/h3>\n

\u00a0Just like Template Transform you can use ZS Logging Task<\/a> to create JSON string from Template and Variable Placeholders.<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

Here is how to use Logging Task to save JSON into a variable.<\/p>\n

    \n
  1. Select Variable which you like to Set on Logging Task<\/li>\n
  2. Enter your Template JSON String which you like to produce (see example below). Notice the use of yyyy-MM-dd format specifier<\/a> and also JSONENCODE function.\n
    {\r\n ActionId: {{User::vActionId}},\r\n CreatedOn: \"{{User::vOrderDate,yyyy-MM-ddTHH:mm:ss}}\",\r\n Notes: \"{{User::vNotes,JSONENCODE}}\"\r\n}<\/pre>\n<\/li>\n
  3. Select Message Type = None<\/strong><\/li>\n
  4. Uncheck Append<\/strong>, and Prefix<\/strong> options like below\n
    \"Creating<\/a>
    Creating JSON \/ XML Document using SSIS Logging Task<\/p><\/div><\/li>\n<\/ol>\n
     <\/p>\n
    Here are some articles<\/a> for use case of this task.<\/p>\n
     <\/p>\n
    Create JSON using SSIS Export JSON Task<\/h2>\n
    Now let’s look at Export JSON Task form example to create JSON. Steps listed in below sections are very similar to JSON File Destination or JSON Generator Transform.<\/p>\n
    ZappySys offers innovative Layout Editor for JSON \/ XML creation from multiple sources. You can use Layout Editor to produce desired JSON Layout (Layout is stored in XML\u00a0format behind the scene) and then in later step you will see how to make it dynamic so values are changed at runtime.<\/p>\n
    You can read layout as direct value, read from variable or read from file from disk.<\/p>\n
    Select source connection for data<\/h3>\n
    First lets create new OLEDB or ADO.net connection from where we will read data. In our case we reading from Northwind Database.<\/p>\n
    \"SSIS<\/a>
    SSIS JSON Export Task – Select Source Database Connection<\/p><\/div>\n
    Create Datasets<\/h3>\n
    Now add datasets which will be used to produce JSON File. One dataset will be always Root Dataset (Example: If your Root information is about customer then select customer table or query as your Main dataset. All other sections will be child sections)<\/p>\n
    \"Add<\/a>
    Add SQL Dataset – Export JSON from SQL Query Option<\/p><\/div>\n
    Add Output Columns for Main JSON Document<\/h3>\n
    Now its time to add columns for our output. If * is added by default then remove. * means output all columns. When you add columns first make sure you select correct parent node under which you want to add columns. In our case “Mappings” is parent node.<\/p>\n
    \"Add<\/a>
    Add Output Columns for JSON Document – Column Selection Screen<\/p><\/div>\n
    Add JSON Array \/ JOIN Conditions for related document<\/h3>\n
    Now let’s look at how to add JSON Array. For example Customer may have one or more Orders so we need to add node called Orders which may Produce JSON fragment like below<\/p>\n
    Orders : [ {…} , {…} ]\u00a0 <\/span><\/p>\n
    Once customer related columns added, its time to add Orders section.<\/p>\n
      \n
    1. This is Array of documents so first highlight “Mappings” node<\/li>\n
    2. Click “Add Document Array” option from toolbar.<\/li>\n
    3. On Add\/Edit\u00a0Elements\u00a0UI specify name for JSON Attribute and specify correct relationship to parent dataset. In our case Orders related to Customers using CustomerID key.<\/li>\n<\/ol>\n
      \"Add<\/a>
      Add Child Section – nested JSON document , Define Parent-Child Relationship<\/p><\/div>\n
      Handling JSON Array with dummy root dataset (Multiple Arrays)<\/h3>\n
      So in previous section we saw how to add array and define JOIN condition with parent dataset. Now what if you really have no parent dataset to join with ? For example we need to produce following sample JSON where Order array is related to Customer by common Column CustomerID as JOIN column. But Customer is not bound with any parent column. So how do you define JOIN condition? In such case you have to use below technique called dummy root dataset. (See next section on how to handle only one dataset using Single Dataset Array Mode<\/strong>)<\/p>\n
      Sample JSON (Dummy root pattern)<\/strong><\/p>\n
      {\r\n    \"Customers\": [\r\n      {\r\n        \"CustomerID\": \"ALFKI\",\r\n        \"CompanyName\": \"Alfreds Futterkiste\",\r\n        \"Orders\": [\r\n          {\r\n            \"OrderID\": 1000,\r\n            \"OrderDate\": \"2015-12-01\"\r\n          },\r\n          {\r\n            \"OrderID\": 1001,\r\n            \"OrderDate\": \"2015-12-01\"\r\n          }\r\n        ]\r\n      },\r\n\r\n      {\r\n        \"CustomerID\": \"ANATR\",\r\n        \"CompanyName\": \"Ana Trujillo Emparedados y helados\",\r\n        \"Orders\": [\r\n          {\r\n            \"OrderID\": 1002,\r\n            \"OrderDate\": \"2015-12-01\"\r\n          },\r\n          {\r\n            \"OrderID\": 1003,\r\n            \"OrderDate\": \"2015-12-01\"\r\n          }\r\n        ]\r\n      }\r\n\r\n    ]\r\n  }\r\n}<\/pre>\n
      Since root level always needs dataset, we must provide dummy dataset (only one row) even though there is no need for Parent Dataset JOIN in Customer Array. Typically you can write query like below to product dummy row in SQL Server or other database using OLEDB Source or Use\u00a0Dummy Data Source<\/a>\u00a0with Max Rows=1 setting to produce one fake record.<\/p>\n
      SELECT 1 as Col1<\/pre>\n
      Using Dummy DataSource as Input<\/strong><\/p>\n
      For generating JSON which needs dummy root. Perform the following steps<\/p>\n
        \n
      1. Drag Dummy Data Source from SSIS Toolbox. Change How many rows<\/strong> to 1. Click OK<\/li>\n
      2. Now drag two more OLEDB sources and Configure (e.g. Customers and Orders)<\/li>\n
      3. Now drag ZS JSON FIle Destination<\/a> component or ZS JSON Generator<\/a><\/li>\n
      4. Now connect Dummy datasource then Customers and then Orders<\/li>\n
      5. Double click JSON component to configure layout. You may notice that Dummy Input is set as Root by default.<\/li>\n
      6. You can now select Mappings node and click on Add Array Documents icon. Configure like below. Notice we have not selected any JOIN criteria because its joined with Dummy Root.\n
      7. Now select newly added node and Click Add column icon to add CustomerID, CompanyName column under Customers array<\/li>\n
      8. Now select Customers array node and click Array Documents icon to Add Orders array.<\/li>\n
      9. Once Orders Array ia added you can Select Orders node and Insert new element to add OrderID and OrderDate columns<\/li>\n
      10. Finally it will look like below\n
        \"JSON<\/a>
        JSON File Destination – Generate JSON in SSIS from multiple inputs<\/p><\/div><\/li>\n<\/ol>\n
         <\/p>\n
        Single dataset used as array<\/h3>\n
        Now lets look at one more pattern just like described in the previous section but now we have just one nested level (Only customers and not using Orders). If you have such case then choose Single dataset pattern option in the output mode. On Export Task you may see as check box rather than dropdown. To produced desired JSON (see blow) perform these steps.<\/p>\n
          \n
        1. Select Output mode as Single Dataset Array<\/strong><\/li>\n
        2. In your Layout builder click on Add Document Array<\/strong> Icon and name it “Customers”.<\/li>\n
        3. After that highlight newly added node > Right-Click > Add Elements<\/strong> under that node<\/li>\n
        4. That’s it you will now see preview like below.<\/li>\n<\/ol>\n
          {\r\n    \"Customers\": [\r\n      {\r\n        \"CustomerID\": \"ALFKI\",\r\n        \"CompanyName\": \"Alfreds Futterkiste\"\r\n      },\r\n\r\n      {\r\n        \"CustomerID\": \"ANATR\",\r\n        \"CompanyName\": \"Ana Trujillo Emparedados y helados\"\r\n      }\r\n    ]\r\n  }\r\n}<\/pre>\n
           <\/p>\n
          How to export JSON in 2D Array format<\/h3>\n
          If you have to export JSON as 2D array format then you can change following option.<\/p>\n
          \"SSIS<\/p>\n
          Here are examples of output for each option.
          \nSelect say you have source data with 2 columns id<\/i> and name<\/i>.<\/p>\n
          Default<\/b><\/p>\n
          [{id:1,name:\"AA\"}, {id:2,name:\"BB\"}]<\/pre>\n
          Multicontent<\/b><\/p>\n
          {id:1,name:\"AA\"}{id:2,name:\"BB\"}<\/pre>\n
          ArrayDocs<\/b><\/p>\n
          [{id:1,name:\"AA\"}, {id:2,name:\"BB\"}]<\/pre>\n
          Array2D<\/b><\/p>\n
          [[1,\"AA\"],[2,\"BB\"]]<\/pre>\n
          Array2DWithHeader<\/b><\/p>\n
          [[\"id\",\"name\"],[1,\"AA\"],[2,\"BB\"]]<\/pre>\n
          ArrayLines<\/b><\/p>\n
          [1,\"AA\"][2,\"BB\"]<\/pre>\n
          ArrayLinesWithHeader<\/b><\/p>\n
          [\"id\",\"name\"][1,\"AA\"][2,\"BB\"]<\/pre>\n
           <\/p>\n
          Preview JSON output using live preview<\/h3>\n
          You can enable \/ disable Live preview of JSON layout as below.<\/p>\n
          \"JSON<\/a>
          JSON Output preview in SSIS Export JSON File Task<\/p><\/div>\n
          Make things dynamic (Using SSIS Variable for SQL or Layout)<\/h2>\n
          There will be a time when you have use dynamic values in your JSON generation. You may have to Alias something from variable or you have to supply Variable in your SQL used for Dataset. Not to worry. ZappySys Supports multiple ways to make it dynamic. You can use SSIS Variables following 3 ways to make Layout XML dynamic.<\/p>\n
            \n
          1. Use SSIS variable inside Dataset SQL (Only for ZS Export JSON Task<\/a>)<\/li>\n
          2. Anywhere inside Layout XML (Click Edit Code<\/strong> option)<\/li>\n
          3. Load Layout From Variable (Indirect Method)<\/li>\n<\/ol>\n
            Using Variable inside Dataset SQL<\/h3>\n
            Here is how you can use Variable inside dataset SQL for\u00a0ZS Export JSON Task<\/a><\/p>\n
            \"SSIS<\/a>
            SSIS Export JSON Task – Using Variable for Dynamic SQL<\/p><\/div>\n
            Using Variable inside Layout XML<\/h3>\n
            Here is how you can insert SSIS Variable anywhere in Layout XML. In below Example we are choosing Alias for one column from some SSIS Variable.<\/p>\n
            \"SSIS<\/a>
            SSIS JSON File Destination – Using SSIS Variable for Dynamic Layout<\/p><\/div>\n
            Load Layout From Variable<\/h3>\n
            If you have some need to load Layout XML from File or SSIS Variable. You can use Load Layout from Variable Option.<\/p>\n
            For example in below screenshot we are creating layout first and ave to some variable. Or you can load from external file using ZS Advanced File System Task<\/a>.\u00a0 Connect Logging task to Export JSON Task and you ready to produce JSON.<\/p>\n
            \"Set<\/a>
            Set SSIS Variable value (multi-line content using placeholder)<\/p><\/div>\n
            Here is how to load Layout From any SSIS Variable. You can also load Layout from XML file or Variable which contains layout File Path.<\/p>\n
            \"JSON<\/a>
            JSON Layout from SSIS Variable<\/p><\/div>\n
            Other JSON Output Options<\/h2>\n
            Execute package and verify generated JSON it should look like below (Date format may be different in your case if you have set different on JSON options tab). For clarity we have formatted JSON below but in your output it may be compact version so reduce file size.<\/p>\n
            Indent JSON Output<\/h3>\n
            You can check Indent option to make JSON look pretty like below.<\/p>\n
            {\r\n  \"CustomerID\": \"ALFKI\",\r\n  \"CompanyName\": \"Alfreds Futterkiste\",\r\n  \"ContactName\": null,\r\n  \"Orders\": [\r\n    {\r\n      \"OrderID\": 10643,\r\n      \"OrderDate\": \"1997-08-25T00:00:00\"\r\n    },\r\n    {\r\n      \"OrderID\": 10692,\r\n      \"OrderDate\": \"1997-10-03T00:00:00\"\r\n    },\r\n    {\r\n      \"OrderID\": 10702,\r\n      \"OrderDate\": \"1997-10-13T00:00:00\"\r\n    },\r\n    {\r\n      \"OrderID\": 10835,\r\n      \"OrderDate\": \"1998-01-15T00:00:00\"\r\n    },\r\n    {\r\n      \"OrderID\": 10952,\r\n      \"OrderDate\": \"1998-03-16T00:00:00\"\r\n    },\r\n    {\r\n      \"OrderID\": 11011,\r\n      \"OrderDate\": \"1998-04-09T00:00:00\"\r\n    }\r\n  ]\r\n}<\/pre>\n
            Hide NULL Attribute Option<\/h3>\n
            There will be a time when you want to Hide NULL attribute rather than showing MyAttr: null<\/strong>\u00a0this is also possible by checking Hide NULL Attribute Option on Options Tab.<\/p>\n
            Export JSON to Amazon S3, Azure Blob, Secure FTP (SFTP \/ FTPS)<\/h2>\n
            Export JSON Task support some really good options to save file to other than local file system. If you want to export JSON directly to Amazon \/ Azure Cloud or FTP Server without saving to local disk then you can use Azure \/ FTP \/ Amazon Connection Manager on Target Tab of Export JSON Task. This option is only available for PRO Edition<\/strong> Users.<\/p>\n
            \"Export
            Export JSON \/ CSV \/ XML Files to Azure Blob, Amazon S3, Secure FTP Storage<\/p><\/div>\n
            Conclusion<\/h2>\n
            If you want to output JSON from SQL Server or any other relational DB then its not simple task in SSIS specially when you have nested JSON documents (e.g. Customer->Orders). In this post you have seen how to use SSIS JSON Export Task<\/a> to generate nested JSON from multiple datasets.<\/p>\n","protected":false},"excerpt":{"rendered":"
            Introduction SQL Server 2012\u00a0and all previous versions lacking native JSON support, same theory is true for their ETL Platform SSIS. If you are one of them who wants to export JSON from SQL Server or any other RDBMS like MySQL, Oracle then you can easily\u00a0output complex\u00a0JSON using any of the following components. Five ways of […]<\/p>\n","protected":false},"author":1,"featured_media":5358,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[94,323,131,145,267],"tags":[6,48,12,93,4],"yoast_head":"\r\n5 Ways to Export JSON from SQL Server using SSIS | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Export JSON from SQL Server using SSIS. Learn five ways to create JSON in SSIS. Output nested JSON, save to REST API, File, Secure FTP, Azure Blob, AWS S3\" \/>\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\/export-json-from-sql-server-using-ssis\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"5 Ways to Export JSON from SQL Server using SSIS | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Export JSON from SQL Server using SSIS. Learn five ways to create JSON in SSIS. Output nested JSON, save to REST API, File, Secure FTP, Azure Blob, AWS S3\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2015-12-07T19:38:44+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2023-01-08T21:06:29+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/12\/ssis-generate-json-using-dummy-root-multiple-input-source.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"933\" \/>\r\n\t<meta property=\"og:image:height\" content=\"784\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"12 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/\",\"url\":\"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/\",\"name\":\"5 Ways to Export JSON from SQL Server using SSIS | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/12\/ssis-generate-json-using-dummy-root-multiple-input-source.png\",\"datePublished\":\"2015-12-07T19:38:44+00:00\",\"dateModified\":\"2023-01-08T21:06:29+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Export JSON from SQL Server using SSIS. Learn five ways to create JSON in SSIS. Output nested JSON, save to REST API, File, Secure FTP, Azure Blob, AWS S3\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/12\/ssis-generate-json-using-dummy-root-multiple-input-source.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/12\/ssis-generate-json-using-dummy-root-multiple-input-source.png\",\"width\":933,\"height\":784,\"caption\":\"JSON File Destination - Generate JSON in SSIS from multiple inputs\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"5 Ways to Export JSON from SQL Server using SSIS\"}]},{\"@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\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"5 Ways to Export JSON from SQL Server using SSIS | ZappySys Blog","description":"Export JSON from SQL Server using SSIS. Learn five ways to create JSON in SSIS. Output nested JSON, save to REST API, File, Secure FTP, Azure Blob, AWS S3","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\/export-json-from-sql-server-using-ssis\/","og_locale":"en_US","og_type":"article","og_title":"5 Ways to Export JSON from SQL Server using SSIS | ZappySys Blog","og_description":"Export JSON from SQL Server using SSIS. Learn five ways to create JSON in SSIS. Output nested JSON, save to REST API, File, Secure FTP, Azure Blob, AWS S3","og_url":"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2015-12-07T19:38:44+00:00","article_modified_time":"2023-01-08T21:06:29+00:00","og_image":[{"width":933,"height":784,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/12\/ssis-generate-json-using-dummy-root-multiple-input-source.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/","url":"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/","name":"5 Ways to Export JSON from SQL Server using SSIS | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/12\/ssis-generate-json-using-dummy-root-multiple-input-source.png","datePublished":"2015-12-07T19:38:44+00:00","dateModified":"2023-01-08T21:06:29+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Export JSON from SQL Server using SSIS. Learn five ways to create JSON in SSIS. Output nested JSON, save to REST API, File, Secure FTP, Azure Blob, AWS S3","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/12\/ssis-generate-json-using-dummy-root-multiple-input-source.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/12\/ssis-generate-json-using-dummy-root-multiple-input-source.png","width":933,"height":784,"caption":"JSON File Destination - Generate JSON in SSIS from multiple inputs"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/export-json-from-sql-server-using-ssis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"5 Ways to Export JSON from SQL Server using SSIS"}]},{"@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\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/209"}],"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\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=209"}],"version-history":[{"count":7,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/209\/revisions"}],"predecessor-version":[{"id":9914,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/209\/revisions\/9914"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/5358"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=209"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=209"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=209"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}