Read from Salesforce Custom Reports in SSIS / ODBC


In our previous posts we saw how to read and write 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 read Salesforce data from Custom Reports in SSIS which will use JSON Source rather than Salesforce Source.

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.

So let’s get started!!!


Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
  4. (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.

Read Salesforce Custom Reports in SSIS

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 to get all table information) .

So lets get started.

  1. In case you missed the previous section, You need to Download and Install SSIS ZappySys PowerPack.
  2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
  3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
    SSIS Data Flow Task - Drag and Drop
  4. Double click on the Data Flow task to see Data Flow designer surface.
  5. From the SSIS toolbox drag and drop JSON Source on the Data Flow designer surface.
    SSIS JSON Source - Drag and Drop
  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.
    Example URL 
  7. Check Use Credentials and configure Salesforce Connection
    Create new REST API Connection in SSIS (For JSON, XML or CSV Source)

    Create new REST API Connection in SSIS (For JSON, XML or CSV Source)

  8. Configure your Salesforce connectionSSIS Salesforce Connection Manager UI
  9. Select or type filter. For example in this case you will type  $.factMap.T!T.rows[*]
  10. Uncheck Use Parents option (You can keep it but it will add many unwanted fields)
    NOTE: 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.
    Example URL 

    Configure – Read Salesforce Custom Reports Data

  11. Now Go to Array Transform Tab
    1. Select Transform Type : Complex 2-dimensional array
    2. Column name filter: $.reportMetadata.detailColumns[*]
    3. Row values filter: $.dataCells[*].label
  12. Here is how your Array Transform Tab configuration should look like
    Configure - Advanced Array Transform (Read Salesforce Custom Reports Data in SSIS)

    Configure – Advanced Array Transform (Read Salesforce Custom Reports Data in SSIS)

  13. Now Click OK to Save UI and attach your source to some target like OLEDB Destination and run your data flow to test.
    Extract Data from Salesforce Custom Reports in SSIS - Load into Target like SQL Server, MySQL, PostgreSql, Oracle

    Extract Data from Salesforce Custom Reports in SSIS – Load into Target like SQL Server, MySQL, PostgreSql, Oracle

Read Salesforce Custom Reports in ODBC Apps

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?


In order to access API data inside your App using ODBC Driver you will need to make sure following requirements are met.
  1. Download and Install ZappySys ODBC PowerPack (This includes XML / JSON / REST API and few other drivers for SQL Server and ODBC connectivity in tools like Excel, Power BI, SSRS)

ZappySys Provides native Salesforce Driver to read / write Salesforce Object’s data (Its part of ODBC PowerPack). This driver can read/write standard objects (e.g. Accounts, Contacts) However reading from salesforce custom report is not supported by native driver. For this purpose you have to use JSON / REST Driver using this approach. 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

So let’s get started.

  1. Follow this blog post to install JSON / REST Driver and configure Salesforce OAuth Connection.
  2. Once you can read some sample data described in previous step we are ready to run advanced queries.
  3. Go to Preview Tab and Run below Query to pull data from Custom Report

Integrate Salesforce Data in ODBC Apps (e.g. Power BI, SSRS, Excel, Informatica, Access, C#…)

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.   ZappySys ODBC Drivers for REST API, JSON, XML - Integrate with Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, Excel, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#,, PHP. PowerShell

BI / Reporting Tools Integration

ETL Tools Integration Programming Languages Integration


In this post we saw how easy it is to perform Salesforce Data Integration in SSIS or any other apps using ZappySys SSIS Connectors or ODBC Drivers. Download FREE Trial of SSIS PowerPack or if you need to integration in other apps then get ODBC PowerPack here


Posted in JSON File / REST API Driver, REST API, REST API Integration, SSIS JSON Source (File/REST), SSIS Salesforce Connection and tagged , , .