Had a request to import InfoPath forms into a SQL table for consolidating reporting purposes. In this scenario the InfoPath form data is stored in the form XML itself.
The InfoPath form is for tracking project activities and has 6 fields: Project Leader, Week End Date, Accomplishments, Milestones, Risks, Lessons Learned.
There is no easy way to directly import the InfoPath XML with SSIS. The XML is not in a format that the SSIS XML Task can deal with.
In this scenario, my InfoPath XML looks like this:
<?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution solutionVersion="1.0.0.61" productVersion="12.0.0" PIVersion="1.0.0.0" href="https://ServerName/PWA/WARs/Forms/template.xsn" name="urn:schemas-microsoft-com:office:infopath:WARs:-myXSD-2011-09-01T15-42-54" ?><?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?><my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2011-09-01T15:42:54" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-us">
<my:WeekEndDate>2014-03-07</my:WeekEndDate>
<my:Lead>ProjectManager</my:Lead>
<my:group5>
<my:RisksConcerns>Risks 2</my:RisksConcerns>
</my:group5>
<my:group6>
<my:Milestones>Milestones 1</my:Milestones><my:Milestones>Milestones 2</my:Milestones>
</my:group6>
<my:group7>
<my:LessonsLearned>Lessons 1</my:LessonsLearned>
</my:group7>
<my:group8>
<my:Accomplishments>Accomplishment 1</my:Accomplishments><my:Accomplishments>Accomplishment 2</my:Accomplishments><my:Accomplishments></my:Accomplishments>
</my:group8>
</my:myFields>
I created the following SSIS package to
1. Download all the InfoPath XML files from the Sharepoint Document Library to a folder on my database server. This is covered in previous postings here.
2. The second step is to convert all the XML files into a CSV file.
3. The resulting CSV files are imported into a SQL Table.
Each box is a ForEach Loop Container. You will also want to create a global string variable; in this case FileName.
Starting with the conversion process: Create a ForEach Loop Container using the ForEach File Enumerator. Also set a Variable Mapping to your User::FileName.
Drop a Script Task into this container: Make sure you set the properties on your script to target the .NET Framework 3.5 and add the references shown.
Now for the magic sauce: Edit the Script and paste the following; making the applicable changes.
using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
namespace ST_5921bebcfd87468d967d2eef032f0def.csproj
{
[System.AddIn.AddIn(“ScriptMain”, Version = “1.0”, Publisher = “”, Description = “”)]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables[“MyCaseSensitiveVariableName”].Value;
To post a log entry, call Dts.Log(“This is my log text”, 999, null);
To fire an event, call Dts.Events.FireInformation(99, “test”, “hit the help message”, “”, 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add(“OLEDB”);
cm.ConnectionString = “Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;”;
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
public void Main()
{
String XmlInputFile = @”F:importswars” + Dts.Variables[“User::FileName”].Value;
String XmlOutputFile = @”F:importswars” + Dts.Variables[“User::FileName”].Value + “.csv”;
string keys = “”;
/* We are parsing the XML to a CSV with the following Headers */
string headers = “”WeekEndDate”,”Lead”,”CommentType”,”Order”,”Comments””;
/* These are the fields available in our XML */
string[] list1 = new string[2] { “my:WeekEndDate”, “my:Lead” };
string[] list2 = new string[5] { “my:Activities”, “my:Accomplishments”, “my:Milestones”, “my:RisksConcerns”, “my:LessonsLearned” };
StreamWriter sw = new StreamWriter(XmlOutputFile, false);
sw.WriteLine(headers);
for (int i = 0; i < list1.Count(); i++)
{
XmlTextReader reader = new XmlTextReader(XmlInputFile);
while (reader.ReadToFollowing(list1[i]))
{
string value = reader.ReadString();
keys = keys + (“”” + value.Replace(“””,”‘”) + “”,”);
}
reader.Close();
}
for (int i = 0; i < list2.Count(); i++)
{
XmlTextReader reader = new XmlTextReader(XmlInputFile);
int j = 0;
while (reader.ReadToFollowing(list2[i]))
{
string value = reader.ReadString();
j++;
sw.WriteLine(keys + “”” + list2[i].Replace(“my:”, “”) + “”,”” + j.ToString() + “”,”” + value.Replace(“””, “‘”) + “””);
}
reader.Close();
}
sw.Close();
}
}
}
This script opens each XML and streams it to a formatted CSV file.
The next step is to import these CSV files into your SQL Table.
Create another ForEach Loop Container; setting your ForEach File Enumerator to *.csv and the global variable mapping.
Add a Dataflow task. This Dataflow task will contain a Flat File Source linking to an OLE Database Destination.
The Flat File Source will prompt you to create a Flat File Connection. Use one of your .CSV files to get started.
But in the property panel of the connection set an Expression for the ConnectionString to use your global filename variable.
A couple of additional notes: By default, the table creation script in the OLE Database Destination created my WeekEndDate and Comments as varchar(50) datatype.
You will want to change this if applicable in the Flat File Source Output properties.
