Read from Salesforce Custom Reports in SSIS / ODBC

Introduction

In our previous posts we saw how to read and write Salesforce Data using SSIS. In this post we will see how to read Salesforce Metadata in SSIS using JSON 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.

Prerequisites

Before we perform steps listed in this article, you will need to make sure following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from 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).
  4. Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages

Step-By-Step – Integrate 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)
    Configure - Read Salesforce Custom Reports Data

    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

Step-By-Step – Reading 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?

Requirements

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#, VB.net, PHP. PowerShell

BI / Reporting Tools Integration

ETL Tools Integration Programming Languages Integration
 

Conclusion

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 , , .