How to translate text using the Google Translate API in SSIS

SSIS General
1

Introduction

This article provides a step-by-step guide on connecting to the Google Translate API using SSIS. Whether you need to automate translations for customer support, marketing content, or internal communications, this integration will enable you to efficiently retrieve and manage translation data directly within your SSIS packages.

Prerequisites

  • SSIS PowerPack: Download and install the ZappySys SSIS PowerPack from here.
  • Google Account

Steps

Create/Select a Project in the Google API Console

  1. Navigate to the Google API Console.

  2. Click on the Project Dropdown in the top bar and either select an existing project or create a new one by clicking CREATE PROJECT.

    Google API Console - Create Project|auto
    Google API Console - Create Project|auto744×230 20.9 KB

  3. Once the project is set, click ENABLE APIS AND SERVICES.

  4. Enable the Cloud Translation API by searching for it and clicking ENABLE.
    Enable Service

  5. Return to the main screen and click on the OAuth Consent Screen tab. Provide the necessary details and save.

  6. Move to the Credentials tab.

  7. Click CREATE CREDENTIALS in the top bar, and choose API Key. You will receive a new API key, which you should save for later use.

    Create API Key
    Create API Key866×334 43 KB

Connect with Google Translate API in SSIS JSON Source

  1. Drag and drop a Data Flow task into your SSIS package.

  2. Add a JSON Source component inside the Data Flow.

  3. Enter the following URL:
    https://translation.googleapis.com/language/translate/v2

  4. Select the POST method and add the following JSON in the Body to translate English to French::

    {
  "q": "This is an example of a JSON source",
  "source": "en",
  "target": "fr",
  "format": "text"
}

    For more details on each parameter, refer to the Google Translate API documentation:

    • q: The text to be translated.
    • source: The text’s source language is set to one of the language codes listed in the Language Support. If not specified, the API will attempt to detect the language automatically.
    • target: The translation’s target language is set to one of the language codes listed in the Language Support.
    • format: The format of the source text (e.g., text or HTML).

  5. Set the Body content type to JSON(application/json) and include your API key in the Header:
    Authorization: Bearer YOUR_API_KEY

  6. For the Array Filter, use the following to retrieve the translated text:
    $.data.translations[*].translatedText

  7. Click on Preview to see the result, and then press OK to save the configuration.

    JSON Source Configuration
    JSON Source Configuration830×737 77.9 KB

  8. Use a destination component and run the package.
    JSON Example

Connect with Google Translate API in SSIS REST API Task

  1. Drag and drop a REST API Task into your SSIS package.

  2. Use the same configuration from the JSON Source task: the same URL, Body, and Header.

    REST API Task Configuration
    REST API Task Configuration840×717 50.6 KB

  3. In the Response Settings tab, apply a filter and save the result in a variable or a file.

    REST API Task Filter Option
    REST API Task Filter Option840×279 24.2 KB

Considerations

Save your API Key to avoid having to retrieve it from your account every time you connect.

Video Tutorial

REST API Task

JSON source

Conclusion

Following these steps, you can seamlessly connect to the Google Translate API within SSIS using the ZappySys tools, ensuring efficient data retrieval and integration. If you encounter any issues or need further assistance, our support team is available via chat on our website.

References