Power Automate Flow for Data Warehouse

Hi,

Sharing a Power Automate (PA) flow here for those that would like to explore using MS Power Automate to load RE data (via SKY API) and FE data into a data warehouse.

Prereq:

• Azure SQL Server instance that is already setup with tables that will get loaded with data
  • this is what I used, but I'm sure the flow can be modified for any other DBMS that can connect via PA
• Blackbaud Developer account already setup with application registered to do SKY API calls via HTTP
• SharePoint List setup that has the Subscription Key and Authentication Token ready for use at any moment
  • I do things a little differently with the “headless” authentication in order to issue HTTP calls to the SKY API, you can read from here on what I did: https://community.blackbaud.com/forums/viewtopic/486/59706#p234008

Short explanation:

• This flow is a slightly more complex situation where I am actually dumping into 3 tables: gift table, gift soft credits table, and gift split table. (I separate them out as they are 1-many relationship with a gift)
• Flow runs on schedule depending on your need to:
  • Truncate the tables (quick way to delete all rows from the table)
  • Calls SKY API for gift listing via HTTP action, 5000 gifts at a time (max limit) until no more next_link
    • Parse the JSON returned to get next_link and the gift json array
    • Execute an INSERT INTO gift table through selecting data from the json
    • Execute another INSERT INTO gift soft credit and gift split table

What to expect:

• full dump our constituent table of 400K+ records in ~23min
• full dump consittuent code table of 400K+ records in ~5min
• full dump gift table (and soft credit and gift split) of 1.5M+ records in 1hr 50min

Blackbaud-RENXT-LoadSQLGift_20220728045914.zip