XForms and SSIS (Integration services)
Objective
Collect and transform data collected by XForms using SSIS.
Recently i ran into a situation where i needed to collect data from EPiServer (XForms) using SSIS. At first, i had a few possible ways to solve this task.
- Use SQL and collect the data direct from database.
- Create my own Data Flow Source in SSIS using EPiServer assemblys.
- Simply create an XForm which posts to a custom made ASPX-page.
After a few hours i knew none of the above would do the trick for me because:
- The data is binary serialized and i couldn’t find a way of deserialize it.
- The assemblies wouldn’t run on my website, so it would be useless.
- My network is extremely segmented, so the ASPX-page wouldn’t be able to store data on my SSIS server.
So, what to do?
I decided to create a generic handler on my site which exposed the data as XML. It would than be an easy task for SSIS to get the data, live, using the XML Source component.
Lets begin
I found this excellent blog entry by Dominik Juszczyk: http://blog.juszczyk.biz/2008/05/get-posted-data-xformdata-from-xform/ which describes how to get the data. This turned out to be a good start for me.
I created a class called XForms containing two methods. One method which return the entire XForm, and one which return the posted data.
| public static IList<XFormData> GetXFormData(XForm xForm) public static XForm GetXForm(PageReference pageReference, string xFormPropertyName, string xFormFolderPath) PageData pageData = global::EPiServer.DataFactory.Instance.GetPage(pageReference); PropertyData xFormProperty = pageData.Property[xFormPropertyName]; if (!(xFormProperty is PropertyXForm)) IList<XForm> xFormFolder; foreach (XForm xform in xFormFolder) if (formGuid.CompareTo((Guid)xform.Id) == 0) return null; |
Create the handler
The next step to take were to create the handler. I simply created a new generic handler on my site.
The first thing to do is to make sure the handler works. I created a folder at root level called Modules. For the handler to work at all, i had to add a location element in web.config.
| <location path="Modules"> |
When this issue were solved, the rest is quite easy. All i had to do was to make sure the handler produces XML. A also needed it to take some parameters, like PageReference to the page containing the form, XForm property name and XForm folder. The folder parameters isn’t really needed, but if you have a lot of forms, you don’t want to loop through them all… The lines XForms.GetXForm and XForms.GetXFormData calls the methods in the class described above.
| public void ProcessRequest(HttpContext context) PageReference PageLink; XForm xform = XForms.GetXForm(PageLink, XFormsPropertyName, XFormsFolderPath); StringBuilder sb = new StringBuilder(); IList<XFormData> xformdata = XForms.GetXFormData(xform); context.Response.Write(sb.ToString()); |
Result
Time for testing. I logged on my EPiServer and created a XForm with the elements Lastname, Firstname and Email. After some posting to the form i was anxious to try my new handler. I opened up my browser and typed the URL http://localhost/Modules/XFormsDataExport.ashx?PageLink=131&XFormsPropertyName=XForm&XFormsFolderPath=
The result was exactly what i needed
| <XForm> |
Consume the XML in SSIS
This is a really simple task. All i had to do was to add an XML Source, enter the URL to my handler, generate an XSD and map the columns. Voila!
Conclusion
For security reasons i locked down the access to my handler. I simply used IIS IP security to do this. I also made a lot of data validation in my SSIS package to make sure i didn’t get any dirty data.
Just for your info, in CMS 6 XForms uses the Dynamic Data Store and therefore posts are viewable via a normal database view.
/ Paul Smith
Nice article!