Make your employee photos flow through your systems with Power Automate.

Some clients have many on-prem and cloud systems, and not all of them are connected.

As a part of the Onboarding process of new employees, it’s good to have the data flow from where it is entered the first time, so you don’t get your systems having out-of-date data.

This post is an example of a custom onboarding process I applied to the Onboarding flow using Parent and Childs flows where the Master onboarding flow can call the other flows and use it again rather than creating large flows that can break.

In this example, the customer had an Internal custom website (Umbraco cms) for the company, and all of the data is stored in an OnPrem MSSQL server. The employees can upload pictures to this internal website portal.

This is where the Power Automate SQL connector comes in using the “When an item is modified (V2)” trigger on the database holding the data.

The first problem was that there was no direct link from the photo user table to the employee, so the system’s service provider provided us with an API to ask for the picture, so we just needed to make an HTTP request to that API.

Another problem was that the modified SQL connector prerequisite was to have the “ROWVERSION column,” so we had to empty the table and rebuild it with the new column in place. After that, it worked very well, and now each time an employee updates their photo, the photo flows through every system.

First, we get the URL of the photo from the CMS, and then we upload the thumb to the thumbnailphoto attribute in the On-Prem Active Directory. We upload the original picture use the set-userphoto” exchange PowerShell command. When that is done, we update the employee master database with the photo URL. Hence, we have one database with all the info up-to-date.

Now, this process looks something like this.

User uploads picture -> cms stores data in SQL -> modified Sql trigger Parent flow ->

On-Prem Powershell script child flow-> query cms for photoid -> upload thumb to ad -> upload photo to Office365.

Here is the powershell script I used for the onpremis part

   param (#parameters from flow [Parameter(Mandatory=$false)] [string] $oldphotourl , [Parameter(Mandatory=$true)] [string] $email , [Parameter(Mandatory=$false)] [string] $photoid ) $samaccount = $email.Split("@") $SamAccountName = $samaccount[0] import-Module ExchangeOnlineManagement $UserCredential = Get-AutomationPSCredential -Name ‘company_Flow_Automation’ Connect-ExchangeOnline -Credential $UserCredential #get photopath from umbraco $Url = “http://innri.company.internal/umbraco/api/ProfileAccess/MediaUrl?Mediaid=$photoid” #photo API token $header = @{ ProfileAccessToken = “1234567890qwertyuiopasdfghjkl” } $image = Invoke-RestMethod -Method ‘get’ -Uri $url -Headers $header # get image fro API $photourl = ‘http://innri.company.internal’+$image If ($photourl -ne $oldphotourl ) #check if the picture has updated { #flip brackets from path $photo =”\\company-WEB-01\media$”+$image.Replace(‘/’,’\’) #find the thumb $dirphoto = “\\company-WEB-01\media$\media\”+$image.split(‘/’)[2]+”\” $findthumbphoto = Get-Childitem –Path $dirphoto -Include *_thumb* # the cms creates a thumb for uploded picture this finds that thumb if ($findthumbphoto) { #if thumbphoto found set ad with that pic $thumbphoto = [byte[]](Get-Content $findthumbphoto.FullName -Encoding byte) Get-ADUser $SamAccountName |Set-ADUser -Replace @{thumbnailPhoto=$thumbphoto} if (!$photo){Set-UserPhoto -Identity $email -PictureData ([System.IO.File]::ReadAllBytes($findthumbphoto.FullName)) -confirm:$false} #Write-Output $thumbphoto } if ($photo){ #if photo found set office365 with that pic Set-UserPhoto -Identity $email -PictureData ([System.IO.File]::ReadAllBytes($photo)) -confirm:$false #return the photourl to flow Write-Output $photourl.trim() } }