SQL Server REST API Integration: 3 Easy Methods

on Data Integration, ETL, Tutorials • January 19th, 2021 • Write for Hevo

Do you want to transfer your data from REST API to Microsoft SQL Server? Are you finding it challenging to set up the SQL Server REST API integration? If yes, then you’ve landed at the right place! This article will answer all your queries & relieve you of the stress of finding a truly efficient solution. Follow our easy step-by-step guide to help you master the skill of efficiently transferring your data to Microsoft SQL Server from REST APIs.

It will help you take charge in a hassle-free way without compromising efficiency. This article aims at making the data export process as smooth as possible.

Upon a complete walkthrough of the content, you will be able to successfully set up a connection between REST API servers & Microsoft SQL Server and seamlessly transfer your data for a fruitful analysis. It will further help you build a customised ETL pipeline for your organisation. Through this article, you will get a deep understanding of the tools & techniques, and thus, it will help you hone your skills further.

Table of Contents

Introduction to REST API

REST API Logo.

REST, also known as Representational State Transfer, is one of the most sought-after architectural styles for developing a web services-based application. It allows users to leverage stateless operations to read or write resources. Each API (Application Program Interface) that acts as an interface between a diverse set of collaborating applications; constitute the implementation of these operations. REST APIs are further known for their simplicity and ease of use.

Introduction to Microsoft SQL Server

SQL Server Logo.

Microsoft SQL Server is a robust relational database management system that allows users to store, access, analyse and manage large volumes of complex data with ease. It houses integration support for numerous other application and Microsoft products, making it one of the most popular and in use RDBMS. It also allows users to leverage their Microsoft SQL Server instance as a data warehousing solution by letting them store/backup their critical business data seamlessly. This data can be used later for analytics, reporting and strategic planning.

For further information on Microsoft SQL Server, you can check the official website here.

Ways to Load Data from REST API to Microsoft SQL Server

Method 1: Using Microsoft SQL Server Integration Services (SSIS) to Load Data into Microsoft SQL Server

Using Microsoft SQL Server Integration Services (SSIS) to load data from REST APIs to Microsoft SQL Server is one way. This method requires you to create a data flow task by setting up a REST-based connection. You will then need to use the “DataModel” property to map your incoming data and load your data to your Microsoft SQL Server database correctly.

Method 2: Using Custom Code Snippets to Set Up SQL Server REST API Integration

Using custom code snippets to set up the SQL Server REST API integration is one such way. This method requires you to install the JDBC driver for Microsoft SQL Server and then use your connection URL to establish a connection between them. You can then leverage a statement object to carryout SQL-based operations and insert your data from REST API to Microsoft SQL Server databases.

Method 3: Using Hevo Data, a No-code Data Pipeline

A fully managed, No-code Data Pipeline platform like Hevo Data, helps you load data from REST APIs (among 100+ Sources) to Microsoft SQL Server in real-time, in an effortless manner. Hevo, with its minimal learning curve, can be set up in a matter of minutes making the users ready to load data without compromising performance. Its strong integration with various sources such as databases, files, analytics engine, etc. gives users the flexibility to bring in data of all different kinds in a way that’s as smooth as possible, without having to write a single line of code.

Get started with Hevo today! Sign up here for a 14-day free trial! 

Prerequisites

  • Working knowledge of Microsoft SQL Server.
  • Working knowledge of REST APIs.
  • A Microsoft SQL Server account.
  • A Microsoft SQL Server database.

Methods to Set Up SQL Server REST API Integration

There are multiple ways in which you can set up the SQL Server REST API integration:

Method 1: Using Microsoft SQL Server Integration Services (SSIS) to Load Data into Microsoft SQL Server

