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.
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.
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 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 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.
1https://na34.salesforce.com/services/data/v20.0/sobjects/Account/describe/ - Check Use Connection and select New ZS-Salesforce Connection from the list.
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 $.sobjects[*]
- Click OK to Save UI and attach your source to some target like OLEDB Destination and run your data flow to test.
- Here is full UI after all configurations.
Read Salesforce Metadata in SSIS (Get Table List Example – SOObject REST API Call)
How to read Salesforce Table Fields Metadata
If you have need to read fields and its information for tables then use below steps
- Use almost identical steps as previous section. Except two things. Our URL and Filter would be different.
- Configure your API URL as below
Example:1https://YOUR-INSTANCE.salesforce.com/services/data/v20.0/sobjects/YOUR-TABLE/describe/
1https://demo.salesforce.com/services/data/v20.0/sobjects/Account/describe/ - Configure Filter as a $.fields[*]
Get Salesforce Table Fields Metadata using REST API call in SSIS
