SSIS API Source
 PreviousNext

SSIS API Source can be used to read data from popular API Data Sources using predefined Connector File without learning internals of API Calls. This component allows you make API calls and read API data as Flat Table.

Download SSIS PowerPack

Content

Video Tutorial

Step-By-Step

In this tutorial we will learn how to extract data from API Server using ZS API Source. For example purpose we will use Google Sheets Connector but concept is same for Other Connectors.
  1. Download and Install SSIS ZappySys PowerPack.
  2. Once you finished the first step, Open Visual Studio and Create New SSIS Package Project.
  3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
    SSIS Data Flow Task - Drag and Drop
  4. Double click on the Data Flow task to see Data Flow designer surface.
  5. Here, In Visual Studio, drag and drop the ZS API Source in the design surface.
    SSIS API Source - Drag and Drop

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 Source to edit. On UI Click [New] Connection.
    SSIS API Source - Create API Connector
  2. Choose [OData] from Popular Connectors dropdown and Click Continue.
    SSIS API connection
  3. This example doesn't need any authentication so leave everything else default. However in real world you will need to supply Authentication information.
    SSIS API connection
  4. Click Test Connection to confirm everything is good.
  5. Click OK to save UI and go back to API Source UI.

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.
    SSIS Create New Connection
  2. Select ZS-API Connection Manager from the Connection Managers list and Click on Add Button.
    SSIS API Connection - ADD
  3. On New Connection Dialogbox configure using same steps we described in the previous section (Method #1).
  4. Click on Test Connection to check configure correct or not.
  5. Click on OK button to save API Connection Manager configure setting UI.

How to read data from API Service

  1. On API Source UI now you can choose EndPoint from the dropdown. Choose EndPoint (i.e. Read Orders) for example.
    SSIS API Source - Configure
  2. Click Preview to see sample data.
  3. Click on the Columns Tab to review output columns. Here you can uncheck unwanted columns from the output so they dont appear downstream.
    SSIS API Source - Columns Configure
  4. Now click OK to save UI.
  5. From the SSIS toolbox drag and drop Trash Destination on the data flow designer surface.
    SSIS Trash Destination - Drag and Drop
  6. Now single click on the API Source, once you see blue arrow from source ... connect it to Trash Destination.
  7. Double click on the ZS Trash Destination to Configure it.
    SSIS Trash Destination - Configure
  8. Click on OK button to save Trash Destination UI.
  9. Execute the package and verify data in file (used in Trash Destination).
    SSIS API Source Execute - Table Mode

API Connector Files (for Enterprise Edition Only)

Following Pre-Configured API connectors are now available with our brand new API Source and API Destination.

How to parametrize values

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

Method #1 - Placeholder Engine

SSIS API Source / Destination - Using Variable for Dynamic Value

Method #2 - SSIS Expression

SSIS Expression Editor - Use of Variable / Parameters

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
ErrorStatusCodeToMatchRegex Status code(s) to match - separated by vertical bar (e.g. 404|405). When error occurs and ContineOnErrorForStatusCode option is true then if StatusCode matches to this code(s) 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
MaxRows Maximum number of rows to fetch from source. 0=Unlimited
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.

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

Settings UI

SSIS API Source Execute - Setting UI
SSIS API Source Columns
SSIS API Source General
SSIS API Source Error Handling Options

References

See Also

Articles / Tutorials

Click here to see all articles for [SSIS API Source] category
Export API Table Data to SQL Server in SSIS

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 Source. API Source 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

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 Source. API Source 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 […]

Copyrights reserved. ZappySys LLC.