{"id":8909,"date":"2020-05-18T16:13:07","date_gmt":"2020-05-18T16:13:07","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=8909"},"modified":"2023-09-04T07:20:05","modified_gmt":"2023-09-04T07:20:05","slug":"read-salesforce-metadata-ssis-using-rest-api-json-source","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/","title":{"rendered":"How to read Salesforce Metadata in SSIS using REST API (JSON Source)"},"content":{"rendered":"

Introduction<\/h2>\n

In our previous posts we saw how to read<\/a> and write<\/a> Salesforce Data using SSIS. In this post we will see how to\u00a0read Salesforce Metadata in SSIS using JSON Source<\/a>.<\/p>\n

Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n
    \r\n \t
  1. SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
  4. (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\r\n<\/ol><\/div>\n

    Step-By-Step<\/h2>\n

    In this section you will learn how to use ZappySys JSON Source Adapter to extract data from Salesforce REST API (JSON Format). You can call pretty much Any REST API to fetch data from salesforce but our example for this post would be read metadata (i.e. table list) from salesforce (we will call sobjects<\/a>\u00a0to get all table information) .<\/p>\n

    So lets get started.<\/p>\n

      \n
    1. In case you missed the previous section, You need to\u00a0Download and Install<\/b>\u00a0SSIS\u00a0ZappySys PowerPack.<\/a><\/li>\n
    2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.<\/li>\n
    3. Now, Drag and Drop SSIS\u00a0Data Flow Task<\/b>\u00a0from SSIS Toolbox.
      \n\"SSIS<\/li>\n
    4. Double click on the Data Flow task to see Data Flow designer surface.<\/li>\n
    5. From the SSIS toolbox drag and drop JSON Source on the Data Flow designer surface.
      \n\"SSIS<\/li>\n
    6. Now double click JSON Source enter any valid REST API URL like below. Make sure you used correct instance name. Using wrong instance name might slow down connection or fail your request. If you are not sure then just login to salesforce portal using credentials you like to use and observe browser URL.. It will have instance number.\n
      https:\/\/na34.salesforce.com\/services\/data\/v20.0\/sobjects\/Account\/describe\/<\/pre>\n<\/li>\n
    7. Check Use Connection and select New ZS-Salesforce<\/strong> Connection from the list.\n
    8. Configure your salesforce connection
      \n\"SSIS<\/li>\n
    9. Select or type filter. For example in this case you will type\u00a0$.sobjects[*]<\/span><\/li>\n
    10. Click OK to Save UI and attach your source to some target like OLEDB Destination and run your data flow to test.<\/li>\n
    11. Here is full UI after all configurations.\n
      \"ssis-salesforce-call-rest-api-get-metadata-eg-table-field-list\"<\/a>
      Read Salesforce Metadata in SSIS (Get Table List Example – SOObject REST API Call)<\/p><\/div><\/li>\n<\/ol>\n
      How to read Salesforce Table Fields Metadata<\/h2>\n
      If you have need to read fields and its information for tables then use below steps<\/p>\n
        \n
      1. Use almost identical steps as previous section. Except two things. Our URL and Filter would be different.<\/li>\n
      2. Configure your API URL as below\n
        https:\/\/YOUR-INSTANCE.salesforce.com\/services\/data\/v20.0\/sobjects\/YOUR-TABLE\/describe\/<\/pre>\n
        Example:<\/strong><\/p>\n
        https:\/\/demo.salesforce.com\/services\/data\/v20.0\/sobjects\/Account\/describe\/<\/pre>\n<\/li>\n
      3. Configure Filter as a\u00a0$.fields[*]<\/span><\/li>\n<\/ol>\n
        \"ssis-salesforce-get-table-fields-metadata-1\"<\/a>
        Get Salesforce Table Fields Metadata using REST API call in SSIS<\/p><\/div>\n
         <\/p>\n","protected":false},"excerpt":{"rendered":"
        Introduction In our previous posts we saw how to read and write Salesforce Data using SSIS. In this post we will see how to\u00a0read Salesforce Metadata in SSIS using JSON Source. Step-By-Step In this section you will learn how to use ZappySys JSON Source Adapter to extract data from Salesforce REST API (JSON Format). You […]<\/p>\n","protected":false},"author":1,"featured_media":8918,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,466],"tags":[195,3,210],"yoast_head":"\r\nHow to read Salesforce Metadata in SSIS using REST API (JSON Source) | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"How to read Salesforce Metadata in SSIS using REST API (JSON Source) - ZappySys Blog\" \/>\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\/read-salesforce-metadata-ssis-using-rest-api-json-source\/\" \/>\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 read Salesforce Metadata in SSIS using REST API (JSON Source) | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"How to read Salesforce Metadata in SSIS using REST API (JSON Source) - ZappySys Blog\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/\" \/>\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=\"2020-05-18T16:13:07+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2023-09-04T07:20:05+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/07\/create-ssis-rest-api-connection-connect-http-oauth-salesforce.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"592\" \/>\r\n\t<meta property=\"og:image:height\" content=\"215\" \/>\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=\"2 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/\",\"url\":\"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/\",\"name\":\"How to read Salesforce Metadata in SSIS using REST API (JSON Source) | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/07\/create-ssis-rest-api-connection-connect-http-oauth-salesforce.png\",\"datePublished\":\"2020-05-18T16:13:07+00:00\",\"dateModified\":\"2023-09-04T07:20:05+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"How to read Salesforce Metadata in SSIS using REST API (JSON Source) - ZappySys Blog\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/07\/create-ssis-rest-api-connection-connect-http-oauth-salesforce.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/07\/create-ssis-rest-api-connection-connect-http-oauth-salesforce.png\",\"width\":592,\"height\":215,\"caption\":\"Create new REST API Connection in SSIS (For JSON, XML or CSV Source)\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to read Salesforce Metadata in SSIS using REST API (JSON Source)\"}]},{\"@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":"How to read Salesforce Metadata in SSIS using REST API (JSON Source) | ZappySys Blog","description":"How to read Salesforce Metadata in SSIS using REST API (JSON Source) - ZappySys 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:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/","og_locale":"en_US","og_type":"article","og_title":"How to read Salesforce Metadata in SSIS using REST API (JSON Source) | ZappySys Blog","og_description":"How to read Salesforce Metadata in SSIS using REST API (JSON Source) - ZappySys Blog","og_url":"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2020-05-18T16:13:07+00:00","article_modified_time":"2023-09-04T07:20:05+00:00","og_image":[{"width":592,"height":215,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/07\/create-ssis-rest-api-connection-connect-http-oauth-salesforce.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":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/","url":"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/","name":"How to read Salesforce Metadata in SSIS using REST API (JSON Source) | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/07\/create-ssis-rest-api-connection-connect-http-oauth-salesforce.png","datePublished":"2020-05-18T16:13:07+00:00","dateModified":"2023-09-04T07:20:05+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"How to read Salesforce Metadata in SSIS using REST API (JSON Source) - ZappySys Blog","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/07\/create-ssis-rest-api-connection-connect-http-oauth-salesforce.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/07\/create-ssis-rest-api-connection-connect-http-oauth-salesforce.png","width":592,"height":215,"caption":"Create new REST API Connection in SSIS (For JSON, XML or CSV Source)"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/read-salesforce-metadata-ssis-using-rest-api-json-source\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to read Salesforce Metadata in SSIS using REST API (JSON Source)"}]},{"@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\/8909"}],"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=8909"}],"version-history":[{"count":5,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/8909\/revisions"}],"predecessor-version":[{"id":10313,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/8909\/revisions\/10313"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/8918"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=8909"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=8909"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=8909"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}