Automate Microsoft Project Integration Tasks from PowerShell

Ready to get started?

Download for a free trial:

Download Now

Learn more:

MS Project ADO.NET Provider

Rapidly create and deploy powerful .NET applications that integrate with Microsoft Project data including Tasks, Issues, Projects, Deliverables, and more!



Are you looking for a quick and easy way to access Microsoft Project data from PowerShell? We show how to use the Cmdlets for Microsoft Project and the CData ADO.NET Provider for Microsoft Project to connect to Microsoft Project data and synchronize, automate, download, and more.

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.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the Microsoft Project API, 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 Microsoft Project. To access Microsoft Project data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Microsoft Project.

After obtaining the needed connection properties, accessing Microsoft Project data in PowerShell consists of three basic steps.

The User and Password properties, under the Authentication section, must be set to valid Microsoft Project user credentials. In addition, you will need to 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 Microsoft 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 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 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 System.Data.CData.MicrosoftProject.MicrosoftProjectParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()