Resources For IT Professionals
Post an article
Wikis - Page Details
  • First published by
  • When: 29 Jul 2013 9:41 AM
  • Last revision by (cMVP, Microsoft Community Contributo)
  • When: 18 Sep 2013 11:04 AM
  • Revisions: 8
  • Comments: 7
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  SharePoint 2010: Importing Data from a SQL Database into a SharePoint list

SharePoint 2010: Importing Data from a SQL Database into a SharePoint list

SharePoint 2010: Importing Data from a SQL Database into a SharePoint list

When developing solutions for the SharePoint platform, there's often a requirement to import data from a SQL database into one or more SharePoint lists. 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 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 a SQL Server (easy don't you think?):

$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: Update the list items taxonomy field (setting the value of a taxonmy 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: The full script (for querying a SQL database and inserting the rows from the query as new 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 occured.";       
    }
    finally{
        $conn.Close(); 
    }          
}
#Call the function
Get-UsefulWebsitesFromSql;


, , , , , [Edit tags]
Leave a Comment
  • Please add 4 and 2 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 18 Sep 2013 11:04 AM

    Richard Mueller edited Revision 6. Comment: Removed extra space in tag "SharePoint  2010", added tag

  • 30 Jul 2013 5:55 AM

    Gokan Ozcifci edited Revision 5. Comment: Tag & content & title

Page 1 of 1 (2 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Posted by
    on 29 Jul 2013 9:49 AM

    Matthew Yarlett edited Original. Comment: Minor change to formatting.

  • Posted by
    on 30 Jul 2013 5:11 AM

    Matthew Yarlett edited Revision 2. Comment: Updated the code examples. Added an additional example. Minor changes to wording.

  • Posted by
    on 30 Jul 2013 5:45 AM

    Matthew Yarlett edited Revision 3. Comment: Updated tags.

  • Posted by
    on 30 Jul 2013 5:55 AM

    Gokan Ozcifci edited Revision 5. Comment: Tag & content & title

  • Posted by
    on 31 Jul 2013 10:16 AM

    Hi Matthew,

    Thank you for posting the article!

    Do we still need BDC and Secure Store Service application setup for the scripts to work?

  • Posted by
    on 31 Jul 2013 10:42 AM

    Hi Dan,

    Thanks for the feedback!

    You don't need the Secure Store or BDC/BCS. The connection to the SQL server is direct from the PowerShell instance, so it uses the current users windows identity for authentication (and doesn't experience the "Double-Hop" authentication issue).

    You do need to run the script from a PowerShell console on a SharePoint server though.

  • Posted by
    on 18 Sep 2013 11:04 AM

    Richard Mueller edited Revision 6. Comment: Removed extra space in tag "SharePoint  2010", added tag

Page 1 of 1 (7 items)