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
- 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.
- In Form Options, select the fields that you want to ‘promote’ to SharePoint.
- Publish the form
- 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
- Create a table in your database with fields to store the data from the form
- 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
- Once you have tested the process (see steps below), you can schedule the SSIS package to run on as frequently as you need.
- Create a new InfoPath form and submit the form to the library
- In SharePoint, check to see that the promoted fields are populated
- In Visual Studio run the SSIS package (you may need to ‘Run as Administrator’ when you start Visual Studio for this to work
- Check the table in SQLserver to see if any new data has arrived
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.
How to promote fields from InfoPath to SharePoint
How to create an SSIS package with SQL Server Data Tools
5 thoughts on “SSIS, InfoPath and SharePoint Lists”
How do we get data from an on premise list to a SharePoint Online Destination via Odata? Does Odata only go one way?
SharePoint Online lists can be updated via REST Api.
Have you seen this? https://ssisconnectorsharepointonline.codeplex.com
It can be done via PowerShell http://sharepoint.stackexchange.com/questions/112382/how-to-use-csom-powershell-to-update-list-items-in-o365-from-csv
I hope this helps 🙂
Can the OData connection credential be made as sensitive parameters when scheduling the SSIS job? Tried it and I can see that the connection string contains user ID and cookie. There is also a password property but it is displayed as plain text.
Thanks for your post! I found this extremely helpful!
Thank you very much.. This tutorial really helped understand SSIS