Righteous Beast: Conduct a Mass Mailing with SQL Server Data and Power Platform

Overview

Microsoft Word has been a humble working companion for generations, tireless despite the volume of its’ virtual labor. As Word nears a milestone birthday, we consider how to make its life easier, and found a way to increase productivity.

Some tasks, such as a mass mailing, are resource intensive, especially when using the Word desktop app. Too many letters and not enough memory spoil the party. Storing data outside the corporate network may not be possible either, limiting the options.

On-premises resources and the cloud don’t have to be mutually exclusive, though. Many firms have adopted the hybrid cloud model, a flexible approach that includes the on-premises data gateway (OPDG) in its toolkit. The OPDG extends and sustains a common db product, such as SQL Server, by providing additional opportunities for its use.

SQL Server didn’t need to go to the cloud; the cloud came to SQL Server with the on-premises data gateway, powering apps, dashboards, and productivity solutions. The possibilities are abundant.

Thanks to the gateway, data isn’t stored anywhere. It is a secure, cost-effective connection between network and provider that only uses data in its' services. Those services give us much more juice than a desktop for intensive tasks, even the occasional behemoth.

Our old friend mail merge provides a strong use-case of how the two worlds work in harmony. Power Automate, a SaaS, connects to an on-prem SQL Server instance via gateway, creates personalized letters in Word Online, then saves the files on a network drive. Hundreds, if not thousands, of files, too.

By industry, we flourish. And our old friend will get us there.

/**/

This article will demonstrate Power Automate filling a template Word document with SQL Server data via on-premises data gateway.

Caveats

Some things to know before you go.

• Data source flexibility.?This can be carried out using many data sources. Just make sure you have access to those sources. This has been tested with SQL Server, SharePoint and Dataverse. Today, we will use?SQL Server with an on-premises data gateway.
• Premium connector required.?Microsoft Word is a premium connector.
• Limits.?This method could produce a mass of documents so be sure to know your request limits.
• Data sensitivity. Before doing anything, consult with IT, data, or information security teams to ensure that this approach is above board. Be careful with sensitive information, personal and proprietary. DLP policies help reduce risk and protect assets.

Not so bad.

Prerequisites

System requirements.

• Basic knowledge/familiarity.?Power Automate, SQL Server and Microsoft Word are the core applications used in this exercise.
• Permissions.?Read/Write access to a network location for creating and storing documents is also required.
• Connectors.?SQL Server, Microsoft Word Online and the File System are our connectors.
• On-premises data gateway. To create the bridge between the network and cloud, a gateway must be installed on a network. A virtual machine will do.
• SQL Server connection. In Power Automate, create a connection to SQL Server to the OPDG. Note that the OPDG must be installed prior to this step.

Cooking with gas if you have these in order.

Steps to Automate a Mail Merge in Power Platform via SQL Server

General Process

This is the general flow of the process.

Starting in?Word, we will check if the Developer tab is visible in the ribbon. A template letter will be created and saved to?a network drive. If so, great. If not, a few steps will handle that.

Address data and policy details for insureds are required. We will connect to SQL Server tables containing sample data for insureds and policies.

Lastly, we will create a flow in?Power Automate?that generates a Word document for each recipient (row) using their address and contact info from SQL Server.

Word

The same process applies from the previous guide for Microsoft Word. The template file can be stored in SharePoint but today we will use OneDrive for Business.

SQL Server

Create Tables in SQL Server

SQL Server holds your policyholder contact info and policy data in two tables. We need to set up the two tables and import data into them.

The?InsuredAccount?table contains a unique list of insureds and their addresses.

Go ahead and create this first table in your SQL Server environment.

The next table,?PolicyDetail, contains policy information for our insureds – type of coverage, dates, etc. The InsuredID relates this table to the?InsuredAccount?table.

Create the tables and add the necessary fields via the?Columns?item located in the table detail area.

USE Developments

CREATE TABLE InsuredAccount
(
??????InsuredAccountID int IDENTITY(1,1) NOT NULL,
??????InsuredID int NOT NULL,
??????InsuredFirstName nvarchar(150) NOT NULL,
??????InsuredLastName nvarchar(150) NOT NULL,
??????InsuredAddress1 nvarchar(75) NOT NULL,
??????InsuredAddress2 nvarchar(25) NOT NULL,
??????InsuredCity nvarchar(25) NOT NULL,
??????InsuredState nvarchar(25) NOT NULL,
??????InsuredPostal nvarchar(25) NOT NULL
);

CREATE TABLE PolicyDetail
(
??????PolicyDetailID int IDENTITY(1,1) NOT NULL,
??????InsuredID int NOT NULL,
??????PolicyNumber nvarchar(25) NOT NULL,
??????InsuranceProduct nvarchar(50) NOT NULL,
??????EffDate datetime NOT NULL,
??????ExpDate datetime NOT NULL,
??????PolicyStatus nvarchar(25) NOT NULL
);        

Import Test Data

Assuming that we are using the same table structure, at least for testing, these are the scripts to insert data into the InsuredAccount and PolicyDetail tables.

