\n
<?xml version=\"1.0\" encoding=\"UTF-8\"?><?mso-infoPathSolution solutionVersion=\"1.0.0.61\" productVersion=\"12.0.0\" PIVersion=\"1.0.0.0\" href=\"<\/span>https:\/\/ServerName\/PWA\/WARs\/Forms\/template.xsn<\/a>\" 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=\"<\/span>http:\/\/www.w3.org\/2001\/XMLSchema-instance<\/a>\" xmlns:my=\"<\/span>http:\/\/schemas.microsoft.com\/office\/infopath\/2003\/myXSD\/2011-09-01T15:42:54<\/a>\" xmlns:xd=\"<\/span>http:\/\/schemas.microsoft.com\/office\/infopath\/2003<\/a>\" xml:lang=\"en-us\"><\/span><\/code><\/p>\n<my:WeekEndDate>2014-03-07<\/my:WeekEndDate>
\n<my:Lead>ProjectManager<\/my:Lead><\/p>\n
<my:group5><\/p>\n
<my:RisksConcerns>Risks 2<\/my:RisksConcerns><\/p>\n
<\/my:group5><\/p>\n
<my:group6><\/p>\n
<my:Milestones>Milestones 1<\/my:Milestones><my:Milestones>Milestones 2<\/my:Milestones><\/p>\n
<\/my:group6><\/p>\n
<my:group7><\/p>\n
<my:LessonsLearned>Lessons 1<\/my:LessonsLearned><\/p>\n
<\/my:group7><\/p>\n
<my:group8><\/p>\n
<my:Accomplishments>Accomplishment 1<\/my:Accomplishments><my:Accomplishments>Accomplishment 2<\/my:Accomplishments><my:Accomplishments><\/my:Accomplishments><\/p>\n
<\/my:group8><\/p>\n
<\/my:myFields><\/p>\n
\n<\/code>
\nI created the following SSIS package to<\/p>\n1. 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.
\n2. The second step is to convert all the XML files into a CSV file.
\n3. The resulting CSV files are imported into a SQL Table.<\/p>\n
Each box is a ForEach Loop Container. You will also want to create a global string variable; in this case FileName.<\/p>\n
![\"WarImportSSIS\"](\"http:\/\/markwheeler.com\/wordpress\/wp-content\/uploads\/2014\/03\/WarImportSSIS-300x205.jpg\")
<\/a>
\n
\nStarting with the conversion process: Create a ForEach Loop Container using the ForEach File Enumerator. Also set a Variable Mapping to your User::FileName.<\/p>\n![\"WarImportSSIS2\"](\"http:\/\/markwheeler.com\/wordpress\/wp-content\/uploads\/2014\/03\/WarImportSSIS2-300x286.jpg\")
<\/a>
\n
\nDrop 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.
\n![\"WarImportSSIS3\"](\"http:\/\/markwheeler.com\/wordpress\/wp-content\/uploads\/2014\/03\/WarImportSSIS3-300x81.jpg\")
<\/a>
\nNow for the magic sauce: Edit the Script and paste the following; making the applicable changes.
\n<\/code><\/p>\nusing System;
\nusing System.Data;
\nusing System.Collections.Generic;
\nusing System.Linq;
\nusing System.Text;
\nusing System.Xml;
\nusing System.IO;
\nusing Microsoft.SqlServer.Dts.Runtime;
\nusing System.Windows.Forms;<\/p>\n
namespace ST_5921bebcfd87468d967d2eef032f0def.csproj
\n{
\n[System.AddIn.AddIn(“ScriptMain”, Version = “1.0”, Publisher = “”, Description = “”)]
\npublic partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
\n{<\/p>\n
#region VSTA generated code
\nenum ScriptResults
\n{
\nSuccess = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
\nFailure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
\n};
\n#endregion<\/p>\n
\/*
\nThe execution engine calls this method when the task executes.
\nTo access the object model, use the Dts property. Connections, variables, events,
\nand logging features are available as members of the Dts property as shown in the following examples.<\/p>\n
To reference a variable, call Dts.Variables[“MyCaseSensitiveVariableName”].Value;
\nTo post a log entry, call Dts.Log(“This is my log text”, 999, null);
\nTo fire an event, call Dts.Events.FireInformation(99, “test”, “hit the help message”, “”, 0, true);<\/p>\n
To use the connections collection use something like the following:
\nConnectionManager cm = Dts.Connections.Add(“OLEDB”);
\ncm.ConnectionString = “Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;”;<\/p>\n
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.<\/p>\n
To open Help, press F1.
\n*\/<\/p>\n
public void Main()
\n{
\nString XmlInputFile = @”F:importswars” + Dts.Variables[“User::FileName”].Value;
\nString XmlOutputFile = @”F:importswars” + Dts.Variables[“User::FileName”].Value + “.csv”;<\/p>\n
string keys = “”;
\n\/* We are parsing the XML to a CSV with the following Headers *\/
\nstring headers = “”WeekEndDate”,”Lead”,”CommentType”,”Order”,”Comments””;
\n\/* These are the fields available in our XML *\/
\nstring[] list1 = new string[2] { “my:WeekEndDate”, “my:Lead” };
\nstring[] list2 = new string[5] { “my:Activities”, “my:Accomplishments”, “my:Milestones”, “my:RisksConcerns”, “my:LessonsLearned” };<\/p>\n
StreamWriter sw = new StreamWriter(XmlOutputFile, false);<\/p>\n
sw.WriteLine(headers);<\/p>\n
for (int i = 0; i < list1.Count(); i++)
\n{
\nXmlTextReader reader = new XmlTextReader(XmlInputFile);<\/p>\n
while (reader.ReadToFollowing(list1[i]))
\n{
\nstring value = reader.ReadString();
\nkeys = keys + (“”” + value.Replace(“””,”‘”) + “”,”);
\n}<\/p>\n
reader.Close();
\n}<\/p>\n
for (int i = 0; i < list2.Count(); i++)
\n{
\nXmlTextReader reader = new XmlTextReader(XmlInputFile);<\/p>\n
int j = 0;
\nwhile (reader.ReadToFollowing(list2[i]))
\n{
\nstring value = reader.ReadString();
\nj++;
\nsw.WriteLine(keys + “”” + list2[i].Replace(“my:”, “”) + “”,”” + j.ToString() + “”,”” + value.Replace(“””, “‘”) + “””);
\n}<\/p>\n
reader.Close();
\n}<\/p>\n
sw.Close();
\n}<\/p>\n
}
\n}<\/p>\n
This script opens each XML and streams it to a formatted CSV file.
\nThe next step is to import these CSV files into your SQL Table.
\nCreate another ForEach Loop Container; setting your ForEach File Enumerator to *.csv and the global variable mapping.
\nAdd a Dataflow task. This Dataflow task will contain a Flat File Source linking to an OLE Database Destination.
\nThe Flat File Source will prompt you to create a Flat File Connection. Use one of your .CSV files to get started.
\nBut in the property panel of the connection set an Expression for the ConnectionString to use your global filename variable.<\/p>\n
![\"WarImportSSIS4\"](\"http:\/\/markwheeler.com\/wordpress\/wp-content\/uploads\/2014\/03\/WarImportSSIS4-300x116.jpg\")
<\/a>![\"WarImportSSIS6\"](\"http:\/\/markwheeler.com\/wordpress\/wp-content\/uploads\/2014\/03\/WarImportSSIS6-300x300.jpg\")
<\/a><\/p>\n