Skip to main content

Power Automate - On-premises data gateway

On-premises data sources

In this blog post we will have a look, how to integrate Power Automate with an on-premise SQL server.

For this purpose I locally installed Microsoft SQL Server and installed the Adventure Works sample database.

Additionally I added a new SQL server login, with read/write permissions for the Adventure Works database. (In integration scenarios, you most probably restrict this service user to some views or stagging tables, required for your integration).

Azure on-premises data gateway

To be able to integrate with your on-premise database, you need to install the azure on-premises data gateway on your SQL server. When you start the setup, you can choose between two types of setup. We will choose the type of setup that supports Power BI, Power Apps and Power Automate:


Then you follow the installation procedure and at a certain point you will be asked for the e-mail address of the user that shall be used for the integration. Here you should use the user, you will use to create your flows:
You will be asked to authenticate your account:
Finally you provide a name and a recovery phrase for your gateway:
And then your gateway is ready to use.

Retrieve data

To retrieve data from your on-premise SQL server, you have to add a SQL server action to your flow. In this case the "get rows" action:

Once you select the action, you can configure the access to your SQL server, by choosing an authentication method. For the on-premises data gateway you can choose between windows and SQL server authentication. You then provide sql server address, database, username and password to establish the connection:
Once you established the connection you have to choose once again the sql server and database and then you can choose in the field "table name" from which table or view you want to retrieve data. You can additionally restrict the retrieved data by adding top, select a.s.o. statements:

You can then e.g. create new contacts in CRM, or whatever you aim to do with that data ;)

Insert Row

The other way round, you might want to create a new record in your database once a new contact, invoice, ... is created in CRM. This can be achieved by the insert row / insert rows action. 
For that purpose I set up a stagging table called "stagging_contact", where we will insert a row, whenever a new contact is created in CRM:

After creating a new contact in CRM:


the flow triggers:


and creates a new record in the database:

Storing documents

According to the documentation of the SQL connector the datatype varbinary is not supported, but this information seems to be outdated. Like in my previous post about uploading documents to sharepoint, I tried to achieve the same with my on prem database and it worked.

Basically I retrieve the attachments from CRM via a web api request, parse the content, convert the document bode from base64tobinary and upload it into the database via the insert row action:


To demonstrate the document is correctly stored, I added another action to get the row again from the database and send it via e-mail to me:


The flow ran successful:


And I received a nice e-mail with my attachment ;)




Labels:

Comments

Post a Comment

Popular posts from this blog

Power Automate Get absolute Sharepoint URL from document location

Document Locations If you enable Sharepoint document integration in your model driven apps, you can define for which entities the possibility to upload documents to sharepoint shall be offered and you can define a folder structure. Possible folder structures can either be based on account or contact, which means if you create child items (e.g. opportunities, activities, ...) sub folders are created below. If an entity is enabled for document management a "Files" tab is added to the form of your model driven app and as soon as you click on that tab a folder is automatically generated on Sharepoint. Once the folder has been created you have options to create new or upload existing file or to open the document location (folder) on Sharepoint: What happens behind the scenes is, as soon as you click on the files tab your model driven app checks, weither a "document location" for this item already exists, or not. The document location is an own entity in C...
1 comment
Read more

Send e-mails with a link to the record via Power Automate

Today we gonna have a look at how to send an e-mail with a link to a CDS record via Power Automate. In the good old classic workflows, you could directly reference the "record URL" from the dynamic data. But since UCI has been launched and you have the possiblity to target different apps, you already had to do a few more steps in the classic workflows as well. The URL to a CDS record consists of multiple things: CRM URL appid entity name record id So we will work this out one after the other. First we add a "List record" action to lookup the appid of the app, we target. We look for "model driven apps" and filter by the name of the app: Then we initialize a variable with the resulting app id: Then we initialize another variable and extract the CRM_URL: Then we fetch our records, where we want to send out a notification with a link, in this case we look for workorders fullfilling certain criteria: Now we compose the record URL: As a next step we create a new...
2 comments
Read more

Working with Optionset lables in Power Automate

Connector Types Power Automate currently offers 3 different connectors, to connect Power Automate with your Dynamics 365 instance: Dynamics 365 Common Data Service Common Data Service (current environment) Today we will look on ways how to retrieve option set labels via the different connectors, for our sample accounts: Dynamics 365 This connector should not be used any more , since in April 2019 it has been announced as being deprecated. https://flow.microsoft.com/en-us/blog/nine-new-connectors-in-april-and-improved-visibility-into-trigger-failures/ With the Dynamics 365 connector it was quite easy to work with optionsets, since it offered both the optionset value (int) and the optionset label (name) in the dynamic content: And here we go, these are our results: Common Data Service Since the Dynamics 365 connector has been deprecated, this is the connector you should use. If you perform a "Get record" or "List records...
13 comments
Read more