Using SQL to supply metadata to SharePoint

Recently I had a requirement to publish over 1000 documents to a SharePoint library. These documents also needed to be tagged with the necessary metadata. Luckily for me, a SQL table existed that held all of the metadata along with a field that matched the filename. This made it easy to perform a lookup and return the needed metadata and update SharePoint.
The script runs in two parts. The first function uploads a list of files to a specified Document library. You can then specify any number of metadata via the foreach loop that have applied to each matching item.
In order to query sql you need to have the SQL module in one of your $ENV: PSModulePath locations.

If you do not already have this you can install the SQL-Module cmdlets here via my other post.

#Script
Add-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue
Import-Module sqlserver
#Uploads documents to sharepoint via URL, LibraryName and full path of file
function UploadToSharePoint($WebUrl,$DocLib,$FilePath) {
    $web = Get-SPWeb $weburl
    $List = $Web.GetFolder($DocLib)
    $files = $list.files
    $FileName = $FilePath.Substring($FilePath.LastIndexOf("\")+1) 
    $file = Get-Item $filepath
    $files.add($DocLib +"/" + $filename, $file.OpenRead(),$false) | Out-Null
    Write-host -ForegroundColor Blue "$Filename has been uploaded"
    $web.Dispose()
}#Function

#Function to apply metadata to each column .Must add line for each column to be updated $SPITEM must have where clause to make match to update appropriate field.
function ApplyMetadata($web,$doclib,$filename,$company,$date,$idnumber) {
    $web = Get-SPWeb $web
    $SPlist = $web.Lists["$DocLib"]
    $SPitem = $Splist.items | where {$_.Name -eq $FileName}
    $spitem["Company"] = "$Company"
    $spitem["Date"] = "$Date"
    $spitem["IDNumber"] = "$IDNUmber"
    #Add new item for each piece of metadata
    $SPItem.Update()
    Write-host -ForegroundColor Green "Metadata assigned to $filename"
    $web.Dispose()
} #function

#Create SQL connection and sql query
#$sqlConnection = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) "$sqlServer"   
$sqlServer = "SPSQL"
$sqlDatabase = "MetaData_DB"
$sqlQuery = "SELECT Filename, Date, Company, IDNumber FROM  MetaData_SampleView"
#Load in all files that you want to upload the SharePoint
$UploadFiles = Get-ChildItem C:\testfiles -Recurse -Include *.txt
#Find associated metadata
$metadata = @(Get-SqlData -sqlserver $sqlConnection -dbname $sqlDatabase -qry $sqlQuery -ea stop)
#Designate Site URL and Doc Library to upload to.
$SiteURL = "http://lab02"
$DocLibrary = "Shared Documents"

#Cycle through all files in the $UploadFiles and put then into the Sharepoint libary and apply metadata. All Metadata values need to be mapped within the loop
foreach ($f in $UploadFiles) {
   Write-host -ForegroundColor Blue "Processing $($f.Name)..."
   $Meta = $metadata | where {$_.filename -eq $f.name}
  
   UploadToSharePoint $SiteURL $Doclibrary $f.FullName
   ApplyMetadata $SiteURL $DocLibrary $f.Name $meta.Company $meta.Date $meta.IDNumber
}
Note: Another way to do this eaily is to use Access. You can create a complex update query using multiple ODBC source to update your SharePoint metadata. This can be a very effective way to mass update metadata in SharePoint. I have had companies who need to auto-tag content daily and they build Access macros that run on a scheduled task to complete the tagging.
{Kam}