Sync SharePoint Managed metadata field with database table using Powershell

HenriM 13:20 , , , No comments

Or: How to add terms to a termset from Powershell :)

In this case we are collecting project numbers from a SQL Server table, the Job table in a Navision instance to be exact.

When we have got the records all fine and happy we connect to SharePoint and asks it gently if it could please let us in to the Managed Metadata Service.
Once inside we rush to the term group Projects, demands it to show us it's term set Project numbers and then convinces it to accept all new numbers or else:

<#
-------------------------------------------------------------
Developer: Henri Merkesdal
Script to create projectnumbers in Termstore/SharePoint from Navision
-to add a label i.e description etc
$term = $termSet.CreateTerm("United States",1033)
$term.SetDescription(“This is my description”, 1033)
$term.CreateLabel("USA”, 1033, $false)
- See more at: http://blogs.c5insight.com/Home/entryid/435/Creating-Managed-Metadata-Terms-in-SharePoint-with-PowerShell
-------------------------------------------------------------
v1.0 2015.06.04 - HM:
- Init
#>
function Main{
InitNavision
AddToTermStore $rs
}
function InitNavision() {
# Database
$server = "sqlserver1\instancename"
$db = "NAV_THE_DB"
# Create connection and query
$connectionString = "Provider=sqloledb; Data Source=$server; Initial Catalog=$db; User Id=SpNavReader; Password=thepassword;"
$sqlNav="SELECT No_ FROM [Client`$Job]"
#$sqlNav="SELECT No_,Description,[Global Dimension 1 Code] FROM [Client`$Job]"
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$cmdNav = New-Object System.Data.OleDb.OleDbCommand $sqlNav, $connection
# Open connection and fetch data
$connection.Open()
$adapter= New-Object System.Data.OleDb.OleDbDataAdapter $cmdNav
$ds = New-Object System.Data.DataSet
[void] $adapter.Fill($ds)
$connection.Close()
# Return records to script variable rs
$script:rs = $ds.Tables | Select-Object -Expand Rows
}
function AddToTermStore ($projects)
{
Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue
#Connect to the Metadata Service
$taxSite = get-SPSite "http://intranet/sites/projects"
$taxonomySession = Get-SPTaxonomySession -site $taxSite
$termStore = $taxonomySession.TermStores["Managed Metadata Service"]
$termgroup =$termStore.Groups["Projects"]
$termset = $termGroup.Termsets["Project number"]
$terms=$termset.Terms
$change=$false
foreach($p in $projects) {
$nr=$p.No_
if($terms[$nr] -ne $null)
{
#"Existing project $nr"
}
else {
#New project
$termset.CreateTerm($nr,1033)
$change=$true
}
}
if($change) {
$termStore.CommitAll()
}
$taxSite.Dispose()
}
Main
view raw sync_navision_jobs_to_sp_mmd.ps1 hosted with ❤ by GitHub


The term set Project number and Term group Projects already have lived a while in the Term Store. If your Term Store are missing out and wants a visit you could introduce them the usual way: Intro to Term store GUI

After getting on equal terms with Projects we settle down, release the site back to SharePoint with a polite Dispose() and retreat happily into the sun again.

As we both feel this was important we arrange a regular appointment using Scheduled Tasks  and promise to call.

Hope it helps,
Henri

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )

Contact Form

Name

Email *

Message *