Deleting Orphaned SharePoint Databases

When you delete a Service Application, but not the data, databases are left behind. Should you want to clean up references to these databases later, here's how.

Use the Get-SPDatabase cmdlet to list all of the databases the Farm knows about. The output of this command is verbose, so pipe it to Format-List and select a subset of the properties.

Get-SPDatabase | FT Name,Exists

Notice how the IsAttachedToFarm and ExistsInFarm properties don't report true or false for most of the service applications?

It's not a problem! You can use the Exists property (on each database) to filter that list to just databases that SharePoint "thinks" don't exist.

$dbs = Get-SPDatabase            
$dbs | ?{$_.Exists -eq $false} | %{Write-Host "DB"$_.Name"does not exist." -f red}

Or

$dbs | %{Write-Host "Database"$_.Name;if($_.Exists){Write-Host "DB Exists." -f Green}else{Write-Host "DB does not exist." -f red}}

To remove these database references, call the Delete() method, and then the Unprovision() method, on each database.

For Example:

$dbs = Get-SPDatabase            
$dbs | ?{$_.Exists -eq $false}  | %{Write-Host "DB"$_.Name"does not exist. Deleting and cleaning up references." -f red; $_.Delete();$_.Unprovision()}

Creating a Report of Broken Web Pages in SharePoint

Recently I presented a session at SharePoint Saturday Melbourne on Being Crafty with PowerShell. One of the examples was how to audit all the aspx pages in a large farm. I want to share the first part of that script here now.

The example came from a large migration project I was working on. I needed to give the client confidence site pages where working ok, before business users started testing. The challenge was:

1. How to check a large farm with tens of thousands of pages
2. How to determine if the page is ok?

I addressed the issue using a PowerShell script that did the following for each page in the farm:

1. Hit the page, and get the HTTP response code
2. If the response code was 200, then download the page as HTML text, and check the contents for an occurences of "Correlation Id"
3. Log the results in a custom PSObject that could be saved and analysed later

I'll add the full script in a follow up post. In this post, I just want to demonstrate how to achieve these objectives for a single page.

Step 1. Create a custom PSObject to store the results.

For each page that I hit, I'm going to store the results into one of these objects. Each object will then be added to a collection (or an array). The object will record the URL's, page name, HTTP response code and if the page contains "Correlation Id".

$PageInfo = New-Object PSObject            
$PageInfo | Add-Member -MemberType NoteProperty -Name "WebUrl" -value ""            
$PageInfo | Add-Member -MemberType NoteProperty -Name "PageUrl" -value ""            
$PageInfo | Add-Member -MemberType NoteProperty -Name "Response" -value ""            
$PageInfo | Add-Member -MemberType NoteProperty -Name "ResponseCode" -value ""
$PageInfo | Add-Member -MemberType NoteProperty -Name "MatchFound" -value ""

Step 2. Get the response code for the page.

#url to check            
$urlToCheck = "http://corp.bi.local/Beers/Coopers"            
            
# Create a credential object that can be used to authenticate against SharePoint            
$credentials = Get-Credential -UserName $env:USERNAME -Message "Enter credentails for SharePoint"            
            
# Use the System.Net.WebRequest class to create a web request            
$wrq = [System.Net.WebRequest]::Create("http://corp.bi.local/Beers/Coopers");

# Set the Credentials property of the WebRequest object            
$wrq.Credentials = $credentials;            
            
# The GetResponse method returns a System.Net.WebResponse object, which has property called Status code.            
$wrp = $wrq.GetResponse()
            
# One thing to note, is that if the request fails (e.g. with HTTP 401), an exception is raised. So you need to capture the exception, and then use the Response property of the Exception (System.Net.WebException) to get the status code            
try {            
    #GetResponse() returns a System.Net.WebResponse object            
 $wrp = $wrq.GetResponse()            
} catch [System.Net.WebException] {            
    #If an exception was thrown, you can get the System.Net.WebResponse object from the Exceptions Response property            
 $wrp = $_.Exception.Response            
}           
            
#Get the status code            
$wrp.StatusCode            
            
# The StatusCode is an enum, and can be cast to an Int to get the HTTP response number            
[int]$wrp.StatusCode            
            

Step 3. If the page returned an HTTP 200 response, download the pages contents as text (HTML) and check it for the presence "Correlation Id"

