Submitting Date/Time with InfoPath to Web Services

 Sharepoint, Software  Comments Off on Submitting Date/Time with InfoPath to Web Services
Feb 182016
 
Share

I created an Infopath for users to request teleconference numbers.
I have two lists; Schedule and Conference List.

Schedule list had these columns: Title, Start Date, End Date, Attendees, Status, Assigned.
Conference List had these columns; Source (which will be set to Title above), Title, Phone Number, Start Date, End Date

Users get a standard request form which pulls and updates from the Schedule list.
ConReq

Admin/Operators receive workflow email that a request was placed and get an expanded form.

ConOpView

The magic is on the Assign button on the Operator section.
Rules are set to submit 5 values to the Conference List using a Web Service.

There are many good blogs that detail this process.
Reference with Screenshots: http://jaliyaudagedara.blogspot.com/2011/03/submitting-data-to-sharepoint-2010-list.html

The first step is to create an “Add List Items.xml.” This is the data to be inserted into the Conference List:

<?xml version="1.0" encoding="UTF-8"?>
<Batch OnError="Continue">
    <Method ID="1" Cmd="New">
        <Field Name="Title"></Field>
        <Field Name="Source"></Field>
        <Field Name="Number"></Field>
        <Field Name="Start"></Field>
        <Field Name="End"></Field>
    </Method>
</Batch>

The easy part is submitting text fields. Date/Time fields will fail due to invalid type errors. A common problem I’ve seen reported is that you won’t get an error on submit and no data will be posted to your list.

This the workaround for Date/Time.
Create a textbox on your form; you can hide this later.
Set the value to your original form’s time value with the following formula.

concat(YourTimeField,"Z")

TimeField

This is the important piece. The UpdateListItems call needs that “Z” appended to the Time portion.
Your modified time output will look like YYYY-MM-DDTHH:MM:00Z.

When creating your Submit to Web Service rule; add the Submit portion first. In my example the rule is “AssignNumber”.
Go to Publish and Export Source Files. You will need to edit the manifest.xsf. Find your submit rule by name and paste the following BEFORE the submitAction.

<xsf:assignmentAction targetField="xdXDocument:GetDOM(&quot;Add List Item&quot;)/Batch/Method/Field[@Name = &quot;Title&quot;]" expression="d:Title"></xsf:assignmentAction>
<xsf:assignmentAction targetField="xdXDocument:GetDOM(&quot;Add List Item&quot;)/Batch/Method/Field[@Name = &quot;Source&quot;]" expression="xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Title"></xsf:assignmentAction>
<xsf:assignmentAction targetField="xdXDocument:GetDOM(&quot;Add List Item&quot;)/Batch/Method/Field[@Name = &quot;Number&quot;]" expression="d:Number"></xsf:assignmentAction>
<xsf:assignmentAction targetField="xdXDocument:GetDOM(&quot;Add List Item&quot;)/Batch/Method/Field[@Name = &quot;Start&quot;]" expression="xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:TimeStart"></xsf:assignmentAction>
<xsf:assignmentAction targetField="xdXDocument:GetDOM(&quot;Add List Item&quot;)/Batch/Method/Field[@Name = &quot;End&quot;]" expression="xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:TimeEnd"></xsf:assignmentAction>

This allows you to set your Add Item CAML to push the correct values to the Web Service Submit.

From InfoPath Designer; open the manifest.xsf file. Your rules may look like this.

ConAsnRules

You can edit the value of these rules as necessary. Note that I point my Start and End Date columns to the concat fields I created earlier.

Import InfoPath XML to SQL Table with SSIS

 Computer, Software, Uncategorized  Comments Off on Import InfoPath XML to SQL Table with SSIS
Mar 102014
 
Share

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.

WarImportSSIS


Starting with the conversion process: Create a ForEach Loop Container using the ForEach File Enumerator. Also set a Variable Mapping to your User::FileName.

WarImportSSIS2


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

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.

WarImportSSIS4

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.

WarImportSSIS6

 

 Posted by at 11:38 pm  Tagged with: ,