Video tutorial
Watch this quick video to see the integration in action. It walks you through the end-to-end setup, including:
- Installing the SSIS PowerPack
- Configuring a secure connection to Amazon Ads
- Working with Amazon Ads data directly inside SSIS
- Exploring advanced API Source features
Ready to dive in? Download the product to jump right in, or follow the step-by-step guide below to see how it works.
Prerequisites
Before we begin, make sure the following prerequisites are met:
- SQL Server Data Tools (SSDT) designer installed for Visual Studio.
- SQL Server Integration Services Projects 2022+ Visual Studio extension installed.
- SSIS PowerPack is installed.
Make generic REST API request in SSIS
-
Open Visual Studio and click Create a new project.
-
Select Integration Services Project. Enter a name and location for your project, then click OK.
-
From the SSIS Toolbox, drag and drop a Data Flow Task onto the Control Flow surface, and double-click it:
-
Make sure you are in the Data Flow Task designer:
-
Read the data from the source, using any desired source component. You can even make an API call using the ZappySys JSON/XML/API Source and read data from there. In this example, we will use an OLE DB Source component to read real-time data from a SQL Server database.
-
From the SSIS Toolbox drag and drop API Destination (Predefined Templates) on the Data Flow Designer surface and connect source component with it, and double click to edit it.
-
Select New Connection to create a new connection:
API Destination - Amazon AdsRead and write Amazon Ads data effortlessly. Integrate, manage, and automate campaigns, ad groups, and performance metrics — almost no coding required.
-
To configure the Amazon Ads connector, choose one of the following methods:
- Choose from Popular Connector List: Select a pre-installed service directly from the dropdown menu.
- Search Online: Use this to find and download a new connector file to your computer.
- Use Saved/Downloaded File: Once the file is downloaded, browse your local drive to load it into the configuration.
After that, just click Continue >>:
Amazon Ads
-
Proceed with selecting the desired Authentication Type. Then select API Base URL (in most cases default one is the right one). Finally, fill in all the required parameters and set optional parameters if needed. You may press a link Steps to Configure which will help set certain parameters. More info is available in Authentication section.
OAuth Authentication
Amazon Ads authentication
Amazon OAuth Security Profile has be created in Amazon Developer portal. [API reference]
Firstly, login into Amazon Developer portal in https://developer.amazon.com.
Then click Sign in.
If you don't have an account, press Create your Amazon Developer account button to create one.
-
Once logged in, click on your profile icon on the right-top of the window and hit Settings:
-
Then click Security Profiles and proceed with creating a new Security Profile:
-
After that, click Web Settings subtab, then hit Show Secret button to reveal the Client Secret (you will need it later), and click Edit to add a Redirect URL:
-
Continue by setting Allowed Return URLs (you can have your own or use
https://zappysys.com/oauth):
-
Done! Now you can use Client ID, Client Secret, and Redirect URL when configuring OAuth Authentication parameters:
API Connection Manager configuration
Just perform these simple steps to finish authentication configuration:
-
Set Authentication Type to
OAuth Authentication [OAuth] - Optional step. Modify API Base URL if needed (in most cases default will work).
- Fill in all the required parameters and set optional parameters if needed.
- Press Generate Token button to generate the tokens.
- Finally, hit OK button:
Amazon AdsOAuth Authentication [OAuth]https://advertising-api.amazon.com/v2Required Parameters ClientId Fill-in the parameter... ClientSecret Fill-in the parameter... AuthorizationUrl Fill-in the parameter... ReturnUrl Fill-in the parameter... Scope Fill-in the parameter... Profile (click 'Generate Token' before this) Fill-in the parameter... Optional Parameters RetryMode RetryWhenStatusCodeMatch RetryStatusCodeList 429 RetryCountMax 5 RetryMultiplyWaitTime True 
Find full details in the Amazon Ads Connector authentication reference. -
Select Generic Table (Bulk Read / Write) table from the dropdown, then select Insert, Update as operation, and hit Preview Data:
API Destination - Amazon AdsRead and write Amazon Ads data effortlessly. Integrate, manage, and automate campaigns, ad groups, and performance metrics — almost no coding required.Amazon AdsGeneric Table (Bulk Read / Write)Insert, UpdateRequired Parameters Url Fill-in the parameter... Request Method Fill-in the parameter... Optional Parameters IsMultiPart Filter Request Format (Content-Type) Default Body {$rows$} JsonOutputFormat Multicontent DoNotOutputNullProperty Batch Size (Default=1) 1 Meta Detection Order StaticDynamicVirtual Input Columns - For Mapping (e.g. MyCol1:string(10); MyCol2:int32 ...) - Use bool, int32, int64, datetime, decimal, double Output Columns (e.g. MyCol1:string(10); MyCol2:int32 ...) - Use bool, int32, int64, datetime, decimal, double Request Format Response Format Default Headers Accept: */* || Cache-Control: no-cache Csv - Column Delimiter , Csv - Row Delimiter {NEWLINE} Csv - Quote Around Value True Csv - Always Quote regardless type Encoding CharacterSet Writer DateTime Format Csv - Has Header Row True Xml - ElementsToTreatAsArray Layout Map <?xml version="1.0" encoding="utf-8"?> <!-- Example#1: Output all columns --> <settings> <dataset id="root" main="True" readfrominput="True" /> <map src="*" /> </settings> <!-- Example#2: Records under array <?xml version="1.0" encoding="utf-8"?> <settings singledataset="True"> <dataset id="root" main="True" readfrominput="True" /> <map name="MyArray" dataset="root" maptype="DocArray"> <map src="OrderID" name="OrderID" /> <map src="OrderDate" name="OrderDate" /> </map> </settings> --> <!-- Example#3: Records under nested section <?xml version="1.0" encoding="utf-8"?> <settings> <dataset id="dsRoot" main="True" readfrominput="True" /> <map name="NestedSection"> <map src="OrderID" name="OrderID_MyLabel" /> <map src="OrderDate" name="OrderDate_MyLabel" /> </map> </settings> --> 
-
Finally, map the desired columns:
API Destination - Amazon AdsRead and write Amazon Ads data effortlessly. Integrate, manage, and automate campaigns, ad groups, and performance metrics — almost no coding required.
-
That's it; we successfully configured the POST API Call. In a few clicks we configured the Amazon Ads API call using ZappySys Amazon Ads Connector
Amazon Ads Connector actions
Need another use case? Pick the next Amazon Ads action in SSIS below.
Conclusion
You now know how to make generic REST API request (bulk write) in SSIS without writing complex code. Amazon Ads Connector handled pagination and authentication automatically.
Ready to get started? Download the trial or ping us via chat if you need help:
