Move Sharepoint List Data To Sql Server

2018-03-09

Recently I was looking into the On-Premises gateway for Powerapps functionality and how we can send data to on-premise sql database. Here we will see,

  • How to setup the On-Premises gateway.
  • Create a new On-Premise gateway connection.
  • Setting up a flow to send data to the On-Premises database.

How to setup the On-Premises gateway

The detailed step to setup the On-Premise gateway can be found here.  Here I am including the screenshot / steps went through to setup mine.

  1. Download the Installer from the site https://powerapps.microsoft.com/en-us/downloads/

 

 

 

 

  2. Install the On-Premises gateway

 3. Key in the Name and the Recovery key

 

 4. Complete the setup

 Check the Status of the gateway after restarting the service either using the UI or using the command “net start / stop PBIEgwService“.

Create a new On-Premises gateway connection

  1. Once the on-premises gateway is setup with the right account, go to the powerapps application and it will display the gateway created on the previous step.

   2. Create a new connection from the Connection tab

Click “New connection”

 

Select the “SQL Server” connection

Select “On-Premises data gateway” and enter the database details.

Enter the Username and Password required to connect to the database and the Gateway.

Complete the setup and the connection is displayed as below.

Setting up a flow to send data to the On-Premises database

Create a Flow on your tenant to with a trigger on Item created and action with the SQL connector.

Setup the action as to insert a new row.

Configure to store the title of the list item created and selecting the table.

Complete the Flow setup.

Right, the gateway, connection and flow setup is completed. Now let’s create a new list item on the configured list.

Create a new list item on the color calendar list.

The data is synced to the onpremises database using the above configured flow.

 

 

(Visited 950 times, 1 visits today)

About the author 

Balamurugan Kailasam

Download 6 Free Ebooks (No email needed)

There are Beginner Ebooks on Power BI, Power Automate and Teams. 2 Microsoft Teams Governance Ebooks and a very popular Power Users Guide to SharePoint!