In this article, we will learn how to query SQL database using UiPath. Structured Query Language or SQL is a standard Database language which is used to create, maintain and retrieve the relational database. Many enterprises have processes which involve retrieving data from SQL databases through queries. To automate such processes using UiPath, you need to understand how to connect and query the database.
We are using AdventureWorks Database in this tutorial. This is Microsoft product sample for an online transaction processing (OLTP) database. The AdventureWorks Database supports a fictitious, multinational manufacturing company called Adventure Works Cycles. You can download this database from here
Prerequisites: You will need to have database activities package installed in UiPath studio. You can install these activities using package manager.
Let’s get started!
First activity we need is to create connection. “Connect” activity sets up the connection between UiPath studio and Database. Drag and drop Connect activity on the page.
Click ‘Configure Connection’ button.
On Connection settings dialog, click “Connection Wizard” button to select data source. Select ‘Microsoft SQL Server’ from list. Now select data provider for SQL server from Data provider drop down list. Click OK.
‘Connection Properties’ dialog will be opened. Select your database server name from drop down list. Provide authentication details as required. Select the database you want to connect to.
Click ‘Test Connection’ button to verify that UiPath is able to connect to the database.
Click OK. Click Ok again on ‘Edit Connection Settings’ dialog box. You will be able to see connection string with all the details. Select ‘SQL Client’ from dropdown to specify Provider name.
These steps create a connection between database and UiPath, which you will need to store in a variable of type ‘DatabaseConnection’. Create a variable assign it to output property of ‘Connect’ activity. We have ‘connAdventureWorks’ variable for this.
Once we have a connection established, now we can execute queries. Drag and drop ‘Execute Query’ activity from the list.
Click on ‘Configure Connection’ button to open settings. Mention connection variable name. Click OK and get back to Execute Query activity.
Click ‘Edit Query’ button and mention the SQL query you want to run.
This query when runs successfully, returns a table. So we need a data table variable to hold these values.
Then to see how many rows we’ve got in data table, we are writing it to excel file using ‘Write Range’ activity.
Below is the list of products from SQL database.
Using database activities, we can query SQL database and get results in the data table using ‘Execute Query’ activity. If it’s required to execute INSERT, UPDATE, DELETE queries, we have to use ‘Execute Non Query’ activity in similar way. This activity returns number of rows affected.