SSIS Tutorial: How to get data from Spotify

Introduction

Spotify provides rich music and podcast metadata through its Web API, including artists, albums, tracks, playlists, and categories. Many data teams need this data in SQL Server for analytics, trend reporting, and catalog enrichment.

In this tutorial, you will learn how to connect Spotify API data to SQL Server Integration Services (SSIS) by using ZappySys SSIS PowerPack. This approach helps you automate extraction and loading without building a custom API client.

Steps

Create a Spotify Developer App

1. Go to the Spotify Developer Dashboard and sign in with any free or premium Spotify account.
   

   Spotify – Create app

2. Click Create app, give it a name, description, and a Redirect URI (for example http://127.0.0.1:3000).
3. Go to the app Settings page and copy your Client ID and Client Secret.
4. Store both values securely and avoid hard-coding secrets directly in package components.

Create an OAuth2 Connection in SSIS

1. Create or open your SSIS project and package.
2. In Connection Managers, right-click and choose New Connection….
3. Select ZS-OAUTH from the SSIS connectors list.
   

   Create a new SSIS OAuth API Connection Manager

4. Set the following values:
   • OAuth Provider: Custom
   • OAuth Version: OAuth2
   • OAuth Grant Type: Client Credentials Grant
   • Client ID: Your Spotify Client ID
   • Client Secret: Your Spotify Client Secret
   • Access Token URL: https://accounts.spotify.com/api/token
   • Scopes/Permissions: (leave empty)
5. Click Test Connection to confirm a token is issued successfully, then click OK.
   

   Spotify – OAuth connection

Create and Configure JSON Source

1. Add a Data Flow Task in Control Flow and open it.
   

   Drag and drop Data flow

2. In Data Flow, drag and drop ZS JSON Source.
   

   SSIS JSON Source – Drag and Drop

3. Open the JSON Source editor and enable Use credentials.
4. Select the Spotify OAuth2 connection you created.
5. Set the API URL to a Spotify endpoint. Example to search artists by genre:
   
   https://api.spotify.com/v1/search?q=genre:pop&type=artist&limit=50

   1	https://api.spotify.com/v1/search?q=genre:pop&type=artist&limit=50

6. Set HTTP Request Method to GET.
7. Add the following HTTP Header:
   • Accept: application/json
8. Set JSON Path Filter based on the endpoint. For the search artists endpoint:
   
   $.artists.items[*]

   1	$.artists.items[*]

9. Click Preview to confirm rows are returned, then click OK.
   

   Spotify – JSON source

Configure Destination and Load into SQL Server

1. Add an Upsert destination (or another destination of your choice, such as a Trash destination or JSON/CSV/XML destination).
2. Connect the output arrow from JSON Source to the destination component.
3. Configure your SQL Server connection and target table.
4. Check the options you want to use: Insert or/and Update.
5. Map the source columns to destination columns in the Mappings section.
   

   Spotify – Upsert destination

6. Click OK and execute the package.
7. Validate that Spotify rows have been loaded into your SQL Server table.
   

   Spotify – Upsert Destination example

Conclusion

You now have a practical SSIS pattern to extract Spotify Web API data and load it into SQL Server by using ZappySys SSIS PowerPack. With OAuth2 Client Credentials authentication, pagination handling, and proper column mapping, you can operationalize Spotify catalog data for BI and reporting use cases.

Explore the full capabilities of the ZappySys SSIS PowerPack to extend this pipeline with transformations, audits, error handling, and SQL Server Agent scheduling.