The Microsoft SQL Server Integration Services(SSIS) is a robust component and functionality of the Microsoft SQL Server database that allows users to carry out numerous complex data migration tasks with ease. It houses support for and provides various tools such as data warehousing tools that help automate ETL to some extent, workflow tools to automate data migration process and a diverse set of data integration tools that allow users to unify data. It also supports connecting with REST API based sources and lets users ingest data into their Microsoft SQL Server databases.

You can set up the SQL Server REST API integration by leveraging the connection manager using the following steps:

Step 1: Defining a Data Flow Task

Defining a Data Flow Task: SQL Server REST API Integration.

To start setting up the SQL Server REST API integration, you first need to define a new data flow based task. Here, you will also need to specify the Microsoft SQL Server ADO.Net destination and new REST source. To do this, go to the connection manager, add a new connection and choose the connection type as REST, by selecting the REST option from the dropdown list.

This is how you can define a data flow task.

Step 2: Configuring Connection Properties

With your task now added, you need to configure the properties for the connection. To do this, you will have to specify the connection properties such as the REST URI, your credentials and the authentication format, choosing between OAuth, HTTP, NTLM, and Digest, along with information about the incoming file format like JSON, XML, etc.

Configuring Data Mapping and Destination Properties.

Once you’ve provided the necessary information, you now need to specify the “DataModel” property, that helps map incoming data into database tables. You can choose between the following options for the DataModel property:

  • Relational: This helps ensure that mapping for incoming data takes place in a way that maintains referential integrity.
  • Document: This helps map data into nested or hierarchical data documents.
  • Flattened Documents: This helps aggregate data from nested documents and their parent documents into a single table.

This is how you can configure the connection properties.

Step 3: Configuring Extraction Properties of REST Data Source

Configuring the Extraction Properties for REST Sources.

With your connection properties now configured, you now need to configure the REST source extraction properties. To do this, you will need to have the “Create Global Objects” permission and provide the query that will help extract data from your data source. Once you’ve done that close the REST source and connect it to the ADO.NET based destination.

Step 4: Configuring the Microsoft SQL Server Database

With all the necessary configurations now done, you now need to configure your Microsoft SQL Server destination database. You can do this by providing the database name, tables and information about the desired data access mode, choosing between view/table.

Now, map the incoming data with the destination column and then click on the advanced option and specify the following properties:

  • BulkInsertFireTriggers: Should bulk inserts fire triggers on destination tables.
  • BulkInsertOrder: Sort columns specified in ascending/descending order.
  • MaxInsertCommitSize: Maximum number of rows to insert in a single batch) 
  • DefaultCodePage: Code page to use if the source does not provide one.

You can now execute this workflow to start loading data from REST API to Microsoft SQL Server databases.

This is how you can set up the SQL Server REST API integration using the Microsoft SQL Server Integration Service (SSIS).

Limitations of Using Microsoft SQL Server Integration Services (SSIS)

  • The Microsoft SQL Server Integration Service (SSIS) provides support only for the Microsoft SQL Server database. Hence, if your business data needs require using a diverse set of databases, then using SSIS will not work.
  • If your systems require you to delegate data storage or processing to a particular tool based on the type of data such as MongoDB or Neo4J for graph databases; then SSIS can limit your options.

Method 2: Using Custom Code Snippets to Set Up SQL Server REST API Integration

To start loading data from REST APIs, you will first have to leverage the JDBC driver’sDriverManager” class to obtain and establish a connection with your Microsoft SQL Server instance. You can use the following syntax for creating your connection URL:

protocol//[hosts][/database][?properties] 

Once you’ve set up the necessary configurations and created your connection URL, you now need to create a statement object that will carry out SQL operations such as insert, delete, update, etc., and fetch results.

With your statement object ready, you will have to execute the insert command in a repetitive loop based on conditional logic. Once you’ve executed the insert statement or any other operation, you will need to close the statement and connection object.

For example, if you want to insert, set and update values in your Microsoft SQL Server database using APIs, you can do so using the following lines of code:

