Automate SDS CSV Upload with Power Automate

With School Data Sync (SDS) and Power Automate, you can sync your locally exported SIS/SMS CSV files automatically and keep your Microsoft 365 data up to date. No more manual changes or manual csv uploads through the SDS user interface for the active academic session. Provides the following benefits:

• Makes managing and monitoring local csv file uploads easier.
• Keeps Microsoft 365 users and classes updated based on changes from SIS/SMS rather than manual changes by educators or administrators.
• Manage entire upload process via Power Automate UI in the cloud, which is simpler to use than PowerShell scripts.
• All monitoring and management of the solution happens in Microsoft 365.
• Server OS Support for better reliability and on-premises data gateway for local file upload.

Overview

Begin with installing Configuring CSV file location, which includes installing an on-premises Data Gateway, which should take about 5 minutes to configure. Next you will, Configure Power Automate Flow to use the School Data Sync template that connects to the gateway, and use the SDS connector to push files into SDS for processing.

Important

Do not forget to create a Local Service Account on the machine that has the Gateway installed on it. Instructions can be found here. This is required for the File System portion of the Flow creation.

Prerequisites

Refer to Install an on-premises data gateway for a full list of requirements and in case there are any installation problems.

Minimum requirements

• .NET Framework 4.6 (Gateway release August 2019 and earlier)
• .NET Framework 4.7.2 (Gateway release September 2019 and later)
• A 64-bit version of Windows 7 or a 64-bit version of Windows Server 2008 R (or later)

Recommended

• An 8-core CPU
• 8 GB of memory
• A 64-bit version of Windows Server 2012 R2 or later
• Solid state drive (SSD) storage for spooling

Important considerations

• Gateways aren't supported on Server Core installations.
• The user installing the gateway must be the admin of the gateway.
• The gateway can't be installed on a domain controller.
• If you're planning to use Windows authentication, make sure you install the gateway on a computer that's a member of the same Active Directory environment as the data sources.
• You shouldn't install a gateway on a computer, like a laptop that might be turned off asleep, or disconnected from the internet. The gateway can't run under any of those circumstances.
• If a gateway uses a wireless network, its performance might suffer.
• You can install up to two gateways on a single computer: one running in personal mode and the other running in standard mode. You can't have more than one gateway running in the same mode on the same computer.

Ports

The gateway creates an outbound connection to Azure Service Bus. It communicates on outbound ports: TCP 80, 443 (default), 5671, 5672, 9350 through 9354. The gateway doesn't require inbound ports. If you restrict outbound ports on your server, you must ensure these outbound ports are open.

Configure CSV file location

Verify the region of your Power Apps environment

1. Sign into the Power Platform admin center with your global administrator account.

2. Navigate to Environments and verify the region of the environment you're using. The region is needed to help ensure that you install the on-premises Data Gateway to use the same region.

   

Download the on-premises Data Gateway

1. Download, save, and run the gateway installer on a local computer.

2. Accept the default installation path, or specify the location on your computer where you want to install the gateway.

3. Review and accept the terms of use and privacy statement, and then choose Install.

   

4. After the gateway successfully installs, provide your global administrator account, and choose Sign in.

   

5. Choose Register a new gateway on this computer > Next, which registers your gateway installation with the gateway cloud service.

   

6. Complete requested information for your gateway installation:

   • The name you want for your installation
   • The recovery key you want to create, which must have at least eight characters

   Important

   Save and keep your recovery key in a safe place. You need this key when you change the gateway's location, or when you migrate, recover, or take over an existing gateway.

7. Select Configure.

   

8. On the next screen, don’t change the recommended region under Select Region unless you know with certainty that you need to change the region. Refer to the Power Apps environment region from Verify the region of your Power Apps environment to ensure the selected Region is the same. Then select Done.

   

9. After the confirmation page appears, choose Close. The installer confirms that your gateway is now online and ready for use.

   

   Note

   You don't need to select on the link that reads Create a gateway in Azure. For more context, this article explains how the data gateway works.

Create a new local service account

1. Go to Computer Management.

2. Double-click on Local Users and Groups.

3. Double-click on Users.

4. Right-click in the center pane to Add New User.

5. Enter a User Name like SDSServiceAccount and record the name and password you choose as you need to use it again.

6. Uncheck the option “User must change password at next sign in.”

7. Optional: Check the option to “Password never expires.”

8. Select Create.

   

Create a folder for your CSV files

Set up the folder location where the export of your CSV files needs to exist.

1. Open File Explorer.
2. Navigate to your C:\drive.
3. Right select and select New > Folder.
4. Enter Folder Name. In this example, we're creating the folder named “CSV files.”

Give your local service account full permissions on the CSV folder

1. Right select on the folder you created in Step 3, and go to Properties.

2. Go to the Security tab.

3. Select Edit.

   

4. Select Add.

5. Set the Location option to your local machine. This is the first option in the list when you select Locations.

6. Type in the SDSServiceAccount name (that you created in Step 2, number 5) in the objects names field (third field) and select Check Names.

7. Select OK.

   

8. Ensure full control is granted to the service account. Select Apply.

