I needed to import some ‘support’ time that was being tracked from outside of project server. We had reports that combined the two but that seemed messy. I don’t really program though so I needed to work it out in powershell. I believe I have all the major functionality done. Still to be determined are all the issues around data overwrites, timing etc but here is a short walkthough of connecting to Project Server 2007 PSI with powershell, querying users, querying timesheets, updating a line on a timesheet and queuing an update.
Lots of help from the SDK
I also referenced some of the chrisfie, code from codeplex. It was in c# but it gave some good direction.
Still very rough, maybe I will post more as I polish it out. As I spent a good part of my day today on this, hopefully it will help somebody else.
On to the powershell:
#set up some env variables. this is largely to find wsdl and csc, maybe other libraries, i stole it from the web.
$env:VSINSTALLDIR="$env:ProgramFiles\Microsoft Visual Studio 9.0"
$env:VCINSTALLDIR="$env:ProgramFiles\Microsoft Visual Studio $obj\VC"
$env:FrameworkDir=$(split-path $FrameworkPath -Parent)
$env:FrameworkVersion=$(split-path $FrameworkPath -Leaf)
$env:PATH="$env:VSINSTALLDIR\Common7\IDE;$env:VCINSTALLDIR\BIN;$env:VSINSTALLDIR\Common7\Tools;$env:VSINSTALLDIR\Common7\Tools\bin;$env:VCINSTALLDIR\PlatformSDK\bin;$env:FrameworkSDKDir\bin;$env:FrameworkDir\$env:FrameworkVersion;$env:VCINSTALLDIR\VCPackages;C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin;$env:PATH"
#I tried to start w/ the connect-webservice from the orielly cookbook but it was giving me errors and this just worked.
# connect to the Proj Serv Interface (PSI) and create a timesheet object to manipulate timesheets
csc /t:library TimeSheet.cs
$objTS = New-Object TimeSheet
$objTS.UseDefaultCredentials = $true
# connect to the Proj Serv Interface (PSI) and create a resource object to manipulate resources
csc /t:library Resource.cs
$objRes = New-Object Resource
$objRes.UseDefaultCredentials = $true
#we will need a date mathable datetime for calc in a bit so setup a date
$today = date
#get a list of all active users
$lstRes = $objRes.ReadUserList("Active")
#you could loop through the resource list but I will set it to just me for troubleshooting.
# the full list of users can obtained with: $lstRes = $objRes.ReadUserList("Active")
#public TimesheetListDataSet ReadTimesheetList (Guid resUID,DateTime startDate,DateTime finishDate,int select))
# the “int select” is from this table, values are added together if you need multiples.
# Acceptable Value=4. Select timesheets with Acceptable status.
# AllExisting Value=31. Select all timesheets.
# AllPeriods Value=32. Select all timesheets plus an empty record for each period with no timesheet.
# Approved Value=8. Select timesheets with Approved status.
# CreatedByMe Value=64. Select timesheets you created.
# InProgress Value=1. Select timesheets with InProgress status.
# Rejected Value=16. Select timesheets with Rejected status.
# Submitted Value=2. Select timesheets with Submitted status.
$lstTS = $objTS.ReadTimesheetList($objRes.GetCurrentUserUid(), $today.addDays(-50), $today, 31)
#we now have all the timesheets in our date range.
#you can get a specific timesheet
$ts = $objTS.ReadTimeSheet($lstTS.Timesheets.Item(2).TS_UID)
#there are two main items here. Lines are the rows you see in your timesheet view. actuals are the items in the columns
#check your lines with
#get a specific line. NOTE: Do Not use $line variable here. That is a PS variable and it will be reset every time you hit tab.
$tsLine = $ts.Lines.Item(2)
#you will use this in a bit to get your line UID. this is what you attach your actual to.
#now check your actuals
#create a new actual to attach
$myActual = $ts.Actuals.NewActualsRow()
#note I am working on GMT and my server is on EST so it is likely the reason why the times are 5 hours off, i didn't bother to check into it too much
$myActual.TS_ACT_START_DATE = [datetime]"4/7/2009 5:00:00 AM"
$myActual.TS_ACT_FINISH_DATE = [datetime]"4/8/2009 4:59:59 AM"
$myActual.TS_ACT_VALUE = 90000 # this looks like a lot of time but for some reason PS stores data as 1000 units/min so 90,000 = 1.5 hours.
$myActual.TS_LINE_UID = $tsLine.TS_LINE_UID
#add my actual to the actuals list
#add my ts to the update queue
$objTS.QueueUpdateTimesheet([Guid]::NewGuid(), $ts.Headers.Item(0).TS_UID, $ts)
#check your timesheet, man, it's updated.