Automating the Management of Checked-out Projects in Project Server

If you’re a Project Server administrator, you’ve probably had to deal with Projects that seem to remain checked out for a long time.  Sometimes this could be the result of a check-in failure and sometimes Project Manager forgetfulness, but whatever the cause, as an admin you have to periodically force check-in those Projects for good housekeeping.  Checking the checked-out Projects is not on my list of favourite to-do’s, so I decided to write a Powershell script to automate the task.

This script will check the Project Server draft database for any Projects that are currently checked-out.  If the checkout date is greater than the number of days past that we define (default 7), then we email the person who has the Project checked out and ask them to check it back in again.  If the checkout date is greater than the second defined number of days (default 28), then we force the check-in of the Project and advise the user who had it checked out that we have done so.  Then we simply run the script once a week using a Windows Scheduled Task, and our housekeeping is now automated 🙂

Download

Download the script from the Technet Gallery.

Permissions

The account that runs the script needs to have read-access to the Project Server draft database, and also needs to be able to check in Projects.  The PSI Web Services are used to check Projects in.

Variables

There are some variables at the top of the script that should be set for your environment:

$smtpserver: The name of your smtp server for email sending
$From: The email address you want to send from
$admin: The email address of the admin who receives bcc copies of each email sent
$datasource: The SQL server and instance name
$database: The name of your Project Server Draft database
$ProjectServerURL: the URL of your PWA instance

# Mail server info
$smtpserver = "mysmtpserver"
$From = "ProjectServer@contoso.com"
$admin = "Iam.admin@contose.com"

# Database info
$dataSource = “sqlserver\instance”
$database = “PS_2010_PWA_DRAFT_90_DB”

# Project Server PWA URL
$ProjectServerURL = "http://project/PWA"

Parameters

The script has two optional parameters:

-DaysUntilEmailUser

This is the number of days the Project needs to be checked out for before we send an email to the person who has it checked out, asking the to check it in again.  The default is 7.

-DaysUntilForceCheckin

This is the number of days the Project needs to be checked out for before we force the check-in. The default is 28.

Verbose output is also supported which returns each action being taken on the Project plans to the console.

.\ Handle-CheckedoutProjects.ps1 -DaysUntilEmailUser 5 -DaysUntilForceCheckin 10 -verbose

The script has a help file for reference.

Scheduled Task

To automate this process, simply create a scheduled task in Windows, using the action ‘Start a Program’:

Program: Powershell.exe
Arguments: -ExecutionPolicy Bypass -File “<path to script>\Handle-CheckedoutProjects.ps1”

Example Emails