3

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:

Approach 1

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.

Advantages:

  • It is easy to restore the SQL Database to be in sync with the SharePoint list in the event of any failure.

Disadvantages:

  • This will never be real time as it is more suited to a nightly or bulk load.

Approach 2

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.

Advantages:

  • No issues with permissions or security regarding the remote access of XML files.

Disadvantages:

  • 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.

Improve this question
2
  • Which versions of the different platforms/software are you running? InfoPath, SQL, SharePoint, etc.
    – thanby
    Jan 7, 2014 at 16:02
  • Good point, we're running InfoPath 2013, SQL Server 2008 and SharePoint 2013. I'll update my question to reflect this.
    – philip.yuile
    Jan 7, 2014 at 16:34

1 Answer 1

Reset to default
1

Considerations:

Extracting column from submitted form XML is not reliable approach.

And you need something more real-time than scheduling technologies - SSIS, Timer Jobs

I would recommend a third approach.

  1. Those column which you want in SQL, mark them for 'publish to SharePoint' (to appear as columns in form library), you can control what to show them from 'View' settings.

  2. Create custom WF on Form Library with code behind using Visual Studio

  3. Inside WF, get the columns, process them, and push in database

You can also call WCF from WorkFlow if it is already built to do the job or if you want to reuse the Service..

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.