9. Select OK.

   

Copy your CSV files into the folder

1. Copy the latest extract of your CSV files into the folder.

   

   Caution

   Make sure that you only have CSV files in this folder.

Configure Power Automate Flow

Make sure you have Power Automate app enabled

1. Launch your browser.
2. Go to https://admin.microsoft.com and sign in with your Global Admin Account.
3. Select on Users, then Active Users.
4. Search for your Admin account in “Search users” box.
5. Select on your account.
6. Select License and apps.
7. Under Apps ensure "Power Automate for Office 365" is enabled. If not, select then select Save.

Important

The File System connector can only be set up or modified from within the template creation process. If there is ever any changes needed to the File System; including Username, Password, or Folder location, you will need to recreate the Flow entirely.

Set up your Template

1. Launch your browser.

2. Go to https://make.powerautomate.com.

3. Ensure you're signed in with your Global Admin Account.

   Caution

   Your account should be the same as the one you used to set up the gateway duirng the setup for Configure CSV file loacaiton.

4. Navigate to the Templates, on the left side.

   

5. In the Templates search prompt, search for School Data Sync. Select Automate School Data Sync CSV Upload.

   Caution

   The following are for SDS (Classic), do not select or use with the new SDS experience:

   Automate CSV files uploaded to your School Data Sync (Classic) Sync Profile

   Send Email on School Data Sync (Classic) Errors

   School Data Sync (Classic) via SFTP

   

   

6. Select Create for the File System connector. You're prompted to set up a new File System connector and use the Data Gateway and the Local Service Account sign-in credentials to access the CSV file folder location.

   Caution

   If you already have a File System available, you will need to click on the ellipsis and choose to create a new connector.

   

7. Name the Connection. Recommended naming it something related to its function. In our example, SDS CSV File Automation.

8. In the Root Folder, enter the path to your CSV File Folder created previously. In our example, "C:\CSV_Files."

9. Authentication Type should be set to Windows by default.

10. Username is the Full username for the Service account created previously including the machine name. In our example, DESKTOP-SD85O7N\SDSServiceAccount.

11. Enter the Password for that local service account.

12. Select the Gateway that was set up previously.

    Tip

    If you are not seeing the Gatway that was setup previously follow missing on-premises data gateway troubleshooting steps.

13. Select Create.

    

14. Select Continue to begin the Flow Creation/Customization process.

Get the School Data Sync | Connect data source | Flow Id

1. In a separate tab, go to the SDS portal at https://sds.microsoft.com.

2. Navigate to Sync | Configuraiton | Connect data tab. Go to the section for the corresponding source configuration that matches the CSV data extracts that are to be uploaded.

   • If you need to create a corresponding Connect data configuration for your Source in SDS, follow the relevant instructions based on the CSV format you're using. This needs to be done to complete the configuration of your Power Automate flow.
     • Connect data with SDS v2.1 CSV
     • Connect data with SDS v1 CSV

3. At the bottom of the corresponding Connect data configuration for your Source locate Id, which is represented as a GUID. Copy it so it can be used later when configuring the Power Automate flow.

   

Create and Configure an Application

Important

You need an Azure account with an active subscription. If you don't have an Azure subscription, you can set up a free subscription here, or check the the current list of Azure offers.

In a separate tab, follow the steps to:

1. Register an Application

2. Generate App Client Secret

3. Grant Application Permissions to Application

   • IndustryData-InboundFlow.ReadWrite.All
   • IndustryData-DataConnector.Upload

4. Grant Admin Consent

   Note

   Go to Configure Client Application for more indepth information.

Set up Power Automate Flow

1. Go back to the Power Automate tab to continue where we left off.

2. To begin configuring the flow, select Continue.

3. If desired, change the "Recurrence Frequency" for the specific time of day and how often you wish the sync to run.

   

   Tip

   It is recommended that you allow plenty of time for the export and copy to complete to the CSV folder before the defined recurrence frequency is run.

4. Enter the Client ID and Client Secret values from Obtain Client ID and Client Secret.

   • Enter the Client Id in the Clientid step.
   • Enter the Client Secret in the Client Secret step.

   

5. Enter your TenandId.

6. Enter the Id value from values from Get the School Data Sync | Connect data source | Flow Id

   

7. Enter the full file path, created when setting up Create a folder for your CSV files within the Folder prompt of the "List files in folder" block that is within the "Upload Files" section. To verify the connection is correct you can select on the Folder image at the end of the box, and then the arrow. Should only have the text "No Items" within it.

   

8. Select save and then test the connector by selecting Test on the top right of the page. Choose "I'll perform the trigger action" then save and test. Finally confirm by selecting "Run Flow" to test your flow.

9. The flow should run and receive all green check marks stating that everything was successful. If there are issues, reach out to SDS Support or your deployment manager.

   Note

   The Power Automate template only uploads the files to SDS to be included in the next Sync run. Sync runs are typically scheduled to run every 12 hours. Please check SDS | Home dashboard or Sync | Run to confirm that the validation and sync process completed appropriatly for the subsequent sync run after automated upload.