if(([int]$wrp.StatusCode) -eq 200)            
{            
    # The System.Net.WebClient class has a nice function called DownloadString(). We'll call it, passing in the same url, then check to see if the string returned contains "Correlation Id"            
    $wc = New-Object System.Net.WebClient;            
            
    # Reuse the credential object we created earlier            
    $wc.Credentials = $credentials;            
            
    # Store the downloaded string into a variable            
    $content = $wc.DownloadString($urlToCheck);             
            
    # To find if the page has an error, parse the html content looking for the text "Correlation Id"            
    $content.Contains("Correlation Id");            
            
    # Finally, dispose the WebClient object            
    $wc.Dispose();            
}            

Step 4. Put all the information into the custom PSObject we created, and add it to an array.

# Create a instance of the object            
$pi = $PageInfo | Select-Object *            
            
# Set the properties of the custom object (like you set properties on any other object)            
$pi.WebUrl = $Web.Url;            
$pi.PageUrl = $urlToCheck;            
$pi.Response = $wrp.StatusCode            
$pi.ResponseCode = [int]$wrp.StatusCode            
$pi.MatchFound = $content.Contains("Correlation Id");            
            
#Print the contents            
$pi            
            
#Create an array to store the object(s) in            
$results = @();            
            
#Add the current custom object to the array.             
$results += $pi;

Recursively Disabling a SharePoint Feature throughout a Farm

I'm working on a large SharePoint migration project at the moment. It's required quite a bit of PowerShell to automate tasks that ensure the project is a success.

