Home > Paul Mather, Work > Update #ProjectServer lookup tables using #PowerShell #PS2010 #SP2010 #MSProject

Update #ProjectServer lookup tables using #PowerShell #PS2010 #SP2010 #MSProject

Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

I recently wrote a PowerShell script that updates a Project Server lookup table with values from a text file. The script is available to download, I have uploaded it to the Microsoft Script Center below:

http://gallery.technet.microsoft.com/scriptcenter/Update-Server-Lookup-table-bb1ae14f

This post will detail what needs to be updated in the script, the text file and a walkthrough of using the script.

Firstly lets take a look at the text file containing the values that I want to add to my lookup table. Worthing noting is that it can either be an empty lookup table or a lookup table that already contains values. The values I am going to add can be see below:

image

This script does assume that the lookup table values are all level 1.

As mentioned there are variables / lines in the script that will need to be updated, these can be seen below:

  • $values = Get-Content "C:\Lookupvaluestoadd.txt"  – This line will need to be updated to reference the filename and path for the text file that contains the lookup table values.
  • $lookupTablename = "Test Lookup Table" – This variable will need to be updated to reference the lookup table you wish to update
  • $svcPSProxy = New-WebServiceProxy -uri "http://vm353/pwa/_vti_bin/PSI/LookupTable.asmx?wsdl" – This line will need to be updated to reference your Project Server PWA URL, replace http://vm353/pwa with your PWA URL

The following methods will need to be updated to reference to correct local ID, the script currently uses 1033:

  • ReadLookupTables
  • ReadLookupTablesbyUids (used twice)
  • UpdateLookupTables

Now that we have the lookup table values ready in the text file and the lookup table exists, in this example the lookup table is call “Test Lookup Table”, we are ready to execute the script. Also make sure the lookup table is checked in.

As you can see below, currently the “Test Lookup Table” is empty:

image

After closing the lookup table (the lookup table needs to be checked in), we will execute the PowerShell script to insert the values from the text file. Open Windows PowerShell with a Project Server administrator account and navigate to the location of the script, in this case it is on the desktop:

image

Press enter to execute:

image

The script states that everything was successful, now see the lookup table:

image

I have created some other PowerShell scripts working with lookup tables, I will upload these in the next week or so.

As always, this script is provided as is with no warranties etc. use at your own risk and test on a test environment before using on a production environment.

Advertisements
Categories: Paul Mather, Work Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: