In the Fall of 2010, Winona State University implemented a cloud-based application from Hobsons® called Connect® – a CRM (Customer Relationship Management) tool designed to help manage student communications from the inquiry phase through application to enrollment. A significant challenge in the implementation was extracting, transforming, and loading the Connect database with information from ISRS – our student information system.
Like most Hobsons’ clients, we initially used SFTP (a secure version of FTP that encrypts data during transmission) to upload data files to Connect. To obtain data for uploading, we used a set of views and stored procedures to extract and transform information from our student information system. We used a PowerShell® script to run the stored procedure, export the results to a file, and upload the file to Connect via SFTP. A diagram of this process is shown below:
Although this process worked initially, we quickly ran into a few problems and concerns. The first of these was just the sheer bulk of data. Use of Connect grew very rapidly to the point where we have created well over a 100 attributes and needed to upload data every day. Uploading this large amount of data proved to be a challenge. We attempted to use local history files to upload only new or changed information, but ran into problems when Connect imports failed or rejected records. We also could not control the timing of Connect imports, which sometimes did not finish until long after a file was uploaded.
In addition to these issues, members of our IT Systems group voiced concerns regarding security. While SFTP encrypts files during transfer, an unencrypted copy of the file existed on a file share prior to transmission. Assess to the file share was limited, but concerns lingered over so much data existing in plain, unencrypted format.
To address these issues, we turned to using SOAP-compliant web services provided by Hobsons. A web service is a special communications protocol designed to allow two computers to exchange information over the Internet. Commands and data are encoded into XML messages and delivered over the Internet to the other computer using HTTPS, the same Internet protocol used by many web sites to exchange credit card other sensitive information. In general, our new process works as follows:
The new process is very secure and has the advantage of supporting more granular updates and better logging. In my next Blog post, I’ll provide some actual examples of calling Hobsons’ web service using PowerShell.