Salesforce Connector for Informatica How to Make Generic API Request

Introduction

In this article we will delve deeper into Salesforce and Informatica integration, and will learn how to make generic api request. We are continuing from where we left off. By this time, you must have installed ODBC PowerPack, created ODBC Data Source, and configured authentication settings in your Salesforce account .

So, let's not waste time and begin.

Use Query Builder to generate SQL query

The first thing you have to do is open Query Builder:

ZappySys API Driver - Salesforce

Amazon Ads Connector can be used to get Amazon advertisements using Amazon Advertisements API, download various advertisement-related reports.

SalesforceDSN
Then simply select the Make Generic API Request endpoint (action).
Continue by configuring the Required parameters. You can also set optional parameters too.
Move on by hitting Preview Data button to preview the results.

If you see the results you need, simply copy the generated query:

Make Generic API Request

Required Parameters
HTTP - Url or File Path	Select the value from the dropdown
Optional Parameters
HTTP - Request Body
HTTP - Is MultiPart Body (Pass File data/Mixed Key/value)
HTTP - Headers (e.g. hdr1:aaa \|\| hdr2:bbb)	Accept: / \|\| Cache-Control: no-cache
Parser - Filter (e.g. $.rows[*] )
Download - Enable reading binary data	False
Download - File overwrite mode	AlwaysOverwrite
Download - Save file path
Download - Enable raw output mode as single row	False
Download - Raw output data RowTemplate	{Status:'Downloaded'}
Download - Request Timeout (Milliseconds)
Advanced Properties
HTTP - Request Method	GET
HTTP - Request Format (Content-Type)	ApplicationJson
Parser - Response Format (Default=Json)	Default
Parser - Encoding
Parser - CharacterSet
General - Enable Custom Search/Replace
General - SearchFor (e.g. (\d)-(\d)--regex)
General - ReplaceWith (e.g. $1-***)
General - File Compression Type
General - Date Format
General - Enable Big Number Handling	False
General - Wait time (Ms) - Helps to slow down pagination (Use for throttling)	0
JSON/XML - ExcludedProperties (e.g. meta,info)
JSON/XML - Flatten Small Array (Not preferred for more than 10 items)
JSON/XML - Max Array Items To Flatten	10
JSON/XML - Array Transform Type
JSON/XML - Array Transform Column Name Filter
JSON/XML - Array Transform Row Value Filter
JSON/XML - Array Transform Enable Custom Columns
JSON/XML - Enable Pivot Transform
JSON/XML - Array Transform Custom Columns
JSON/XML - Pivot Path Replace With
JSON/XML - Enable Pivot Path Search Replace	False
JSON/XML - Pivot Path Search For
JSON/XML - Include Pivot Path	False
JSON/XML - Throw Error When No Match for Filter	False
JSON/XML - Parent Column Prefix
JSON/XML - Include Parent When Child Null	False
Pagination - Mode
Pagination - Attribute Name (e.g. page)
Pagination - Increment By (e.g. 100)	1
Pagination - Expression for Next URL (e.g. $.nextUrl)
Pagination - Wait time after each request (milliseconds)	0
Pagination - Max Rows Expr
Pagination - Max Pages Expr
Pagination - Max Rows DataPath Expr
Pagination - Max Pages	0
Pagination - End Rules
Pagination - Next URL Suffix
Pagination - Next URL End Indicator
Pagination - Stop Indicator Expr
Pagination - Current Page
Pagination - End Strategy Type	DetectBasedOnRecordCount
Pagination - Stop based on this Response StatusCode
Pagination - When EndStrategy Condition Equals	True
Pagination - Max Response Bytes	0
Pagination - Min Response Bytes	0
Pagination - Error String Match
Pagination - Enable Page Token in Body	False
Pagination - Placeholders (e.g. {page})
Pagination - Has Different NextPage Info	False
Pagination - First Page Body Part
Pagination - Next Page Body Part
Csv - Column Delimiter	,
Csv - Has Header Row	True
Csv - Throw error when column count mismatch	False
Csv - Throw error when no record found	False
Csv - Allow comments (i.e. line starts with # treat as comment and skip line)	False
Csv - Comment Character	#
Csv - Skip rows	0
Csv - Ignore Blank Lines	True
Csv - Skip Empty Records	False
Csv - Skip Header Comment Rows	0
Csv - Trim Headers	False
Csv - Trim Fields	False
Csv - Ignore Quotes	False
Csv - Treat Any Blank Value As Null	False
Xml - ElementsToTreatAsArray

SELECT * FROM __DynamicRequest__

That's it! You can use this query in Informatica.

Let's not stop here and explore SQL query examples, including how to use them in Stored Procedures and Views (virtual tables) in the next steps.

SQL query examples

Use these SQL queries in your Informatica data source:

How to Get DynamicRequest

SELECT * FROM __DynamicRequest__

generic_request endpoint belongs to __DynamicRequest__ table(s), and can therefore be used via those table(s).

Make Generic API Request in Informatica

Open Informatica Mapping Designer (Click [D] icon)
Click on Source Icon to switch to Sources designer
From the top menu > Click on Sources > Import from Database …

Import Salesforce Source definition in Informatica Mapping Designer (JSON file or REST API)
Select ODBC data source from the dropdown (Find out DSN we created earlier to use as JSON Source)
Click Connect button to get a list of tables. Any array node is listed as a table. Also, you will see array node with parent columns (e.g. value_with_parent). You may get some warning like below but they are harmless so just ignore by clicking OK.

DLL name entry missing from C:\Informatica\PowerCenter8.6.1\client\bin\powrmart.ini Section = ODBCDLL Entry = ZappySys JSON Driver
—————————————————-
Using EXTODBC.DLL to support ZappySys JSON Driver. For native support of ZappySys JSON Driver make an entry in the .ini file.

Select Salesforce Source Table in Informatica Mapping Designer (JSON file or REST API)
Select Table you wish to get (You can filter rows by custom SQL query. We will see later in this article how to do)
Optionally once table structure is imported you can rename it

Rename imported table definition in Informatica Source Designer