Automate Microsoft Project Integration Tasks from PowerShell

The CData Cmdlets for Microsoft Project are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Microsoft Project.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Microsoft Project, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete Microsoft Project data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for MS Project. To access Microsoft Project data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for MS Project.

Once you have acquired the necessary connection properties, accessing Microsoft Project data in PowerShell can be enabled in three steps.

The User and Password properties, under the Authentication section, must be set to valid Microsoft Project user credentials. In addition, specify a URL to a valid Microsoft Project server organization root or Microsoft Project services file.

PowerShell

1. Install the module:

   Install-Module MicrosoftProjectCmdlets

2. Connect:

   $microsoftproject = Connect-MicrosoftProject -User "$User" -Password "$Password" -URL "$URL"
   

3. Search for and retrieve data:

   $projectname = "Tax Checker"
   $projects = Select-MicrosoftProject -Connection $microsoftproject -Table "Projects" -Where "ProjectName = `'$ProjectName`'"
   $projects
   

   You can also use the Invoke-MicrosoftProject cmdlet to execute SQL commands:

   $projects = Invoke-MicrosoftProject -Connection $microsoftproject -Query 'SELECT * FROM Projects WHERE ProjectName = @ProjectName' -Params @{'@ProjectName'='Tax Checker'}
   

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for MS Project\lib\System.Data.CData.MicrosoftProject.dll")
    

2. Connect to Microsoft Project:

    
   $conn= New-Object System.Data.CData.MicrosoftProject.MicrosoftProjectConnection("User=myuseraccount;Password=mypassword;URL=http://myserver/myOrgRoot;")
   $conn.Open()
   

3. Instantiate the MicrosoftProjectDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT ProjectName, ProjectActualCost from Projects"
   
   $da= New-Object System.Data.CData.MicrosoftProject.MicrosoftProjectDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.projectname $_.projectactualcost
   }
    

Update Microsoft Project Data

PowerShell

Update-MicrosoftProject -Connection $MicrosoftProject -Columns @('ProjectName','ProjectActualCost') -Values @('MyProjectName', 'MyProjectActualCost') -Table Projects -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.MicrosoftProject.MicrosoftProjectCommand("UPDATE Projects SET ProjectName='Tax Checker' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.MicrosoftProject.MicrosoftProjectParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Microsoft Project Data

PowerShell

Add-MicrosoftProject -Connection $MicrosoftProject -Table Projects -Columns @("ProjectName", "ProjectActualCost") -Values @("MyProjectName", "MyProjectActualCost") 

ADO.NET

$cmd = New-Object System.Data.CData.MicrosoftProject.MicrosoftProjectCommand("INSERT INTO Projects (ProjectName) VALUES (@myProjectName)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.MicrosoftProject.MicrosoftProjectParameter("@myProjectName","Tax Checker")))
$cmd.ExecuteNonQuery()

Delete Microsoft Project Data

PowerShell

Remove-MicrosoftProject -Connection $MicrosoftProject -Table "Projects" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.MicrosoftProject.MicrosoftProjectCommand("DELETE FROM Projects WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.MicrosoftProject.MicrosoftProjectParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()