SSIS: Upload file to Sharepoint Document Library (revisited)

 Computer, Sharepoint, Software  Comments Off on SSIS: Upload file to Sharepoint Document Library (revisited)
Apr 242014
 
Share

A while ago I showed how to upload a file to a Sharepoint Document Library using a File Task in SSIS.

Here’s another solution…Simply cut and paste the following into a Script Task in your SSIS package. This is the simple version as the local file,
Sharepoint URL and service account credentials are hardcoded. You will also need to target your script task for .Net Framework 3.5


using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using System.Windows.Forms;
using System.Net;
using System.IO;

namespace ST_99ff82c376eb40a2829b74f045a2f3be

{

[System.AddIn.AddIn(“ScriptMain”, Version = “1.0”, Publisher = “”, Description = “”)]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
WebClient myWebClient;
string LocalFileName;
string DestinationURL;
bool FireAgain = true;

try

{

myWebClient = new WebClient();
LocalFileName = “F:\SomeFolder\SomeFile.csv”;

// Destination file name with year and month appended ( SomeFile_04-23-2014.csv )

DestinationURL = “https://servername/documentlibrary/somefile_” + DateTime.Now.ToString(“MM-dd-yyyy”) + “.csv”;

Console.WriteLine(LocalFileName);

myWebClient.Credentials = new NetworkCredential(“username”, “password”,”domain”);
myWebClient.Credentials = CredentialCache.DefaultNetworkCredentials;

Dts.Events.FireInformation(0, String.Empty, String.Format(“Uploading {0} to {1}”, LocalFileName, DestinationURL), String.Empty, 0, ref FireAgain);

// upload the file

myWebClient.UploadFile(DestinationURL,”PUT”, LocalFileName);

}
catch (Exception ex)
{
// Catch and handle error
Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0);
}
}
}
}

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: ,