InsuredAccount:

INSERT INTO InsuredAccount (InsuredID, InsuredName, InsuredFirstName, InsuredLastName, InsuredAddress1, InsuredAddress2, InsuredCity, InsuredState, InsuredPostal) VALUES (1,'John Davidson','Johnny','Davidson','123 Main Street','Apt 2','New York','New York','10001'
INSERT INTO InsuredAccount (InsuredID, InsuredName, InsuredFirstName, InsuredLastName, InsuredAddress1, InsuredAddress2, InsuredCity, InsuredState, InsuredPostal) VALUES (2,'Databricks','Eric','Singer','Spear Street 160','15th Floor','San Francisco','CA','94105')
INSERT INTO InsuredAccount (InsuredID, InsuredName, InsuredFirstName, InsuredLastName, InsuredAddress1, InsuredAddress2, InsuredCity, InsuredState, InsuredPostal) VALUES (3,'Toyota North America','Pamela','Walsh','6565 Headquarters Dr',NULL,'Plano','TX','75024')
INSERT INTO InsuredAccount (InsuredID, InsuredName, InsuredFirstName, InsuredLastName, InsuredAddress1, InsuredAddress2, InsuredCity, InsuredState, InsuredPostal) VALUES (4,'Zenith Shipping','Kelly','Cooper','P.O. Box 969',NULL,'Conover','NC','28613')
INSERT INTO InsuredAccount (InsuredID, InsuredName, InsuredFirstName, InsuredLastName, InsuredAddress1, InsuredAddress2, InsuredCity, InsuredState, InsuredPostal) VALUES (5,'JTO Advisors','John','O''Brien','One State Street Plaza','10th Floor','New York','New York','10002'))        

PolicyDetail:

INSERT INTO PolicyDetail (InsuredID, PolicyNumber, InsuranceProduct, EffDate, ExpDate, PolicyStatus, DateOfMailing) VALUES (1,'12345','Homeowners','01/01/23','01/01/24','Active','08/01/23'
INSERT INTO PolicyDetail (InsuredID, PolicyNumber, InsuranceProduct, EffDate, ExpDate, PolicyStatus, DateOfMailing) VALUES (1,'123456','Car Insurance','01/01/23','01/01/24','Active','08/01/23')
INSERT INTO PolicyDetail (InsuredID, PolicyNumber, InsuranceProduct, EffDate, ExpDate, PolicyStatus, DateOfMailing) VALUES (1,'1234567','General Liability','01/01/23','01/01/24','Active','08/01/23')
INSERT INTO PolicyDetail (InsuredID, PolicyNumber, InsuranceProduct, EffDate, ExpDate, PolicyStatus, DateOfMailing) VALUES (1,'12345678','Flood','01/01/23','01/01/24','Active','08/01/23')
INSERT INTO PolicyDetail (InsuredID, PolicyNumber, InsuranceProduct, EffDate, ExpDate, PolicyStatus, DateOfMailing) VALUES (2,'12956789','Cyber','01/01/23','01/01/24','Active','08/01/23')
INSERT INTO PolicyDetail (InsuredID, PolicyNumber, InsuranceProduct, EffDate, ExpDate, PolicyStatus, DateOfMailing) VALUES (2,'16767900','D&O','02/01/23','02/01/24','Active','08/01/23')
INSERT INTO PolicyDetail (InsuredID, PolicyNumber, InsuranceProduct, EffDate, ExpDate, PolicyStatus, DateOfMailing) VALUES (3,'20579011','Product Recall','02/01/23','02/01/24','Active','08/01/23')
INSERT INTO PolicyDetail (InsuredID, PolicyNumber, InsuranceProduct, EffDate, ExpDate, PolicyStatus, DateOfMailing) VALUES (4,'24390122','Cargo','02/01/23','02/01/24','Active','08/01/23')
INSERT INTO PolicyDetail (InsuredID, PolicyNumber, InsuranceProduct, EffDate, ExpDate, PolicyStatus, DateOfMailing) VALUES (4,'28201233','Aviation','03/01/23','03/01/24','Active','08/01/23')
INSERT INTO PolicyDetail (InsuredID, PolicyNumber, InsuranceProduct, EffDate, ExpDate, PolicyStatus, DateOfMailing) VALUES (4,'32012344','General Liability','03/01/23','03/01/24','Active','08/01/23')
INSERT INTO PolicyDetail (InsuredID, PolicyNumber, InsuranceProduct, EffDate, ExpDate, PolicyStatus, DateOfMailing) VALUES (5,'35823455','Professional Liability','04/01/23','04/01/24','Active','08/01/23'))        

After these tables are created and populated with data, we can connect the letter and the data via Power Automate.

Power Automate

Create Flow for Mass Generating Docs

Let’s get down to it.

Create an instant flow. Choose a button to trigger the flow.

Add a SQL Server Get Rows (V2) action.

This is where the on-premises data gateway comes in. Using the connection set up previously, click the … and select the SQL Server connection.

