Author: Kyle

The Project: Migrating Data from Legacy System to SharePoint

I recently completed a data migration project to migrate an aging Domino database to a custom site collection in SharePoint 2010. The data itself was processed in two stages: first via LotusScript to extract it from the Domino database, and then via built-in SharePoint web services to import it into the new SharePoint site collection.

Environment Limitations

The caveat in my scenario, however, is that my highest level of access to the SharePoint environment was site collection administrator. I did not have farm administrator rights, and requesting temporary rights was out of the question.

This ruled out the use of the SharePoint server-side object model, as well as the SharePoint Management Console cmdlets via PowerShell, which was by far the most favorable and straightforward approach to importing the data. This also ruled out several third-party tools, but those would have added additional cost to a project that I was already managing with zero budget.

Instead, I consumed the out-of-the-box SharePoint web services via PowerShell to process my data using the limited tools available. This post describes how I utilized a few list services to update list items and upload file attachments.

The Data to Import

I constructed new SharePoint lists to mirror the Domino database schema and then imported all of my text-based data into the new lists using datasheet views. This left me with skeleton list items that were missing dynamic content from the Domino database (i.e. rich-text data and file attachments). These items were exported from Domino using LotusScript into a directory structure based on the internal unique Domino document ID.

To add a layer of complexity, the Domino data had to be merged with a few thousand existing list items in SharePoint. In some cases, there were duplicate items, so my PowerShell script logic was designed to handle those duplicates in a graceful manner (by merging fields and archiving one of the duplicates). To handle the tens of thousands of exported file attachments from the Domino database, I designed script logic to re-attach them to the parent items in the new SharePoint site lists after successfully matching them based on the Domino document ID. I used PowerShell’s exceedingly simple filesystem cmdlets to enumerate the Domino export directory and match the name of each subfolder (the Domino document ID) to an imported list item on the SharePoint list. Once matched, the files in that subfolder were attached to the SharePoint list item and some fields updated to indicate that the item has been processed already by the script.

My Toolbox

• SharePoint 2010 Web Services
  • GetListItems Method
  • UpdateListItems Method
  • AddAttachment Method
• PowerShell 3.0 (or higher)
• PowerGUI 3.8.0.129 – a lightweight, free GUI editor for PowerShell scripts. I used this instead of the PowerShell ISE for its advanced debugging features. These were essential to my success with digesting the SharePoint web services, as they allow you to visualize the web service calls and returns in realtime.
• CAML Designer 2013 – an excellent, lightweight tool for building CAML queries from existing SharePoint data. I used the 2013 version since it is backward compatible with 2010. In a few short clicks, you can connect the Designer to your SharePoint site, select your target list, enter the query conditions, and like magic, the CAML is generated for you in a variety of formats.

The Script

Assumptions

I make certain assumptions about the reader’s knowledge of PowerShell and general SharePoint development, given that you probably would not be interested in my write-up unless you have a reason to be using these tools together.

First-things-first: Framework (My Scripting Style), Variables, and Definitions

I will openly admit that I may carry forward some well-worn habits from my past scripting experience with VBScript, shell, batch, LotusScript, etc. If you entered the scripting world starting with PowerShell, please try to overlook these.

Since I am a faithful advocate of debugging options in my major scripts, I start this one with some PowerShell logic to grab a debug parameter from the command-line, or default to no “debugging” otherwise. As you will see, debugging is lightly implemented in my code samples, but was more heavily (and specifically) used in the production version. Customize or eliminate this completely, as needed.

