Video Tutorial

Content

• Video Tutorial
• Step-By-Step
• How to parameterize values
• Properties
• Error Handling
• Settings UI
• FAQ
• References
• See also

Video Tutorial

Coming soon...

Step-By-Step

In this tutorial we will learn how to write data to API Datasource using ZS API Destination. For example purpose we will use Google Sheets Connector and write sample rows but concept is same for other API Connectors too.

1. Download and Install SSIS ZappySys PowerPack (Skip this if you already installed SSIS PowerPack).

2. Once you finished the previous step, Open Visual Studio and Create New SSIS Package Project.

3. Drag and Drop SSIS Data Flow Task from SSIS Toolbox and Double click on it

   
   
4. Drag and drop any source in the data flow designer surface (for this example we will use Dummy Data Source)
   
5. Now drag and drop the ZS API Destination in the design surface.
   

6. Select Dummy Data Source and you will see blue arrow coming out from it. Drag Blue arrow on API Destination to link the flow

   
   

How to create API Connection Manager

Now lets look at how to create API connection which can be used with API Source or Destination. Basically there are two ways you can create API Connection Manager

Method #1 - Create connection from UI (Recommended)

1. Double click API Destination to edit. On UI Click [New] Connection.
   

Method #2 - Create connection from Connection Manager Panel

Here is another way of creating connection manager.

1. Right click in the Connection Manager panel and click on New Connection.
   
2. Select ZS-API Connection Manager from the Connection Managers list and Click on Add Button.
   

Configure API Connection

Method #1 - Choose [OData] from Popular Connectors dropdown and Click Continue

Method #2 - Select Search Online and select the one you need

Method #3 - Select a local file

Method #4 - Select Direct Config XML and insert a valid XML connection

Configure API Destination to write data to API Service

Now let's configure API Destination.

1. Double click on API Destination to configure it.
2. In the API Destination, select connection we have created in previous section, select Table > Sheets.
3. From the Table / EndPoints dropdown choose Table > Sheets.
4. Now make sure operation dowpdown is selected as Insert.
   
5. Now click on the mappings tab, some columns are mapped automatically when you come on this UI first time. Verify the columns mapped correctly. For dynamic Row by Row operation you can also map Parameters (starting with $$).
   
6. Now click on the response output tab to check the output columns
   
7. Click OK to save API Destination UI.
8. From the SSIS toolbox drag and drop Trash Destination on the data flow designer surface.
9. Now single click on the API Destination, once you see blue arrow from source ... connect it to Trash Destination.
10. Double click on ZS Trash Destination to Configure it.
    
11. Click on OK button to save Trash Destination configure setting UI.
12. Execute the package and verify source data in file.
    

How to parametrize values

There are mainly three ways you can parameterize for API Destination. Check this help page to learn more about this approach.

Method #1 - Mapping Screen (Map Upstream column to Parameter)

In previous section we saw how to Map Parameter to some upstream column to do row-by-row operation which gives you dynamic value for parameter at runtime. However there is one drawback for this approach. When you map Parameter on mapping screen (i.e. Map $$ prefixed names) it always invokes row-by-row operation regrdless Target has Bulk support or not.

Method #2 - Placeholder Engine

Method #3 - SSIS Expression

Properties

Property Name	Description
TableName	Table name. This option only used when AccessMode=Table
Parameters	Parameters overrides for selected endpoint. Value set of these parameters overrides any default value defined in API Template
ContinueOnUrlNotFoundError	If this option is true then component will continue without exception on 404 error (Url not found). This allows you to consume data gracefully.
ContineOnAnyError	Continue when any type of exception occurs during http request
ContineOnErrorForMessage	Continue on error when specified substring found in response
ContineOnErrorForStatusCode	Continue on error when specified status code returned from web server
ConsumeResponseOnError	When error occurs no data is returned. Use this option to get content eventhough error occurs. When this option is checked you can't use [continue on error when specific string found in response] option
ErrorStatusCodeToMatch	Status code to match when error occurs and ContineOnErrorForStatusCode option is true. If Response status code matches to this code then task continues to run
ErrorSubstringToMatch	Error substring to match when error occurs and ContineOnErrorForMessage option is true. If Response status code matches to this code then task continues to run
Operation	Specifies operation for destination data load (e.g. Insert, Update, Delete etc). Default operation is Insert. Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior). Option Description Insert [0] Insert (i.e. Create New) Update [1] Update Delete [2] Delete Lookup [3] Lookup Upsert [4] Upsert (Update or Insert) Select [5] Select
OutputRaw	Output raw data instead of parsed rows/columns in the response output for downstream. This option is ignored if EnableResponseOutput is not true.
LoggingMode	LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log. Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior). Option Description Normal [0] Normal Medium [1] Medium Detailed [2] Detailed Debugging [3] Debugging
PrefixTimestamp	When you enable this property it will prefix timestamp before Log messages.
DoNotCreateOutputs	Enable / Disable Response Output. Checking this option can remove unwanted warnings about unused Columns and many times speedup performance so if output is not needed set this option to True so its removed.

Error Handling

• This component supports error output.
• Any bad value will cause Error or Truncation (e.g. If your sample value is 5 character long but output column length is set to 3 chars). If you have error output attached with RedirectRow option on Error, Truncation then you can capture bad rows along with exact reason of error.
• Use error handling tab to set option for error handling. By default component fails on any data error or truncation issue

References

• API Homepage

See Also

• SSIS API Destination Adapter
• SSIS API ExecuteSql Task
• SSIS MongoDB Destination Adapter
• SSIS MongoDB Connection Manager

Articles / Tutorials

Click here to see all articles for [SSIS API Destination] category

Export API Table Data to SQL Server in SSIS ZappySys provides high performance drag and drop connectors for API Integration. In this post you will see how to Export API Table Data to SQL Server Table using SSIS API Destination. API Destination supports SQL language to query API data. API is a powerful, open source relational database with strong reputation for reliability, feature robustness, and performance. […]

Load API Table Data to CSV File in SSIS Introduction ZappySys provides high performance drag and drop connectors for API Integration. In this post you will see how to Load API Table Data to CSV File using SSIS API Destination. API Destination supports SQL language to query API data. API is a powerful, open source relational database with strong reputation for reliability, feature robustness, and performance. In […]