Select the?InsuredAccount?table in the?Table name?field. The connector returns views as well. A new connection can also be added via the + Add new connection option.

Click?Show advanced options.?In the?Filter rows?input area, add the following statement.

InsuredAddress1 ne null        

This is an?OData filter.?All returned rows will only have an address.

Next, add a?Select data?action. This action lets you cherry-pick the fields to be displayed from the SQL Server table rows – similar to a Select statement in SQL.

In?From, add?value?from the?List Rows – Get Insured Accounts?via dynamic content menu.

In?Map,?each row represents a field that holds data from the List Rows action. The fields on the left side act as an alias to the actual data fields in the returned rows.

The SQL equivalent:

SELECT [Field1], [Field2], [Field3
FROM
(
? SELECT [Field1],[Field2],[Field3]
? FROM
? InsuredAccount
)
AS BOOP        

Add an?Initialize variable?action. Name the variable?varInsuredAccounts?and select?Array?as the?Type.

In the?Value,?place?Output?from the prior?Select?action from the dynamic content menu.

This is where it gets fun: joining this array to the related policies table.

Add a second SQL Server Get Rows?(V2) action. Select?PolicyDetail?in the?Table name?dropdown.

In the Dataverse guide, we utilize the?Fetch Xml Query?feature to join entities, which is similar to joining tables in SQL. That is not necessary here.

Add another?Select?operation and select the fields for that will be displayed on the second page from the dynamic content menu.

Note the?EffDate and ExpDate fields.?They use an expression that displays only the date and leaves out the time.

/*Effective date*/
formatDateTime(item()?['EffDate'],'MM/dd/yyyy')

/*Expiration date*/
formatDateTime(item()?['ExpDate'],'MM/dd/yyyy')
?        

** Loops count towards your daily request limit.?A Select operation is a cost-efficient action rather than say, a loop action with a Compose operation. You do not need a loop with a Select action.

We want to save our request limit for a rainy day so we will use the loop on creating our batch of documents.

Add an?Apply to each?control.

Then, add?varInsuredAccounts?to the?Select an output from previous steps?area.

Next, add a?Filter array?action.

Add?varPolicyDetails?to the?From?area from the dynamic content menu.

Place this expression in the left side input area. Set the filter condition to?is equal to.

items('Apply_to_each')?['InsuredID']        

Place this expression one the?right side?input area.

item()?['InsuredID']        

Here we are.

This filter will produce a list of policies from the PolicyDetail table based on the matching?InsuredID?field.

Nearly there. Add a Word Online?Populate a Microsoft Word template?action.

Once you select the Word document that we saved in the network drive, the bookmarks in the document will appear after a moment.

In each field, here is an expression that will pull the field from the?InsuredAccount?array.

items('Apply_to_each')?['InsuredState']        

Add an expression to each field in the template. Leave RepeatingRows blank for just a second.

In the?RepeatingRows?input area, add?Body?from the?Filter array?section in the dynamic content menu.

Adding the?Body?output to?RepeatingRows?will generate a list of policies?related only to that insured.

Lastly, we?create a file for each document?in our mailing.

Add a File System connector and select the Create a file action.

Specify the?Folder Path and File Name. If we want to add an element to identify each document, use this expression as an example:

Note:?Like a file system, a unique file name is required.

Finally, add?Body?from the?Populate a Microsoft Word template?action from the dynamic content menu.

Your own mighty Wurlitzer is set up to deliver. Hit the?Test?button and let’s see our flow churn out some sweet docs.

Here is the final flow:

Inside the loop:

Testing

Naturally, we need to test this out to ensure the letters came out as expected. A few notes while testing.

• Avoid testing with a lot of documents.?Test this out with two or three documents at a time. Short bursts at allow you to get this right so you can tweak from there. You do not want to clean up 1,000 documents if something goes wrong, do you?
• Lo, the daily request limit.?Be aware of the?request limits?based on your Power Automate license.
• Folder governance.?This method depends on a single Word document template. The template must be located in the directory in which it is referenced within Power Automate (ie, move/delete the file, the flow will fail) and all fields should be empty.

Once we run the flow, letters for all insureds will be created in the network drive.

Address block prints out as well as the first name of the policyholder:

And a table of policies for the further benefit of the consumer.

Tested this by looking up the policies associated with this InsuredID in SSMS.

InsuredAccount:

PolicyDetail:

These docs look ready for official review. What do we think?

/**/

Resources

• About on-premises gateways - Power Platform | Microsoft Learn?|
• On-premises data gateway in-depth - Power BI | Microsoft Learn |
• Nuclear Mail Merge | Dan Romano
• Power Automate: Create Word Document with Repeating Section?| Matthew Devaney
• Word Online Throttling Limits?| Microsoft Learn
• Compliance and data privacy - Power Platform?| Microsoft Learn
• Data storage and governance in Power Platform - Power Platform?| Microsoft Learn
• On-premises data gateway in-depth - Power BI?| Microsoft Learn
• Data loss prevention policies - Power Platform?| Microsoft Learn
• Additional links provided inline.

?

?