Home » SharePoint Online » Send Data to On-Premises SQL Database From SharePoint List

Send Data to On-Premises SQL Database From SharePoint List

office365-address-book

Anurag | Published: March 12, 2018 | SharePoint Online, Updates | 2 Minutes Reading

Recently we were looking into the On-Premises gateway for Power Apps of Office 365 account functionality. To send data to on-premise SQL database is quite a bit problem when it comes to move data from Sharepoint Apps like Flow, Powerapps, data gateways and connect to SQL Server database. But don’t worry today we would go beyond all problems

We will see here all these issues:

  • Setup Process of On-Premises gateway.
  • Creation of New On-Premise Gateway Connection.
  • Setting up a Flow to Send Data to the On-Premises Database.

Setup the On-Premises Gateway

We will not include the detailed step to setup the On-Premise gateway. Here we would be making up with simple steps to setup the Gateway

  1. Download the PowerApps Installer from the site – https://powerapps.microsoft.com/en-us/downloads/
    Screenshot_2
  2. Install the On-Premises gateway
  3.  Key in the Name and the Recovery key
    Screenshot_2
  4. Complete the setup
  5. Check the Status of the gateway after restarting the service either using the UI or using the command
    “net start / stop PBIEgwService“.
    Screenshot_2

The detailed process you can get here in this segment.

Create New On-Premises Gateway Connection

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.

  1. Create a new connection from the Connection tab
  2. Click “New connection”
    Screenshot_2
  3. Select the “SQL Server” connection Screenshot_2
  4. Select “On-Premises data gateway” and enter the database details.
    Screenshot_2
  5. Enter the Username and Password required to connect to the database and the Gateway.
  6. Complete the setup and the connection is displayed as below.

Setting Up Flow to Send Data to On-Premises Database

  1. Create a Flow on your tenant to with a trigger on Item created and action with the SQL connector.
    Screenshot_2
  2. Setup the action as to insert a new row.
    Screenshot_2
  3. Configure to store the title of the list item created and selecting the table.
    Screenshot_2
  4. Right, the gateway, connection and flow setup is completed. Now let’s create a new list item on the configured list.

Result

  1. Create a new list item on the color calendar list.
    Screenshot_2
  2. The data is synced to the onpremises database using the above configured flow.
    Screenshot_2