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.
Before we perform steps listed in this article, you will need to make sure following prerequisites are met:
- SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it).
- 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:
- Navigate to the top right corner of the screen.
- Click on your account name.
- From the drop-down menu, select “API Keys.”
- Alternatively, you can directly access the API Keys section by clicking here.
To generate a new secret key, please follow these steps:
- Go to the API Keys page.
- Look for the “Create new secret key” button.
- Click the button to generate a new secret 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.
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.
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.
4. We will use this on the URL path:
5. In the HTTP headers add the following values:
-H "Authorization: Bearer $OPENAI_API_KEY" \
-H "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.
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)
- Inside Data Flow, Drag and drop Upsert Destination Component from SSIS Toolbox
- Connect our Source component to Upsert Destination
- Double click Upsert Destination to configure it
- Select Target Connection or click NEW to create new connection Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS
- Select Target Table or click NEW to create new table based on source columns
- Click on Mappings Tab to Auto map columns by name. You can change mappings as you need SSIS Upsert Destination - Columns Mappings
- Click OK to Save Upsert Destination Settings
- That's it, You are now ready to run data flow. NOTE: If you wish to debug data flow and see records when you run, add data viewer by right click on blue arrow > Click Enable Data Viewer
- To execute data flow, Right click anywhere inside Data Flow Surface and click Execute Task
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.