Jonas Lindau
Jan 28, 2010
  5784
(0 votes)

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.

  1. Use SQL and collect the data direct from database.
  2. Create my own Data Flow Source in SSIS using EPiServer assemblys.
  3. 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:

  1. The data is binary serialized and i couldn’t find a way of deserialize it.
  2. The assemblies wouldn’t run on my website, so it would be useless.
  3. 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)
{
    return xForm.GetPostedData();
}

public static XForm GetXForm(PageReference pageReference, string xFormPropertyName, string xFormFolderPath)
{
    if (pageReference == null || pageReference == PageReference.EmptyReference)
        return null;

    PageData pageData = global::EPiServer.DataFactory.Instance.GetPage(pageReference);

    PropertyData xFormProperty = pageData.Property[xFormPropertyName];
    if (xFormProperty == null || xFormProperty.Value == null)
        return null;

    if (!(xFormProperty is PropertyXForm))
        return null;

    IList<XForm> xFormFolder;
    if (xFormFolderPath != String.Empty)
        xFormFolder = XFormFolder.GetForms(xFormFolderPath);
    else
        xFormFolder = XFormFolder.GetForms();

    foreach (XForm xform in xFormFolder)
    {
        Guid formGuid = new Guid(xFormProperty.Value.ToString());

        if (formGuid.CompareTo((Guid)xform.Id) == 0)
        {
            return xform;
        }
    }

    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">
  <system.webServer>
    <handlers>
      <add name="*.ashx_GET,HEAD" path="*.ashx" verb="GET,HEAD" type="System.Web.UI.SimpleHandlerFactory"/>
    </handlers>
  </system.webServer>
</location>

 

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)
        {
            context.Response.ContentType = "text/xml";

            PageReference PageLink;
            string XFormsPropertyName;
            string XFormsFolderPath;
            try
            {
                PageLink = PageReference.Parse(context.Request.QueryString.Get("PageLink"));
                XFormsPropertyName = context.Request.QueryString.Get("XFormsPropertyName");
                XFormsFolderPath = context.Request.QueryString.Get("XFormsFolderPath");

                XForm xform = XForms.GetXForm(PageLink, XFormsPropertyName, XFormsFolderPath);

                StringBuilder sb = new StringBuilder();
                sb.Append("<XForm>");
                sb.Append("<FormName>").Append(xform.FormName).Append("</FormName>");
                sb.Append("<AllowAnonymousPost>").Append(xform.AllowAnonymousPost).Append("</AllowAnonymousPost>");
                sb.Append("<AllowMultiplePost>").Append(xform.AllowMultiplePost).Append("</AllowMultiplePost>");

                IList<XFormData> xformdata = XForms.GetXFormData(xform);
                foreach (XFormData data in xformdata)
                {
                    sb.Append("<DataItem>");
                    sb.Append("<Id>").Append(data.Id).Append("</Id>");
                    sb.Append("<DatePosted>").Append(data.DatePosted).Append("</DatePosted>");
                    NameValueCollection dataparams = data.GetValues();
                    foreach (string dataparamname in dataparams)
                    {
                        sb.Append("<").Append(dataparamname).Append(">").Append(dataparams[dataparamname]).Append("</").Append(dataparamname).Append(">");
                    }
                    sb.Append("</DataItem>");
                }
                sb.Append("</XForm>");

                context.Response.Write(sb.ToString());
            }
            catch {
            }
        }

 

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>
<FormName>My test form</FormName>
<AllowAnonymousPost>True</AllowAnonymousPost>
<AllowMultiplePost>True</AllowMultiplePost>
<DataItem>
<Id>883a4e9c-0e53-47d6-9d7d-bade1b699cd4</Id>
<DatePosted>2010-01-26 11:34:37</DatePosted>
<Lastname>Doe</Lastname>
<Firstname>John</Firstname>
<Email>john@doe.net</Email>
</DataItem>
<DataItem>
<Id>f6501f48-9301-4ce8-8443-9d79de6b03fb</Id>
<DatePosted>2010-01-26 11:44:02</DatePosted>
<Lastname>Robinsson</Lastname>
<Firstname>Mrs</Firstname>
<Email>mr@nowhere.net</Email>
</DataItem>
</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.

Jan 28, 2010

Comments

Sep 21, 2010 10:33 AM

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

Anton Kallenberg
Anton Kallenberg Sep 21, 2010 10:33 AM

Nice article!

Please login to comment.
Latest blogs
Optimizely PIM - Variant Management Overhaul & Rollout

Optimizely PIM has completely overhauled variant management with improved usability and support for variant swatches. In order to enable this...

Arthur Vander Voort | Feb 4, 2023

You Just Got Vectored! SVG Image Formats

 If you're reading this, then you've come across a need that nearly all Opti developers encounter in their careers; You need to display a vector...

Greg J | Feb 3, 2023 | Syndicated blog

CMS Core 12.12.0 delisted from Nuget feed

We have decided to delist version 12.12.0 of the CMS Core packages from our Nuget feed. As a consequence, we are also temporarily delisting version...

Magnus Rahl | Feb 3, 2023

How Optimizely Web Experimentation enables fast validation of design and UX changes

Ever wondered how to increase conversion rates and quickly validate design/UX alternatives to find the most impactful changes? I wrote a practical...

Jacob Pretorius | Jan 31, 2023

What’s next after Google Optimize’s sunsetting?

Google has announced that it is sunsetting the Google Optimize and Optimize 360 services, forcing customers to explore new platforms and invest in...

Ynze | Jan 31, 2023 | Syndicated blog

What’s next after Google Optimize’s sunsetting?

Google has announced that it is sunsetting the Google Optimize and Optimize 360 services, forcing customers to explore new platforms and invest in...

Ynze | Jan 31, 2023 | Syndicated blog