OData Connector
Documentation
Version: 5
Documentation

Write data to OData using SSIS (Import data)


In this section we will learn how to configure and use OData Connector in the API Destination to write data to OData.

Video tutorial

This video covers following and more so watch carefully. After watching this video follow the steps described in this article.

  • How to download SSIS PowerPack for OData integration in SSIS
  • How to configure connection for OData
  • How to write or lookup data to OData
  • Features about SSIS API Destination
  • Using OData Connector in SSIS

Step-by-step instructions

In upper section we learned how to read data, now in this section we will learn how to configure OData in the API Source to POST data to the OData.

  1. Read the data from the source, being any desired source component. In example we will use ZappySys Dummy Data Source component.

  2. 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.
    SSIS API Destination (Predefined Templates) - Drag and Drop

  3. Select New Connection to create a new connection:

    API Destination - OData
    OData Connector can be used to integrated any REST API which supports OData Standard. You can view tables, read data from tables using this connector. It also comes with some example OData Services to get started.
    API Destination - New Connection

  4. Use a preinstalled OData Connector from Popular Connector List or press Search Online radio button to download OData Connector. Once downloaded simply use it in the configuration:

    OData
    OData Connector Selection

  5. 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.

    Fill in all required parameters and set optional parameters if needed:

    OdataDSN
    OData
    No Authentication [Http]
    https://services.odata.org/V3/Northwind/Northwind.svc
    Optional Parameters
    RetryMode RetryWhenStatusCodeMatch
    RetryStatusCodeList 429
    RetryCountMax 5
    RetryMultiplyWaitTime True
    IgnoreSSLCertificateErrors
    SslVersion
    ODBC DSN HTTP Connection Configuration

    Fill in all required parameters and set optional parameters if needed:

    OdataDSN
    OData
    Windows Authentication [Http]
    https://services.odata.org/V3/Northwind/Northwind.svc
    This Authentication Type has no parameters to fill-in.
    ODBC DSN HTTP Connection Configuration

    Fill in all required parameters and set optional parameters if needed:

    OdataDSN
    OData
    Basic Authentication [Http]
    https://services.odata.org/V3/Northwind/Northwind.svc
    Required Parameters
    UserName Fill-in the parameter...
    Password Fill-in the parameter...
    Optional Parameters
    RetryMode RetryWhenStatusCodeMatch
    RetryStatusCodeList 429
    RetryCountMax 5
    RetryMultiplyWaitTime True
    IgnoreSSLCertificateErrors
    SslVersion
    ODBC DSN HTTP Connection Configuration

    Fill in all required parameters and set optional parameters if needed:

    OdataDSN
    OData
    NTLM Authentication [Http]
    https://services.odata.org/V3/Northwind/Northwind.svc
    Required Parameters
    UserName (i.e. Domain\UserName) Fill-in the parameter...
    Password Fill-in the parameter...
    Optional Parameters
    RetryMode RetryWhenStatusCodeMatch
    RetryStatusCodeList 429
    RetryCountMax 5
    RetryMultiplyWaitTime True
    IgnoreSSLCertificateErrors
    SslVersion
    ODBC DSN HTTP Connection Configuration

    Fill in all required parameters and set optional parameters if needed:

    OdataDSN
    OData
    Token Authentication [Http]
    https://services.odata.org/V3/Northwind/Northwind.svc
    Required Parameters
    Token Fill-in the parameter...
    Optional Parameters
    AuthScheme Bearer
    AuthHeader Authorization
    RetryMode RetryWhenStatusCodeMatch
    RetryStatusCodeList 429
    RetryCountMax 5
    RetryMultiplyWaitTime True
    IgnoreSSLCertificateErrors
    SslVersion
    ODBC DSN HTTP Connection Configuration

    Fill in all required parameters and set optional parameters if needed:

    OdataDSN
    OData
    OAuth Connection for OData API [OAuth]
    https://services.odata.org/V3/Northwind/Northwind.svc
    Optional Parameters
    GrantType Default
    OAuthVersion Default
    ClientId
    ClientSecret
    UserName
    Password
    Scope
    AuthUrl https://some-domain.com/api/auth
    TokenUrl https://some-domain.com/api/token
    ReturnUrl https://zappysys.com/oauth
    ExtraAttributesForTokenRequest
    ExtraAttributesForAuthRequest
    DoNotIncludeCredentialsInHeader
    DoNotIncludeCredentialsInBody
    RenewBeforeSec
    ExtraHeaders (e.g. x-hdr1:AAA||x-hdr2:BBB)
    ContentType
    EnableCodeVerifier
    RemoveAuthHeader
    CustomAuthHeader
    CustomTokenPrefix
    RetryMode RetryWhenStatusCodeMatch
    RetryStatusCodeList 429
    RetryCountMax 5
    RetryMultiplyWaitTime True
    IgnoreSSLCertificateErrors
    SslVersion
    ODBC DSN Oauth Connection Configuration

  6. Select the desired endpoint, change/pass the properties values, and go to the Mappings tab to map the columns.

    API Destination - OData
    OData Connector can be used to integrated any REST API which supports OData Standard. You can view tables, read data from tables using this connector. It also comes with some example OData Services to get started.
    API Destination - Select Endpoint

  7. Finally, map the desired columns:

    API Destination - OData
    OData Connector can be used to integrated any REST API which supports OData Standard. You can view tables, read data from tables using this connector. It also comes with some example OData Services to get started.
    API Destination - Columns Mapping

  8. That's it; we successfully configured the POST API Call. In a few clicks we configured the OData API call using ZappySys OData Connector

    Execute Package - Reading data from API Source and load into target