#*** PARAMETERS ***
#pass a debug flag to toggle additional command-line output while testing the script
param (
[Int]$debug = 0
)
#******************
I defined some constant values near the top of my script so I can easily re-use the code should I need only to point it to a different environment.
#*** CONSTANTS ***
#Establish "test" and "prod" variables to use in conjunction with the debug parameter above
if($debug) {
# Define the source network location (UNC Path):
$directory = "\\network\path\test"
# Define the target SharePoint site collection:
$uri = "http://url/testsite/_vti_bin/lists.asmx?wsdl"
# Define the target SharePoint list in above site collection:
$listName = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}"
} else {
$directory = "\\network\path\prod"
$uri = "http://url/prodsite/_vti_bin/lists.asmx?wsdl"
$listName = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}"
}
#******************
I work with a team not entirely composed of scripters, so for the sake of clarity I always declare my global variables near the top of the script. Note, though, that PowerShell does not require this.
#*** GLOBALS ***
#(declared here for clarification)
$IDMigrated
$IDExisting
$totalPSProcessed
$totalFilesAttached
$totalMerged
#*** INCLUDES ***
Add-Type -AssemblyName System.Web
#*****************
Script Functions:
function ResetXML() {
# Resets the XML query parameters
$global:query = $null
$global:qxml = $null
$global:viewFields = $null
$global:vfxml = $null
}

function BlankQueryElement() {
# Builds the base XML query elements (default values are blank/null)
$global:xmlDoc = new-object System.Xml.XmlDocument
$global:viewName = "" # Default
$global:list = $null
$global:query = $global:xmlDoc.CreateElement("Query")
$global:viewFields = $global:xmlDoc.CreateElement("ViewFields")

# Define the query options:
# (presence required in the XML, but not used in this case)
$global:queryOptions = $global:xmlDoc.CreateElement("QueryOptions")
$global:rowLimit = "1"
}

function ExecuteQuery() {
# Queries the SharePoint list service and returns the matching list item ID
# (returns NULL if no match or connection error)
if($global:service -ne $null){
try{
$global:list = $global:service.GetListItems($global:listName, "", $global:query, $global:viewFields, $global:rowLimit, $queryOptions, "")
MyOutput ("Query success on ID " + $list.data.row | Select-Object -ExpandProperty ows_ID)
return $list.data.row | Select-Object -ExpandProperty ows_ID
}
catch{
$global:matchingTitle = $null
MyOutput ("Failed to query the SharePoint list: " + $global:query.OuterXml)
$tempStr = "Failed to query the SharePoint list: " + $global:query.OuterXml + " - " + $_
if($debug) {
Write-Error -Message $tempStr
Throw "Failed to query the SharePoint list." }
return $null
}
} else {
Throw "SharePoint web service connection ERROR!"
}
}

function AddAttachment($aID) {
#Adds a file attachment (global variable) to the given list item (ID passed to function)
$tempReturnValue
# Set the XML content:
$global:batchelement.innerxml = $global:xml

try {
$tempReturnValue = $global:service.AddAttachment($global:listName, $aID, $global:file.ToString(), $global:buffer)
MyOutput ("Attach success!`t" + $global:notesID + "`t" + $aID + "`t" + $global:attachFileName)
$global:totalFilesAttached++
}
catch {
MyOutput ("Failed to attach file:" + $file.ToString() + " " + $tempReturnValue)
MyOutput ($global:notesID + "`t" + $matchingID + "`t" + $global:matchingTitle + "`t" + $global:attachFileName + "`tFAILED")
write-error $_ -erroraction:'SilentlyContinue'
}

return $tempReturnValue
}

function UpdateListItemField($ID,$be) {
# This function will update the SharePoint list item with given ID using the passed XML batch element
# Call the web service:
try {
$global:service.updatelistitems($global:listName, $be)
MyOutput ("Success: updatelistitems on ID " + $ID + ": " + $be.InnerXml)
}
catch {
MyOutput ("Failed: updatelistitems on ID " + $ID + " with error: " + $ndRetUpdate.ChildNodes[0].InnerText)
write-error $_ -erroraction:'SilentlyContinue'
}
}

function MyOutput($outStr) {
#A customized output function that can change logging behavior  based on the debug parameter
If($debug) {
Write-Host $outStr
} else {
Write-Host $outStr
Write-Output $outStr | Out-File -FilePath:"MyOutputLog.txt" -Append:$true -Force:$true
}
}
Body of Script
$totalFilesAttached = 0
MyOutput " "
MyOutput "****** NEW SCRIPT ITERATION ******"
if($debug) {MyOutput "*** DEBUG MODE ***"}

#*** CONNECT WITH SHAREPOINT WEB SERVICES ***
# (uses credentials of current user)
$service = $null
try{
$service = New-WebServiceProxy -Uri $uri  -Namespace SpWs  -UseDefaultCredential
MyOutput "Connected to SharePoint web service!"
}
catch{
MyOutput "Failed to connect to SharePoint web service!"
Exit
}

#*** ENUMERATE THE FOLDERS ***
# Get all subfolder names from source location:
$foldernames = Get-ChildItem -Path $directory | Select-Object -ExpandProperty Name
If(!$foldernames) {
MyOutput "ERROR: UNC path not available!"
Exit
}
Else {
MyOutput ("Total number of folders to process: " + $foldernames.Count)
}

If($debug) {
MyOutput "List of Domino IDs:"
ForEach($dominoID in $foldernames) {
MyOutput $dominoID
}
}

$dominoID = ""
ForEach($dominoID in $foldernames) {
MyOutput ("Processing notesID: " + $dominoID)

# Reset XML parameters:
ResetXML
# Generate new XML query element:
BlankQueryElement

# Customize the query:
# Look for a match on "MigrateID", and "PSProcessed" equal to FALSE
# (PSProcessed set to true if row already processed by this script)
$qxml = "<Where><And><Eq><FieldRef Name='MigrateID' /><Value Type='Text'>" + $dominoID + "</Value></Eq><Eq><FieldRef Name='PSProcessed' /><Value Type='Integer'>0</Value></Eq></And></Where>"
$query.set_InnerXml($qxml)

# Define the item field(s) to retrieve:
$vfxml = ""
$viewFields.set_InnerXml($vfxml)

# Execute the query and get the matching list ID:
$matchingID = ExecuteQuery
If($matchingID) {
$matchingTitle = $list.data.row | Select-Object -ExpandProperty ows_Title
$matchingSubtitle = $list.data.row | Select-Object -ExpandProperty ows_Subtitle
$matchingAttachments = $list.data.row | Select-Object -ExpandProperty ows_Attachments

# Re-requery the list to find any existing item with the same CR ID:
ResetXML
BlankQueryElement

# Look for a match on "CR ID", and "PSProcessed" equal to FALSE
$ImportID = $ImportID.Substring(0,4)
$qxml = "<Where><And><And><Eq><FieldRef Name='ImportID' /><Value Type='Integer'>" + $ImportID + "</Value></Eq><IsNull>           <FieldRef Name='MigrateID' /></IsNull></And><Eq><FieldRef Name='PSProcessed' /><Value Type='Integer'>0</Value></Eq></And></Where>"
$query.set_InnerXml($qxml)

# Define the item field(s) to retrieve (all):
$vfxml = ""
$viewFields.set_InnerXml($vfxml)

# Execute the query and get the matching list ID:
$IDExisting = ExecuteQuery
}

If($IDExisting) {
$existingTitle = $list.data.row | Select-Object -ExpandProperty ows_Title
$existingSubtitle = $list.data.row | Select-Object -ExpandProperty ows_Subtitle
$existingAttachments = $list.data.row | Select-Object -ExpandProperty ows_Attachments
$existingMigrateID = $list.data.row | Select-Object -ExpandProperty ows_MigrateID
}

If($matchingID -and -not $matchingPSProc) {
MyOutput ("The matching migrated item ID is " + $matchingID + ' - ' + $matchingTitle)
if($IDExisting) {MyOutput ("The matching existing item ID is " + $IDExisting + ' - ' + $existingTitle)}

#Add the files from the matching subfolder as attachments on the list item:
$tempPath = $directory + "\" + $dominoID + "\"
$filenames = Get-ChildItem -Path $tempPath | Select-Object -ExpandProperty Name
Write-Host $filenames
$totalFiles = $filenames.Count

# Get name attribute values (guids) for list and view:
$ndlistview = $service.getlistandview($listname, "")
$strlistid = $ndlistview.childnodes.item(0).name
$strviewid = $ndlistview.childnodes.item(1).name

# Create an xmldocument object and construct a batch element and its attributes.
$xmldoc = new-object system.xml.xmldocument

# note that an empty viewname parameter causes the method to use the default view
$batchelement = $xmldoc.createelement("Batch")
$batchelement.setattribute("OnError", "Continue")
$batchelement.setattribute("ListVersion", "1")
$batchelement.setattribute("ViewName", $strviewid)

# Methods for batch call in CAML:
# To update or delete, specify the id of the existing item.
# To update or add, specify the target column and the target value.
$xml = ""

# Grab the target attachment file (in raw bytes):
ForEach($attachFileName in $filenames) {
$file = Get-Item ($tempPath + $attachFileName)
$stream = $file.OpenRead()
$buffer = New-Object byte[] $stream.length
[void]$stream.Read($buffer, 0, $stream.Length)

# *********************************************** #
# Attempt to attach file on migrated Domino item:
AddAttachment $matchingID
# *********************************************** #

if($IDExisting) {
# Attempt to attach files on original (existing) SharePoint item:
$ndRetAttach = AddAttachment $IDExisting
}
}
MyOutput ("  Results: " + $totalFilesAttached + " of " + $totalFiles)

if($IDExisting) {
#**************************************************************
# With attachments complete, now verify and update fields on
# existing item, as needed (only when blank):
#**************************************************************
if(!$existingSubtitle) {
$xml = "<Method ID='1' Cmd='Update'>" +
"<Field Name='ID'>$IDExisting</Field>" +
"<Field Name='Subtitle'>$matchingSubtitle</Field>" +
"</Method>"
$batchelement.innerxml = $xml
UpdateListItemField  $IDExisting $batchelement
}
if(!$existingMigrateID ) {
$xml = "<Method ID='1' Cmd='Update'>" +
"<Field Name='ID'>$IDExisting</Field>" +
"<Field Name='MigrateID'>$dominoID</Field>" +
"</Method>"
$batchelement.innerxml = $xml
UpdateListItemField  $IDExisting $batchelement
}
}

#**************************************************************
# Now that actions are complete, update PSProcessed column on:
# 1) matching migrated Notes item
# 2) matching existing SharePoint item (if it exists)
#**************************************************************
if($IDExisting) {
#Create batch element that sets migrated Notes item to Obsolete:
$xml = "<Method ID='1' Cmd='Update'>" +
"<Field Name='ID'>$matchingID</Field>" +
"<Field Name='PSProcessed'>1</Field>" +
"<Field Name='Status'>Archived</Field>" +
"<Field Name='History'>This item was migrated from Domino and merged with an existing SharePoint item.</Field>" +
"</Method>"

#And increment the merged document counter:
$totalMerged++
} else {
#Otherwise, the migrated Notes item remains at current status:
$xml = "<Method ID='1' Cmd='Update'>" +
"<Field Name='ID'>$matchingID</Field>" +
"<Field Name='PSProcessed'>1</Field>" +
"<Field Name='History'>This item was migrated from Domino.</Field>" +
"</Method>"
}
# Set the XML content:
$batchelement.innerxml = $xml
UpdateListItemField $matchingID $batchelement

#And increment the total PSProcessed counter:
$totalPSProcessed++

if($IDExisting) {
#If a matching item exists, update it too:
$xml = "<Method ID='1' Cmd='Update'>" +
"<Field Name='ID'>$IDExisting</Field>" +
"<Field Name='PSProcessed'>1</Field>" +
"</Method>"
# Set the XML content:
$batchelement.innerxml = $xml
# Reset the return code:
$ndRetUpdate = $null

UpdateListItemField $IDExisting $batchelement
}
}
ElseIf($matchingID -and $matchingPSProc) {
MyOutput "Matching item " + $matchingID + " already processed by script."
}
Else {
If($debug) {MyOutput "No matching item."}
}
}

#output final stats:
MyOutput ("Total list items processed by script: " + $totalPSProcessed)
MyOutput ("Total list items archived via merge process: " + $totalMerged)
MyOutput ("Total files attached to list items: " + $totalFilesAttached)
MyOutput ("Script processing complete.")