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.
PrerequisitesBefore we perform steps listed in this article, you will need to make sure following prerequisites are met:
- SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it).
- Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages
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.
- Check Use Connection and select New ZS-Salesforce Connection from the list.
- 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.
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
- Configure Filter as $.fields[*]