Jonas Lindau

Jan 28, 2010

6332

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

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)
{
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.

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 Sep 21, 2010 10:33 AM

Nice article!

Please login to comment.