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

August 29, 2012 at 10:30 am | Posted in Administration, Configuration, Customisation, Functionality, PowerShell, PSI | 4 Comments
Tags: , , , ,

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

****Update – this also works for Project Server 2013****

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

4 Comments »

RSS feed for comments on this post. TrackBack URI

  1. Very good content Paul. Thanks sharing.

  2. […] This article has been cross posted from pwmather.wordpress.com (original article) […]

  3. Wow this is quite usefull thanks Paul.

  4. […] found a very useful PowerShell script on the blog of Paul Mather (the code is available in the TechNet Script Center as well). This script utilizes the PSI […]


Leave a Reply

Fill in your details below or click an icon to log in:

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

Create a free website or blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: