SharePoint 2010: Import Data from SQL Server into a List using PowerShell
Introduction
There's often a requirement to import data from a SQL Server database into one or more SharePoint lists when developing solutions for the SharePoint platform. This can easily be accomplished using PowerShell. By using PowerShell you have a lot of control over what data gets imported, and how it is formatted. PowerShell scripts are also very quick and easy to manipulate and test, making the process of importing data quick and easy.
As an example, the script below connects to a database called UsefulWebsites on the Library SQL Server. It executes a select command that joins two tables together (links and linkscategory) and returns a list of useful websites as a DataTable. For each row in the DataTable, it creates a new list item in the Useful Websites SharePoint list. Progress information is written to the console window via Write-Progress ([[An Example of Using Write-Progress in a Long Running SharePoint PowerShell Script]]).
The script performs the following data manipulation on each row:
- Migrates the database column Category into a Managed Metadata Field (olCategory), creating the category as a new Term if it doesn't already exist.
- Checks the Hyperlink column of each row starts with http, and if not, adds it.
[Note]: This script needs to be run locally on a SharePoint server
Example: Querying SQL Server
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=Library; Initial Catalog=UsefulWebsites; Integrated Security=SSPI")
$conn.Open();
$query = "select lc.CategoryName,il.DisplayName,il.HyperLink,il.Description,il.Shared,lc.Shared as LCShared from incelinks il join linkscategory lc on il.CategoryId = lc.CategoryId where il.Shared = 1 and lc.shared =1 order by lc.CategoryName"
$dap = new-object System.Data.SqlClient.SqlDataAdapter($query,$conn);
$dt = new-object System.Data.DataTable;
$dap.Fill($dt);
foreach($r in $dt.Rows)
{
Write-Host $r["DisplayName"]
}
$conn.Close();
Example: Adding List Items from the Query that Include a Taxonomy Field
This example goes through setting the value of a taxonomy field using the CategoryName database column.
$w = Get-SPWeb "http://corporate/library";
$list = $w.Lists["Useful Websites"];
$categoryField = $list.Fields["olCategory"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyField];
$tsId = $categoryField.TermSetId;
$termStoreId = $categoryField.SspId;
$tsession = Get-SPTaxonomySession -Site $list.ParentWeb.Site;
$tstore = $tsession.TermStores[$termStoreId];
$tset = $tstore.GetTermSet($tsId);
foreach($r in $dt.Rows)
{
$i = $list.Items.Add();
$i["Title"] = $r["DisplayName"];
$category = $r["CategoryName"];
#Check if the term exists
$terms = $tset.GetTerms($category,$false);
$term = $null;
if($terms.Count -eq 0)
{
#Create the term
Write-Host ([String]::Format("Creating Term, {0}",$category)) -ForegroundColor DarkYellow;
$term = $tset.CreateTerm($category, $tstore.Languages[0]);
$tstore.CommitAll();
}
else
{
#The term exists. Note we are using the first instance of the Term
$term = $terms[0];
}
#Set the Managed Metadata field
$categoryField.SetFieldValue($i,$term);
$i.Update()
}
Example: Creating a Reusable Script
This example demonstrates creating a PowerShell script that queries a SQL Server database and inserts rows from the query as new list items in a SharePoint list.
function Get-UsefulWebsitesFromSql
{
#Connect to the database
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=Library; Initial Catalog=UsefulWebsites; Integrated Security=SSPI")
Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (1) -Status "Opening Connection to the SQL Server";
$conn.Open();
try
{
#Execute the query
Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (2) -Status "Querying SQL Server";
$query = "select lc.CategoryName,il.DisplayName,il.HyperLink,il.Description from links il join linkscategory lc on il.CategoryId = lc.CategoryId where il.Shared = 1 and lc.shared =1 order by lc.CategoryName"
$dap = new-object System.Data.SqlClient.SqlDataAdapter($query,$conn);
$dt = new-object System.Data.DataTable;
$dap.Fill($dt);
$w = Get-SPWeb http://corporate/library;
$list = $w.Lists["Useful Websites"];
$listTitle = $list.Title;
$pi = $dt.Rows.Count;
$pci =1;
$categoryField = $list.Fields["olCategory"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyField];
$tsId = $categoryField.TermSetId;
$termStoreId = $categoryField.SspId;
$tsession = Get-SPTaxonomySession -Site $list.ParentWeb.Site;
$tstore = $tsession.TermStores[$termStoreId];
$tset = $tstore.GetTermSet($tsId);
$itemsAdded = 0;
Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (25/($pi+45)*100) -Status "Importing ($pi) items into SharePoint.";
foreach($r in $dt.Rows)
{
Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (($pci+25)/($pi+45)*100) -Status "Importing ($pi) items into SharePoint.";
Write-Progress -Id 2 -ParentId 1 -Activity "Adding new items to $listTitle" -PercentComplete ($pci/$pi*100) -Status "Importing item $pci into SharePoint.";
$pci++;
$i = $list.Items.Add();
try
{
$itemsAdded++;
#Set the title
$i["Title"] = $r["DisplayName"];
#Set the Description Field. Trim the description if it's longer than 255 characters
$description = if(($r["Description"]).ToString().Length -gt 255){($r["Description"]).ToString().SubString(0,254)}else{($r["Description"]).ToString()};
$i["Description"] = $description;
#Set the URL field (Hyperlink field)
$hyperLink = $r["HyperLink"];
if(!$hyperLink.ToLower().StartsWith("http"))
{
$hyperLink = ([String]::Format("http://{0}",$hyperLink));
}
Write-Host $hyperLink;
$urlFieldValue = New-Object Microsoft.SharePoint.SPFieldUrlValue;
$urlFieldValue.Description = ($r["DisplayName"]).Replace("&","and");
$urlFieldValue.Url = $hyperLink;
$i["URL"] = $urlFieldValue;
#Set the category field (Managed Metadata)
$category = $r["CategoryName"];
$terms = $tset.GetTerms($category,$false);
$term = $null;
if($terms.Count -eq 0)
{
Write-Host ([String]::Format("Creating Term, {0}",$category)) -ForegroundColor DarkYellow;
$term = $tset.CreateTerm($category, $tstore.Languages[0]);
$tstore.CommitAll();
}
else
{
$term = $terms[0];
}
$categoryField.SetFieldValue($i,$term);
#Save changes to the item
$i.Update();
Write-Host ([String]::Format("Added item: '{0}', with URL: {1}",$r["DisplayName"],$hyperLink)) -ForegroundColor Green;
}
catch [System.Exception]{
Write-Host ([String]::Format(" Error adding item. Item {0} has been skipped. Error: {1}. ",$r["DisplayName"],$_)) -ForegroundColor Red -BackgroundColor White;
continue;
}
}
Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (80) -Status "Closing SQL Connection.";
Write-Host ([String]::Format("Finished importing items into the list. Imported {0} items. ",$itemsAdded)) -ForegroundColor Blue -BackgroundColor White;
Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (90) -Status "Finished importing ($pi) items into SharePoint.";
$w.Dispose();
}
catch [System.Exception]{
Write-Host ([String]::Format("Error: {0} ",$_)) -ForegroundColor Red -BackgroundColor White;
Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (100) -Status "An error occurred.";
}
finally{
$conn.Close();
}
}
#Call the function
Get-UsefulWebsitesFromSql;