We are looking at using InfoPath 2013 to capture simple sets of data without the need the need for writing custom applications. However, we still need the information to be stored in a SQL Server 2008 database for reporting purposes.
I have been working on a solution to extract data into a SQL Server database from InfoPath form submissions. Currently, the InfoPath form submits to a SharePoint list. This list stores only some of the fields I require directly. The complete entries are stored in XML files and the name of the XML file for each entry is stored in the list.
I have reached a crossroads and I have considered two possible approaches. I am looking for advice as to which approach is best practice, or a better approach that I am not aware of. Here are the two approaches:
The InfoPath form continues to submit to the SharePoint list only. A nightly SSIS package truncates the table(s) in the database and then accesses the SharePoint list using SharePoint Source and Destination (http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652). It uses the XML file related fields in the list to find and open the XML file for each submission and read the remaining fields needed for that submission. It then inserts each entry into the database.
- It is easy to restore the SQL Database to be in sync with the SharePoint list in the event of any failure.
- This will never be real time as it is more suited to a nightly or bulk load.
The InfoPath form submission is changed to have a rule with two actions: submission to the existing SharePoint list and submission to a new WCF service. The WCF service reads the XML coming in from the InfoPath submission and uses this to insert/update rows in the SQL database.
- No issues with permissions or security regarding the remote access of XML files.
- Create/Update must be managed by WCF service, as form submissions could be from existing entries or new entries.
- The stream of data splits in two directions, which could create partial failures leading to discrepancies between the SharePoint List and the reporting database.
- We are attempting to reduce the need to write custom code and would prefer a configuration-based solution.
Which approach would be best practice? Is there a better or more straightforward way of approaching this problem? Thanks very much in advance for your help.