{"id":4414,"date":"2018-07-11T19:21:11","date_gmt":"2018-07-11T19:21:11","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=4414"},"modified":"2025-08-22T14:54:54","modified_gmt":"2025-08-22T14:54:54","slug":"read-salesforce-custom-reports-ssis-odbc","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-salesforce-custom-reports-ssis-odbc\/","title":{"rendered":"Read from Salesforce Custom Reports in SSIS \/ ODBC"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>In our previous posts we saw how to read<\/a> and write<\/a> Salesforce Data using SSIS Salesforce Connectors (Source \/Destination). It covered how to read salesforce data from Tables and SOQL query but it does not support reading from reports. So now in this post we will see how to\u00a0read Salesforce data from Custom Reports in SSIS<\/strong> which will use\u00a0JSON Source<\/a>\u00a0rather than Salesforce Source<\/a>.<\/p>\n

Later in this post we will show you how to integrate Salesforce data in Apps like Power BI, SSRS, Informatica and languages like C#, Java, Python or T-SQL for MS SQL Folks.<\/p>\n

So let’s get started!!!<\/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

    <\/h2>\n

    Read Salesforce Custom Reports in SSIS<\/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 and enter your Report URL like below. Make sure you replace instance name and report ID as per the screenshot.\n
      https:\/\/YOUR-ORG-INSTANCE.salesforce.com\/services\/data\/v64.0\/analytics\/reports\/YOUR-REPORT-ID\r\n<\/pre>\n
      Example URLs<\/strong><\/p>\n
      Using Salesforce Org Domain:\r\nhttps:\/\/zappysys21.salesforce.com\/services\/data\/v64.0\/analytics\/reports\/00O61000001a97n\r\n\r\nUsing Personal Lighting\/My Domain (works when [My Domain] feature enabled):\r\nhttps:\/\/zappysys21.my.salesforce.com\/services\/data\/v64.0\/analytics\/reports\/00O61000001a97n\r\n\r\nUsing Direct Pod\/Instance URL:\r\nhttps:\/\/na34.salesforce.com\/services\/data\/v64.0\/analytics\/reports\/00O61000001a97n<\/pre>\n<\/li>\n
    7. Check Use Credentials and configure Salesforce Connection\n
    8. Configure your Salesforce connection\"SSIS<\/li>\n
    9. Select or type filter. For example in this case you will type\u00a0$.factMap.T!T.rows[*]<\/span><\/li>\n
    10. Uncheck Use Parents option (You can keep it but it will add many unwanted fields)
      NOTE:<\/strong> Please make sure that you’re configuring an API URL and not the HTML\/Browser Page URL as both URLs will be different.Please make sure you replace instance name and report ID as per the below screenshot.\n
      https:\/\/YOUR-ORG-INSTANCE.salesforce.com\/services\/data\/v64.0\/analytics\/reports\/YOUR-REPORT-ID\r\n<\/pre>\n
      Example URLs\u00a0<\/strong><\/p>\n
      https:\/\/na34.salesforce.com\/services\/data\/v64.0\/analytics\/reports\/00O61000001a97n\r\n\r\nUsing Salesforce Org Domain:\r\nhttps:\/\/zappysys21.salesforce.com\/services\/data\/v64.0\/analytics\/reports\/00O61000001a97n\r\n\r\nUsing Personal Lighting\/My Domain (works when [My Domain] feature enabled):\r\nhttps:\/\/zappysys21.my.salesforce.com\/services\/data\/v64.0\/analytics\/reports\/00O61000001a97n\r\n\r\nUsing Direct Pod\/Instance URL:\r\nhttps:\/\/na34.salesforce.com\/services\/data\/v64.0\/analytics\/reports\/00O61000001a97n<\/pre>\n<\/div><\/div>\n
      \"ssis-read-salesforce-custom-reports\"<\/a>
      Configure – Read Salesforce Custom Reports Data<\/p><\/div><\/li>\n
    11. Now Go to Array Transform Tab\n
        \n
      1. Select Transform Type : Complex 2-dimensional array<\/span><\/li>\n
      2. Column name filter: $.reportMetadata.detailColumns[*]<\/span><\/li>\n
      3. Row values filter: $.dataCells[*].label<\/span><\/li>\n<\/ol>\n<\/li>\n
      4. Here is how your Array Transform Tab configuration should look like\n
      5. Now Click OK to Save UI and attach your source to some target like OLEDB Destination and run your data flow to test.\n
        \"Extract<\/a>
        Extract Data from Salesforce Custom Reports in SSIS – Load into Target like SQL Server, MySQL, PostgreSql, Oracle<\/p><\/div><\/li>\n<\/ol>\n
        Read Salesforce Custom Reports in ODBC Apps<\/h2>\n
        So far we have seen how to read salesforce custom report data in SSIS using ETL approach, but what if you are not using SSIS and you want to pull data in programming languages like C#, Java, Python or apps like Power BI, Informatica, Excel, SSRS or using pure T-SQL way in SQL Server?<\/p>\n
        Requirements<\/h2>\r\nIn order to access API data inside your App using ODBC Driver you will need to make sure following requirements are met.\r\n
          \r\n \t
        1. Download and Install\u00a0ZappySys ODBC PowerPack<\/a>\u00a0(This includes XML \/ JSON \/ REST API and few other drivers for SQL Server and ODBC connectivity in tools like Excel, Power BI, SSRS)<\/li>\r\n<\/ol><\/div>\n
          ZappySys Provides native Salesforce Driver<\/a> to read \/ write Salesforce Object’s data (Its part of ODBC PowerPack<\/a>). This driver can read\/write standard objects (e.g. Accounts, Contacts) However reading from salesforce custom report<\/strong> is not supported by native driver. For this purpose you have to use JSON \/ REST Driver using this approach<\/a>. With Generic REST \/ JSON API driver you can automate pretty much any Salesforce Operations. To get some advanced information on Report API you can refer this guide<\/a><\/p>\n
          So let’s get started.<\/p>\n
            \n
          1. Follow this blog post<\/a> to install JSON \/ REST Driver and configure Salesforce OAuth Connection.<\/li>\n
          2. Once you can read some sample data described in previous step we are ready to run advanced queries.<\/li>\n
          3. Go to Preview Tab and Run below Query to pull data from Custom Report\n
            --change YOUR-INSTANCE and YOUR-REPORT-ID\r\nSELECT * FROM $\r\nWITH(\r\nSrc='https:\/\/YOUR-ORG-INSTANCE.salesforce.com\/services\/data\/v64.0\/analytics\/reports\/YOUR-REPORT-ID'\r\n,Filter='$.factMap.T!T.rows[*]'\r\n,ArrayTransformType='TransformComplexTwoDimensionalArray'\r\n,ArrayTransColumnNameFilter='$.reportMetadata.detailColumns[*]'\r\n,ArrayTransRowValueFilter='$.dataCells[*].label'\r\n,RequestMethod='GET'\r\n)<\/pre>\n
             <\/li>\n<\/ol>\n
            Integrate Salesforce Data in ODBC Apps (e.g. Power BI, SSRS, Excel, Informatica, Access, C#…)<\/h2>\n
            ZappySys ODBC Drivers built using ODBC standard which is widely adopted by industry for a long time. Which mean the majority of BI Tools \/ Database Engines \/ ETL Tools already there will support native \/ 3rd party ODBC Drivers. Below is the small list of most popular tools \/ programming languages our Drivers support. If your tool \/ programming language doesn't appear in the below list, which means we have not documented use case but as long as your tool supports ODBC Standard, our drivers should work fine.\r\n\r\n \r\n\r\n\"ZappySys\r\n\r\n\r\n\r\n\r\n
            \r\n
            BI \/ Reporting Tools\r\nIntegration<\/strong><\/p>\r\n<\/td>\r\n
            		ETL Tools\r\nIntegration\r\n<\/strong><\/td>\r\nProgramming Languages<\/strong>\r\nIntegration<\/strong><\/td>\r\n<\/tr>\r\n
            \r\n\r\n\r\n