Try our conversational search powered by Generative AI!

Jonas Lindau
Jan 28, 2010
(0 votes)

XForms and SSIS (Integration services)



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: 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);
        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">
      <add name="*.ashx_GET,HEAD" path="*.ashx" verb="GET,HEAD" type="System.Web.UI.SimpleHandlerFactory"/>


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;
                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();

                IList<XFormData> xformdata = XForms.GetXFormData(xform);
                foreach (XFormData data in xformdata)
                    NameValueCollection dataparams = data.GetValues();
                    foreach (string dataparamname in dataparams)

            catch {



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

<FormName>My test form</FormName>
<DatePosted>2010-01-26 11:34:37</DatePosted>
<DatePosted>2010-01-26 11:44:02</DatePosted>


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!


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


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
Join the Content Recommendations Work Smarter webinar May 8th 16.00-16.45 CET with expert Aidan Swain

Learn more about Content Recommendations, with Optimizely’s very own Senior Solutions consultant, Aidan Swain . He will discuss best practices and...

Karen McDougall | Apr 12, 2024

Plugin for Identifying and Bulk Deleting Orphaned Missing Properties in Optimizely

I am aware that the Optimizely World community has extensively discussed this topic, with numerous solutions and code snippets available to help...

Adnan Zameer | Apr 11, 2024 | Syndicated blog

Enhancing the Authoring Experience: Extending the LinkItem

The LinkItem field is one of the most demanded properties by the community, allowing editors to effortlessly create and manage links across pages a...

Santiago Morla | Apr 10, 2024 | Syndicated blog

The distinctions between Optimizely Web Experimentation and Optimizely Feature Experimentation

This blog is part of the series - Unlocking the Power of Experimentation: A Marketer's Insight. Let’s dive into Optimizely's powerful experimentati...

Holly Quilter | Apr 9, 2024