While (someCondition) {
// Specify the connection URL as per SQL server connection string.
        String connectionUrl = "jdbc:sqlserver://<YourServer>:<port>;databaseName=HevoMSSqlTest;user=<user>;password=<password>";

        try {
//LOAD the API driver 	
	Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
	
//Get a new Connection to SQL Server 
	Connection conn = DriverManager.getConnection(connectionUrl); 

// Create a statement object, this is the vehicle for carrying your SQL inserts				PreparedStatement stmt = conn.prepareStatement("INSERT into my_records" +" VALUES(?,?,?,?,?)");

// SET the values in your table columns 							
	stmt.setInt(1, 0);			
	stmt.setString(2, myString);					
	stmt.setString(3,myAddress);					
	stmt.setString(4, myName);					
	stmt.setString(5, myRole);

//EXECUTE the update 					
	stmt.executeUpdate();
	}// End of TRY block 
	
        catch (Exception e) {
        System.err.println("Error: ");
        e.printStackTrace(System.err); 
        }

	finally {
// CLOSE the Statement and Connection objects 
	stmt.close(); 
	conn.close();    	
	}
} // END of While loop 

This is how you can develop custom code snippets that leverage the JDBC driver for Microsoft SQL Server to set up the SQL Server REST API integration.

Limitations of Using Custom Code Snippets

  • Using drivers requires to keep track of updates and manually update them when new releases are available or when your technology stack(Java, Node.js, C++, Python) undergoes any updations. Similarly, with new versions, existing API calls and methods may depreciate and require upgrades.
  • You will have to add/remove/change the new properties associated with your connections when any change occurs, to ensure smooth data transfers and high efficiencies.
  • Working with APIs to load data requires you to have strong technical knowledge to handle operations such as connection pooling, query optimisation, compression and validation, etc.

Method 3: Using Hevo Data, a No-code Data Pipeline

Hevo Data Logo.

Hevo Data, a No-code Data Pipeline, helps you transfer data from REST APIs (among 100+ sources) to Microsoft SQL Server & lets you visualise it in a BI tool. Hevo is fully-managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on key business needs and perform insightful analysis using various BI tools such as Power BI, Tableau, etc. 

Steps to use Hevo Data

Hevo Data focuses on two simple steps to get you started:

  • Configure Source: Connect Hevo Data with your REST API source by providing a unique name for your pipeline along with information about the method you want to use, choosing between GET and POST. You will also need to provide the URL for your API endpoint, data root for your API and your credentials such as username and password to allow Hevo to access your data, along with information about your query params and API headers.
  • Integrate Data: Load data from REST APIs to Microsoft SQL Server by providing a unique name for your destination along with your database credentials such as username and password. To help Hevo connect with your Microsoft SQL Server database, you will also have to provide information such as the host IP, port number and the name & schema of your database.

Check out what makes Hevo amazing

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a safe & consistent manner with zero data loss.
  • Minimal Learning: Hevo, with its interactive UI, is simple for new customers to work on and perform operations.
  • Live Monitoring: Hevo allows you to monitor the data flow, so you can check where your data is at a particular point in time.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to export. 
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
  • Completely Managed Platform: Hevo is fully-managed. You need not invest time and effort to maintain or monitor the infrastructure involved in executing codes.

Conclusion

This article teaches you how to load data from REST API to Microsoft SQL Server by setting up the SQL Server REST API integration with ease. It provides in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently. These methods, however, can be challenging especially for a beginner & this is where Hevo saves the day. Hevo Data, a No-code Data Pipeline, helps you transfer data from a source of your choice in a fully-automated and secure manner without having to write the code repeatedly. Hevo, with its strong integration with 100+ sources & BI tools, allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiff.

Want to take Hevo for a spin? Sign up here for the 14-day free trial and experience the feature-rich Hevo suite first hand. You can have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

Tell us about your experience of setting up the SQL Server REST API integration! Share your thoughts in the comments section below!

No-code Data Pipeline For SQL Server