Prerequisites

Before we begin, make sure the following prerequisites are met:

  1. SQL Server Data Tools (SSDT) designer installed for Visual Studio.
  2. SQL Server Integration Services Projects 2022+ Visual Studio extension installed.
  3. SSIS PowerPack is installed.

Upsert documents in SSIS

  1. Open Visual Studio and click Create a new project.

  2. Select Integration Services Project. Enter a name and location for your project, then click OK.

  3. From the SSIS Toolbox, drag and drop a Data Flow Task onto the Control Flow surface, and double-click it:

    Drag Data Flow Task onto Control Flow to use SSIS PowerPack Data Flow components

  4. Make sure you are in the Data Flow Task designer:

    Make sure you are in Data Flow designer in SSIS package

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

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

  7. Select New Connection to create a new connection:

    API Destination - ElasticSearch
    Read and write Elasticsearch data effortlessly. Integrate, manage, and automate indexes and documents — almost no coding required.
    API Destination - ElasticSearch

  8. To configure the ElasticSearch 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 >>:

    ElasticSearch
    API Destination -

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

    Basic Authentication (UserId/Password)

    ElasticSearch authentication

    For Local / Hosted Instance by you

    1. Get your userid / password and enter on the connection UI

    For Managed Instance (By Bonsai search)

    If your instance is hosted by bonsai then perform these steps to get your credentials for API call
    1. Go to https://app.bonsai.io/clusters/{your-instance-id}/tokens
    2. Copy Access Key and Access Secret and enter on the connection UI. Click Test connection.
    3. If your Cluster has no data you can generate sample data by visiting this URL and click Add Sample Data https://{your-cluster-id}.apps.bonsaisearch.net/app/home#/tutorial_directory

    API Connection Manager configuration

    Just perform these simple steps to finish authentication configuration:

    1. Set Authentication Type to Basic Authentication (UserId/Password) [Http]
    2. Optional step. Modify API Base URL if needed (in most cases default will work).
    3. Fill in all the required parameters and set optional parameters if needed.
    4. Finally, hit OK button:
    ElasticSearch
    Basic Authentication (UserId/Password) [Http]
    http://localhost:9200
    Optional Parameters
    User Name (or Access Key)
    Password (or Access Secret)
    Ignore certificate related errors
    ZappySys Http Connection
    Find full details in the ElasticSearch Connector authentication reference.
    Windows Authentication (No Password)

    ElasticSearch authentication

    No instructions available.

    API Connection Manager configuration

    Just perform these simple steps to finish authentication configuration:

    1. Set Authentication Type to Windows Authentication (No Password) [Http]
    2. Optional step. Modify API Base URL if needed (in most cases default will work).
    3. Fill in all the required parameters and set optional parameters if needed.
    4. Finally, hit OK button:
    ElasticSearch
    Windows Authentication (No Password) [Http]
    http://localhost:9200
    Optional Parameters
    Ignore certificate related errors
    ZappySys Http Connection
    Find full details in the ElasticSearch Connector authentication reference.

  10. Select [Dynamic Table] table from the dropdown, then select Upsert as operation, and hit Preview Data:

    API Destination - ElasticSearch
    Read and write Elasticsearch data effortlessly. Integrate, manage, and automate indexes and documents — almost no coding required.
    ElasticSearch
    [Dynamic Table]
    Upsert
    Optional Parameters
    RowHeaderFooterContinueOnError True
    RowHeader
    SSIS API Destination - Access table operation

  11. Finally, map the desired columns:

    API Destination - ElasticSearch
    Read and write Elasticsearch data effortlessly. Integrate, manage, and automate indexes and documents — almost no coding required.
    API Destination - ElasticSearch

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

    Execute Package

Deploy SSIS package to Azure Data Factory (ADF)

Once your SSIS package is complete, deploy it to the Azure-SSIS runtime within Azure Data Factory. The setup process requires you to upload the SSIS PowerPack installer to Azure Blob Storage and then customize the runtime configuration using the main.cmd file. For a complete walkthrough of these steps, see our detailed guide on the Azure Data Factory (SSIS) and ElasticSearch integration.

ElasticSearch Connector actions

Need another use case? Pick the next ElasticSearch action in Azure Data Factory (SSIS) below.

Conclusion

You now know how to upsert documents in Azure Data Factory (SSIS) without writing complex code. SSIS ElasticSearch Connector handled pagination and authentication automatically.

Ready to get started? Download the trial or ping us via chat if you need help:

Download SSIS PowerPack
Chat with an Expert