One of those tasks was to find everywhere a feature was activated through the farm, and optionally disable the feature. Whenever we disable a feature throughout the farm, we need to keep a report of all the places the feature was previously enabled (there's about ten thousand webs in this farm).

So I wrote a script, that does exactly that! The script takes a feature-id, an SPWeb and two switches as parameters, and returns an object collection that records details about the feature instances disabled (feature id and display name, the web url, the feature status and the time it was last activated).

The two parameters, -Recurse, and -Report, control the scripts function. The -Recurse parameter tells the script to check all of the input webs sub-webs. The -ReportOnly parameter tells the script to... well, create a report of everywhere the feature is currently active, without actually disabling it!

Here's the function:

function Outst-SPFeature            
{            
 [CmdletBinding()]                        
    Param(                          
            [parameter(Mandatory=$true,Position = 0,valueFromPipeline=$true)][Microsoft.SharePoint.SPWeb]$Web,            
            [parameter(Mandatory=$true)][string]$FeatureId,            
            [parameter(Mandatory=$false)][switch]$Recurse,            
   [parameter(Mandatory=$false)][switch]$ReportOnly            
        )            
 #Define an object that can store the features properties             
 $FeatureInfo = New-Object psobject            
 $FeatureInfo | Add-Member -MemberType NoteProperty -Name "Id" -value ""
 $FeatureInfo | Add-Member -MemberType NoteProperty -Name "DisplayName" -value ""
 $FeatureInfo | Add-Member -MemberType NoteProperty -Name "WebUrl" -value ""
 $FeatureInfo | Add-Member -MemberType NoteProperty -Name "Status" -value ""
 $FeatureInfo | Add-Member -MemberType NoteProperty -Name "TimeActivated" -value ""             
 #create an empty array variable            
 $matches = @();            
             
 Write-Host "Checking web"$Web.Url -f Green -b Yellow;#pipe the list of features ($web.Features) to the Where-Object cmdlet (aliased as '?'), and look for a feature with the same FeatureId that was passed to the script as a parameter            
 $f = $Web.Features | ?{$_.DefinitionId -like $FeatureId}            
    #If the feature was found, $f won't be null            
 if($f -ne $null)            
 {            
        #If the feature was found, check the SPFeature.Definition.Status property, to see if the feature is activated (online)            
  if($f.Definition.Status -eq "Online")            
  {            
   Write-Host "Disabling feature,"$f.Definition.DisplayName -f Green            
            #If the feature is online, then record the features details in a new custom object (this will get returned at the end of the function)            
   $fm = $FeatureInfo | Select-Object *;            
   $fm.Id = $f.Definition.Id;            
   $fm.DisplayName = $f.Definition.DisplayName;            
   $fm.WebUrl = $Web.Url;            
   $fm.Status = $f.Definition.Status;            
   $fm.TimeActivated = $f.TimeActivated            
   #Add the custom object to the $matches array (a list that will contain a custom object for each instance of a feature found)            
   $matches += $fm;            
    #Check the -ReportOnly switch. If the switch wasn't passed to the function, it will be false.             
   if($ReportOnly -eq $false)            
   {            
                #Disable the feature            
    Disable-SPFeature -Identity $f.Definition.Id -Url $Web.Url -Confirm:$false
   }               
  }            
 }            
  #Check the -Recurse switch. If the switch wasn't passed to the function, it will be false. Also check if the web has any sub-webs. If both of these checks are true, then check the sub-webs            
 if($Recurse -eq $true -and $Web.Webs.Count -gt 0)            
 {            
  Write-Host "Checking sub webs" -f Blue            
  foreach($sw in $Web.Webs)            
  {   #Here we want to call the same function again, against the sub-webs. The $matches variable is incremented with the results returned from the new call to the function.             
   if($ReportOnly -eq $false)            
   {            
    $matches += Outst-SPFeature -Web $sw -FeatureId $FeatureId -Recurse            
   }            
   else            
   {            
    $matches += Outst-SPFeature -Web $sw -FeatureId $FeatureId -Recurse -ReportOnly            
   }                 
  }              
 }            
 #The $matches array (containing the list that contains custom objects for each instance of a feature found) is returned.            
 return $matches;            
}            

And this is how it can be called:

#Run this command to disable the feature on all webs in all webapplications.            
$disabledfeature = Get-SPWebApplication | Get-SPSite -Limit All | Get-SPweb -Limit All |Foreach {Outst-SPFeature $_ -FeatureId "a5557f3e-102c-401e-9eae-1e7fcf4340d0" -Recurse}

And this is how you export the results to an xml file that can reloaded at a later time:

#Run this command to disable the feature on all webs in all webapplications, and save a report of all the feature instances that were disabled to C:\Temp\DisabledFeatureReport.xml
Get-SPWebApplication | Get-SPSite -Limit All | Get-SPweb -Limit All |Foreach {Outst-SPFeature $_ -FeatureId "a5557f3e-102c-401e-9eae-1e7fcf4340d0" -Recurse} | Export-Clixml -Path C:\Temp\DisabledFeatureReport.xml

Renaming a HNSC (Host Named Site Collection) in a SharePoint Farm with Multiple Web Applications

Recently I've been involved in migrating a large SharePoint farm from SharePoint 2010 to SharePoint 2013. The farm has multiple web applications, and HSNC's have been created in different web applications. Part of the migration has required us to rename a number of HNSC's (Host Named Site Collections) for the development, testing and staging environments.

I'll start by saying, Microsoft recommends using one web application (SPWebApplication) per farm if you are planning on using HNSC (host named site collections). But that doesn't stop people using HSNC's in a farm with multiple SharePoint web applications.

So the short of this is, if you have a SharePoint farm with multiple web applications, and you want to rename a HNSC within one of those web applications, you have to add a new URL (using New-SPSiteUrl) to an "unused" zone, and then manually add the IIS bindings into SharePoint Web Applications IIS site, for each server in the farm that accepts requests for the site collection. To see some PowerShell on how to do this, skip to the bottom of the article!

If you google or bing "rename SPSIte", you'll get a number of articles and posts about renaming both "path based" and "host named" site collections. There are a few PowerShell ways for achieving this, including Rename-SPSite, and SPSite.Rename(newurl).

In our case, none of these approaches worked. Renaming "path based" site collections works fine, and renaming "host named" site collections also works fine... as long as you have configured your farm in the supported way for HNSC's (with one web application). The environment we were upgrading had many web applications, and HNSC's in more than one of those web applications.

So after a bit of investigation, we (and by we, I mean a colleague of mine, Elaine Van Bergen) pointed out that IIS has to know which IIS site it should send the request to(there is always a single IIS site behind each SharePoint web application). Each IIS site has bindings (for domain names) that tell IIS which urls the site will serve requests for.

After checking the IIS bindings on the IIS site behind one of the SharePoint web applications where we had added a new URL for a HNSC, I found there was no binding there for the new URL (domain) I'd added to the HNSC.

If that doesn't already make sense, hopefully the following example explains it clearer.

For the example, we have an IIS site listening on port 80, with a binding for blackburn.trainstation.com. This IIS site will respond to any requests sent to the address blackburn.trainstation.com/* (e.g. http://blackburn.trainstation.com/default.aspx). We also have another IIS site with a binding for hawthorn.trainstation.com. Both of these IIS Sites have SharePoint web applications behind them, and each web application has a single SharePoint site collection. The site collections are host named site collections.

In this scenario, we have a SharePoint web application for the blackburn.trainstation.com, with an IIS site behind it listening on port 80, with the binding blackburn.trainstation.com. Web requests for blackburn.trainstation.com/* will be accepted by this IIS site, which will pass it onto SharePoint to look up the site, and respond to the request.



If a new URL, "gleniris.trainstation.com", is added to the "intranet" zone of the blackburn.trainstation.com web application, what would happen? When the request is received by the web server, IIS looks for an "IIS" site that has a binding for gleniris.trainistation.com. In our example there are no sites with this binding, but there is a site with a binding for "*" (anything), so IIS will send the request to that "IIS" site (Default Website). Because the Default IIS site doesn't actually have any sort of site behind it, an HTTP 404 is returned.



Once a binding for gleniris.trainstation.com is added to the blackburn.trainstation.com IIS site, requests for gleniris.trainstation.com will now be responded to by the IIS site with the binding. If this is an IIS site with a SharePoint web application behind it, the request is then handed off to SharePoint to fulfil.



So, getting back to the original problem, how do we rename a HNSC on a SharePoint farm with multiple web applications (without doing a backup-spsite, delete-spsite, restore-spsite)? It's not ideal. The way we found was to use Add-SPSiteURL cmdlet to add the new URL to a new zone (one other than the default zone). After doing this, we needed to manually add an IIS binding for the SharePoint web application's IIS website, on all the SharePoint WFE's (web servers), to tell the IIS site to respond to requests for the new URL.

Building on our example, the commands for this looks like this:

#Add a new URL for the HNSC http://blackburn.trainstation.com            
$site = Get-SPSite http://blackburn.trainstation.com            
Add-SPSiteUrl $site -URL http://gleniris.trainstation.com -Zone Intranet            
            
#Add the IIS binding, for gleniris.trainstation.com on port 80 (remember, this will need to be performed on each WFE)            
Import-Module "WebAdministration"            
New-WebBinding -Name $site.WebApplication.Name -Protocol http -Port 80 -IPAddress "*" -HostHeader gleniris.trainstation.com

It's not pretty, but it is a workaround if you find yourself in this position.

As I said at the start, this is probably one reason why Microsoft recommends using HNSC with one web application. If you are using one web application, you can configure it with a binding of "*". Doing this ensures the IIS site responds to all requests, and hands them off to SharePoint to satisfy. SharePoint contains the list of HNSC's and takes care of responding to valid requests.

Using PowerShell, CSOM, and some Hokey Pokey to copy files between two different SharePoint farms

Recently I needed to migrate a large number of images from a SharePoint 2010 farm, to a new SharePoint 2013 farm. I had local server access to the SharePoint 2013 farm servers, but only browser based access to the SharePoint 2010 farm.

PowerShell remoting wasn't an option, so I couple together a couple methods to come up with a solution. Use CSOM (via PoweShell) to get a list of the files (images) on the remote SharePoint library. Then use the WebClient class to download the file, and finally, the standard server based object model to add the downloaded image to the destination SharePoint farm libary.

What the hell, I hear you say?! But why not just use CSOM all the way?! Because, well, actually, I have no good reason. Probably because I was in a hurry, and this was the fastest way I could achieve my objective in the short amount of time I had! And sometimes, a quick solution to a problem is more valuable than "technically perfect" approach! They're my excuses anyway.

So, here's the PowerShell code I used to do it. It's commented, and if nothing else, it demonstrates that there is usually more than one way to approach a problem!! Oh yeah, it also demonstrates using PowerShell and SharePoint CSOM together!

function Import-FilesFromRemoteLibraryUsingCSOM{            
    [CmdletBinding()]                        
    Param(                           
            [parameter(Mandatory=$true)][string]$SourceWebApplicationUrl,            
            [parameter(Mandatory=$true)][string]$SourceWebUrl,            
            [parameter(Mandatory=$true)][string]$SourceLibrary,            
      [parameter(Mandatory=$true)][string]$DestinationWebUrl,            
            [parameter(Mandatory=$true)][string]$DestinationLibrary            
        )            
    #Load the Microsoft SharePoint Client Dll's            
    Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll";            
    Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll";             
    #Using the CSOM in PowerShell is very similar to using it in C#, with just a few minor differences.            
    #Get the Client Context, using the SourceWeb URL passed to the function. This is the web we will be getting the files from.             
    $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SourceWebUrl)            
    #Load the web            
    $w = $ctx.Web            
    $ctx.Load($w)            
    #Get and load the list that contains the files we want to migrate            
    $l = $w.Lists.GetByTitle($SourceLibrary)            
    $ctx.Load($l)            
    #Create a new query. This is where we could filter the list items (files) that we're going to migrate. In this example, we're just limiting the results to 1000 items.            
    $query = New-Object Microsoft.SharePoint.Client.CamlQuery            
    $query.ViewXml = "1000"            
    $items = $l.GetItems($query)            
    #Load the items            
    $ctx.Load($items)            
    #Execute the loaded queries.             
    $ctx.ExecuteQuery()            
    if($l -eq $null){Write-Host "List not found." -f Red;return;}            
    Write-Host "Attempting to import all library files." -f darkyellow;            
    #Create an array for holding a list of file URL's            
    $fileUrls = @();            
    #Get the destination web and list. Remember that this code is running on the destination SharePoint server, so we can just use the standard SharePoint cmdlet's            
    $dw = Get-SPWeb $DestinationWebUrl;            
    $dl = $dw.Lists[$DestinationLibrary];            
    #Create a WebClient object. We'll use this to download each file to a byte array, which we can upload into SharePoint.             
    #I chose to use this method, as it was quick, I was short on time, and I was having trouble getting a byte array using CSOM.            
    #It demonstrates an alternate approach to a problem!             
    $wc = New-Object System.Net.WebClient;             
    $credentials = [System.Net.CredentialCache]::DefaultCredentials;            
    $wc.Credentials = $credentials;            
    #For each item in the list of items retrieved from our source server (using CSOM), we create the URL, download the file as a byte array, and then upload it to the destination SharePoint server            
    foreach($i in $items){            
        #Load the item and file            
        $item = $l.GetItemById($i.Id);            
        $file = $item.File;            
        $ctx.Load($item);            
        $ctx.Load($file);            
        #Execute the query to get the items we loaded            
        $ctx.ExecuteQuery();            
        #Create the Url for the file            
        $itemUrl = ([String]::Format("{0}{1}",$SourceWebApplicationUrl,$item["FileRef"]));              
        $fileName = $itemUrl.Substring($itemUrl.LastIndexOf("/")+1);            
        Write-Host "Downloading file $itemUrl";            
        #Download the file as a byte array            
        $file = $wc.DownloadData($itemUrl);            
        #Upload the file to the destination SharePoint library            
        $nf = $dw.Files.Add(([String]::Format("{0}/{1}/{2}",$dw.Url,$dl.RootFolder.Url,$fileName)),$file,$true);            
        #If the destination list requires files to be checked in, then check it in!            
        if($nf.RequiresCheckout)            
        {               
         $nf.CheckIn("Checked in during migration","MajorCheckIn")             
        }              
        Write-host "Added file $fileName" -f Green;            
    }                
}

Importing Data Into A SharePoint List From An SQL Database

I often need to import data from various systems into SharePoint as part of the process of deploying a new SharePoint based solution that is intended to replace an existing (a.k.a aging) solution.

I find PowerShell is immensely useful for these types of tasks. PowerShell scripts are quick to write, incredibly flexible and very quick to test, troubleshoot, deploy, and re-deploy!

As an example, I thought I'd demonstrate how to import some data from a SQL database into a SharePoint list. The SQL query joins two lists, and returns a few hundred rows. The script iterates through the rows, adding a new SharePoint list item for each row.

It also performs some other actions as it iterates each row;

1. It adds the Category row column to the list items olCategory field (which is a Managed Metadata Field). As it does this, it checks the Termset for the Term, and if the Term doesn't already exist, it creates it.
2. It trims the rows Description field if it exceeds 255 characters.
3. It checks the rows HyperLink field starts with "http" before updating the list items URL field.
4. It uses Write-Progress to keep the "user" (me) informed about the progress of the import (see image below).

Example: Querying the SQL Server and iterating through the rows:

#Connect to the database
$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();    
    }            
}
Get-UsefulWebsitesFromSql;

The script looks like this when it's running: