In this training module we will build an Office 365 SharePoint solution that imports data from a Microsoft Azure SQL Server Database to a SharePoint List using the Infowise tool called Smart Import Pro.
In this video I create and example project that uses Smart Import Pro to to import data from a Microsoft Azure Database.
For this example I am going to import data from a test Microsoft Azure SQL Server database for my test Rental Company. An Azure MSSQL database is in the "Cloud" which means you do not need to install a local version of MSSQL Server. You do need to login to https://portal.azure.com and build setup a database from there. I include links at the bottom of this page that will help you get started with that.
With our Azure Database we are going to simulate having an external payment processor collecting rent. This payment information is stored in the Microsoft Azure database and we are going to import it using Smart Import Pro.
This screen is a shows the setup of my test Azure database. Two important screens we need for Infowise are the Connection Strings which you can see here on this page. Most of the time you will be using the ADO.NET (SQL Authentication) connection string.
Now that you have your connection string you will need to input your User Name and Password. The format should look something like this:
Server=tcp:properties.database.windows.net,1433;Initial Catalog=PropertyManagement;Persist Security Info=False;User ID=MainDevUser;Password=Password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
Make sure you are using a username that has at least read access to the table and make you remove the brackets around the username and password.
The second is the Firewall Settings. Azure requires you to enter the IP Address of the servers that will connect to your Azure Database. In order for InfoWise to import data from Azure we need to enter the Infowise IP address into Azure. Refer to this page for the latest IP ranges you need to allow.
This is SQL Server Management Studio connected to the payment processors Azure Database. Here you can see the Properties table we are going to work with today. There are 4 fields.
- PropertyID - this is the Primary Key for the table and the main ID we are going to use to identify each property.
- LastUpdated - This is a datetime column that updates every time a record is changed.
- Address - The address of the property
- RentPaid - Simple yes/no field that shows if the rent has been paid this month or not.
Next I am going to insert 5 test records, if you are doing this example you can use the following SQL script to insert test data or build your own.
INSERT INTO [dbo].[Properties]
VALUES(GetDate(),'134 Dark St',0),
(GetDate(),'100 Green St',1),
(GetDate(),'134 Purple Rd',1),
(GetDate(),'134 White Pl',0),
(GetDate(),'134 Red BLVD',0)
Here is more information on setting up Microsoft Azure.
Main Azure Site
Download SQL Server Management Studio
Azure Database getting Started
Azure Database Connect through Sql Server Management Studio
Once you have completed setting up your database please continue to step 2 where we will build our Smart Import.
10/5/2017 1:55 PM