Have you ever needed  a low cost solution for capturing data in a form on mobile device and then pull the data back into an in house SQL database. In this solution data is entered into an InfoPath form on a tablet (Windows, iOS or Android), submitted to a SharePoint Online form library and then pulled into a SQLserver database using SQL Integration Services (SSIS).

This solution will also work with standard SharePoint lists and can be used with SharePoint Online, SharePoint 2013 or SharePoint 2016.

The things you need to know:

• InfoPath forms can be hosted in a SharePoint Online forms library
• InfoPath can ‘promote’ fields from the form into metadata fields in the SharePoint Online forms library e.g. each form will be saved and selected fields will appear as columns on the form library.
• SharePoint Online form libraries can be queried using an oData connection
• SQL Server Integration Services (SSIS) can use oData as a data source and push or pull data to and from the data source
• SSIS can be scheduled

Note: SQL Server Standard Edition (or better) includes SSIS

InfoPath steps:

1. Using InfoPath Designer, create a form and publish the form to a SharePoint Form library. The easiest option is to do this from InfoPath and let it create the Form library for you.
2. In Form Options, select the fields that you want to ‘promote’ to SharePoint.
3. Publish the form
4. In SharePoint verify that the promoted fields are showing as columns in the Forms library

SQL Server and SSIS steps:

In this step we setup a database and SSIS package. The connection is initiated from the server running SSIS and so this server must be able to make an outbound connection to Office 365.

SSIS packages are created using SQL Server Data Tools

1. Create a table in your database with fields to store the data from the form
2. Using Visual Studio create an SSIS package
   • Create the  SQLserver data connection for SQL table created in step 1
   • Create the oData connection to the SharePoint Online list. You will need:
     • The URL for the Form library (the site URL)
     • Office 365 credentials with permission to read the list. These credentials will be saved in the SSIS package
     • See the authentication note below
   • Map the fields from the oData Connection to the SQLserver Connection fields
   • Save
3. Once you have tested the process (see steps below), you can schedule the SSIS package to run on as frequently as you need.

Testing:

1. Create a new InfoPath form and submit the form to the library
2. In SharePoint, check to see that the promoted fields are populated
3. In Visual Studio run the SSIS package (you may need to ‘Run as Administrator’ when you start Visual Studio for this to work
4. Check the table in SQLserver to see if any new data has arrived

Authentication issue:

When creating the oData connection to Office 365, you must manually change the setting for ‘Microsoft Online Services Authentication’ to true. This is in the oData Connection Manager settings, under Connection in the Security section. More details here.

Resources:

How to promote fields from InfoPath to SharePoint

How to create an SSIS package with SQL Server Data Tools

MSDN: SSIS oData tutorials