Introduction
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!!!
Prerequisites
Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:- SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
- (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.
- In case you missed the previous section, You need to Download and Install SSIS ZappySys PowerPack.
- Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
- Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
- Double click on the Data Flow task to see Data Flow designer surface.
- From the SSIS toolbox drag and drop JSON Source on the Data Flow designer surface.
- 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 URL1https://YOUR-INSTANCE.salesforce.com/services/data/v39.0/analytics/reports/YOUR-REPORT-ID
1https://na34.salesforce.com/services/data/v39.0/analytics/reports/00O61000001a97n - Check Use Credentials and configure Salesforce Connection
Create new REST API Connection in SSIS (For JSON, XML or CSV Source)
- Configure your Salesforce connection
- Select or type filter. For example in this case you will type $.factMap.T!T.rows[*]
- 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 URL1https://YOUR-INSTANCE.salesforce.com/services/data/v39.0/analytics/reports/YOUR-REPORT-ID
1https://naxx.salesforce.com/services/data/v39.0/analytics/reports/0xx61xx1xxxn
Configure – Read Salesforce Custom Reports Data
- Now Go to Array Transform Tab
- Select Transform Type : Complex 2-dimensional array
- Column name filter: $.reportMetadata.detailColumns[*]
- Row values filter: $.dataCells[*].label
- Here is how your Array Transform Tab configuration should look like
Configure – Advanced Array Transform (Read Salesforce Custom Reports Data in SSIS)
- 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
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?
Requirements
In order to access API data inside your App using ODBC Driver you will need to make sure following requirements are met.- 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.
- Follow this blog post to install JSON / REST Driver and configure Salesforce OAuth Connection.
- Once you can read some sample data described in previous step we are ready to run advanced queries.
- Go to Preview Tab and Run below Query to pull data from Custom Report
12345678910--change YOUR-INSTANCE and YOUR-REPORT-IDSELECT * FROM $WITH(Src='https://YOUR-INSTANCE.salesforce.com/services/data/v39.0/analytics/reports/YOUR-REPORT-ID',Filter='$.factMap.T!T.rows[*]',ArrayTransformType='TransformComplexTwoDimensionalArray',ArrayTransColumnNameFilter='$.reportMetadata.detailColumns[*]',ArrayTransRowValueFilter='$.dataCells[*].label',RequestMethod='GET')
Integrate Salesforce Data in ODBC Apps (e.g. Power BI, SSRS, Excel, Informatica, Access, C#…)
|
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
