Call ChatGPT REST API in SSIS

Introduction

OpenAI, the creators of ChatGPT, provide both a web interface and an API for seamless integration of their tools into applications. This article provides a comprehensive guide on how to establish a connection with the OpenAI API in SSIS using ZappySys JSON Source, enabling seamless data integration and retrieval.

Prerequisites

Before we perform steps listed in this article, you will need to make sure following prerequisites are met:

1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
3. Make sure ZappySys SSIS PowerPack is installed (download it).
4. Optional (If you want to Deploy and Schedule ) – Deploy and Schedule SSIS Packages

Step-by-step guide to making a ChatGPT Rest API call

How to get an OpenAI API Key for ChatGPT

First, obtain your API key from OpenAI. This key is required for authenticating your requests.

To access the services, you will be required to set up an account with OpenAI. Click here to create your account. Once your account has been successfully created, proceed to log in to your OpenAI account.

You can find the API key by following these steps:

1. Navigate to the top right corner of the screen.
2. Click on your account name.
3. From the drop-down menu, select “API Keys.”
4. Alternatively, you can directly access the API Keys section by clicking here.

To generate a new secret key, please follow these steps:

1. Go to the API Keys page.
2. Look for the “Create new secret key” button.
3. Click the button to generate a new secret key.

Generate a new key

Upon generation, your API key will be visible on the screen. It’s crucial to copy the key and store it in a secure location immediately, as it will not be displayed again for security purposes.

The new key

Now that you have your API key, you can begin utilizing it to make calls with your components. A preliminary example involves retrieving a list of all available models in OpenAI. For detailed instructions, please refer to the API documentation available here.

ChatGPT Rest API call using JSON Source

1. Drag and Drop the SSIS Data Flow Task from the SSIS Toolbox.

Drag and drop Data flow task

2. Double-click on the DataFlow task to see the DataFlow designer surface.

3. From the SSIS toolbox drag and drop ZS JSON Source on the dataflow designer surface.

Drag and drop a JSON source

4. We will use this on the URL path:

5. In the HTTP headers grid add the following values by clicking Raw Edit button :

Authorization: Bearer YOUR_OPENAI_API_KEY
OpenAI-Organization: org-YQ9qpxxxxxxxxxxxxgoPi

6. Select your preferred Array Filter, simply click the “Select Filter” button. for this API call we need to select the $.data[*]  array filter

7. Final step: Click ‘Preview’ to initiate the ChatGTP Rest API call. You will also find a demonstration within our component, along with the resulting output from the call, below.

ChatGPT call

That’s it the configuration for the ChatGPT Rest API call has been completed successfully.

Load ChatGPT data in SQL Server using Upsert Destination (Insert or Update)

Conclusion

In this article, we explored the seamless process of establishing a connection with ChatGPT in SSIS and integrating data effortlessly, all without the need for coding. Interested in trying it out yourself? Feel free to Download ZappySys SSIS PowerPack by clicking here and experience the simplicity firsthand. Should you have any lingering queries, please don’t hesitate to ask by clicking here or initiating a conversation with our experts via the live chat icon at the bottom-right corner of this page.