CSV File Sync Automation

There is a new way to automate CSV synchronization in School Data Sync (SDS), which moves the process from a Windows scheduled task into Microsoft Flow with the SDS Flow Connector. The benefit of using Flow are as follows:

  • Makes managing and monitoring file uploads easier.
  • Provides nice visualization to see a history of file uploads and the result of each stage of upload process.
  • No more cached credentials of the AAD global admin on the local server.
  • Manage entire upload process via UI in the cloud, which is much simpler to use than PowerShell scripts.
  • All monitoring and management of the solution happens in Office 365.
  • Server OS Support for better reliability.

You will first need to install an On-Premise Data Gateway, which should take about 5 minutes to configure. Then, you will go to the Flow website to use the “Automate CSV Files upload to your School Data Sync profile” template that will connect to the gateway, and use the SDS connector to push files into SDS for continuous processing.

There is one limitation. Each file has a maximum size of 30 MB, which is approximately the size of a 1.4 million student enrollment files. Assuming eight classes per student, this would be 175K students per data set. If your files do not meet this criteria, you will need to continue uploading via the SDS toolkit, or split your files for each set of 175K users to keep them under the data transfer limits.

Prerequisites

Minimum requirements

  • .NET Framework 4.5.2
  • 64-bit version of Windows 7 or Windows Server 2008 R2 (or later)
  • 8 Core CPU
  • 8 GB Memory
  • 64-bit version of Windows Server 2012 R2 (or later)

Important considerations

You can install the on-premises data gateway only on a local computer, not a domain controller. You don't have to install the gateway, however, on the same computer as your data source. Also, you need only one gateway for all your data sources, so there is no need to install the gateway for each data source.

Ports

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

Next Steps:

  1. Configure CSV file location

  2. Set up your SDS Flow