Microsoft Project Connection

Microsoft Project Connection

This documentation is based on version 21.0.8383 of the connector.

Getting Started

Microsoft Project Version Support

Enabling bidirectional SQL access to Project Server, the connector models Microsoft Project's CSOM and OData APIs as a relational database. The Jitterbit Connector for Microsoft Project can connect to both the cloud and local versions of Project Server.

Establishing a Connection

Connecting to Microsoft Project

In order to connect to the Microsoft Project data source, you will first need to specify the below connection properties.

  • URL: (Required) Set this to a valid Microsoft Project Web App URL. For example:
    http://contoso.com/sites/pwa/
  • Schema: (Optional) There are two available schemas, "ODataV1" and "ODataV2". The "ODataV2" schema (default) can be used for full CRUD operations. However, for reporting purposes, the "ODataV1" schema is superior, since it provides more granular data and exposes the custom fields. See Data Model for more information about the available functionality in each Schema.
  • ProjectId: (Optional) If the "ODataV2" schema is specified, you can additionally set this property to a specific Project Id, in order to define the default project for all the operations that require one.

Authenticating to Project Online

There are several authentication methods available for connecting to Microsoft Project including the SharePoint Online Cookie Service to connect on SharePoint Online, OAuth and SSO. The supported SSO identity providers are: Azure Active Directory, OneLogin, and OKTA.

Authenticating with SharePoint Online Cookie Service

To connect to Microsoft Project on SharePoint Online using your login credentials, set the AuthScheme to SharePointOnlineCookies, the User and Password to your user credentials.

Authenticating with OAuth

To use the OAuth 2.0 authentication standard you should first create a custom OAuth app to obtain the OAuthClientId, OAuthClientSecret. In addition to those properties, set CallbackURL. See Using OAuth2 Authentication for more information.

You should set the AuthScheme to OAuth to enforce the OAuth Authentication scheme.

Authenticating with ADFS

Set the AuthScheme to ADFS. The following connection properties need to be set:

  • User: Set this to the ADFS user.
  • Password: Set this to ADFS password for the user.
  • SSOLoginURL: Set this to the login URL used by the SSO provider.

Below is an example connection string:

AuthScheme=ADFS;User=username;Password=password;SSOLoginURL='https://sts.company.com';

ADFS Integrated

The ADFS Integrated flow indicates you are connecting with the currently logged in Windows user credentials. To use the ADFS Integrated flow, simply do not specify the User and Password, but otherwise follow the same steps in the ADFS guide above.

Authenticating with Okta

Set the AuthScheme to Okta. The following connection properties are used to connect to Okta:

  • User: Set this to the Okta user.
  • Password: Set this to Okta password for the user.
  • SSOLoginURL: Set this to the login URL used by the SSO provider.

The following SSOProperties are needed to authenticate to Okta:

  • APIToken (optional): Set this to the API Token that the customer created from the Okta org. It should be used when authenticating a user via a trusted application or proxy that overrides OKTA client request context.
  • MFAType (optional): Set this only in case you have configured MFA flow. Currently we support only the follwoing types: OktaVerify,Email and SMS.
  • MFAPassCode (optional): Set this only in case you have configured MFA flow. If this is set to empty/invalid the driver will initially issue a MFA challenge which will trigger the platform to send you an one-time password on your device or email, based on the configured MFA type. You need to re-issue another connection where the retrieved one-time password value is passed to MFAPassCode connection property.

The following is an example connection string:

AuthScheme=Okta;SSOLoginURL='https://example.okta.com/home/appType/0bg4ivz6cJRZgCz5d6/46';User=oktaUserName;Password=oktaPassword;

Authenticating with OneLogin

Set the AuthScheme to OneLogin. The following connection properties are used to connect to OneLogin:

  • User: Set this to the OneLogin user.
  • Password: Set this to OneLogin password for the user.

The following SSOProperties are needed to authenticate to OneLogin:

  • OAuthClientId: Set to the OAuthClientId, which can be obtained by selecting Developers > API Credentials > Credential > ClientId.
  • OAuthClientSecret: Set to the OAuthClientSecret, which can be obtained by selecting Developers > API Credentials > Credential > ClientSecret.
  • Subdomain: Set to the subdomain of the OneLogin user accessing the SSO app. For example, if your OneLogin URL is splinkly.onelogin.com, enter splinkly as the subdomain value.
  • AppId: Set to the ID of the SSO app.
  • Region (optional): Set to the region your OneLogin account resides in. The OneLogin API operates in multiple regions and this property is used to find the correct domain. It can take one of the following values:
    • US (default)
    • EU

The following is an example connection string: The following connection string uses an API key to connect to OneLogin:

AuthScheme=OneLogin;User=OneLoginUserName;Password=OneLoginPassword;SSOProperties='OAuthClientID=3fc8394584f153ce3b7924d9cd4f686443a52b;OAuthClientSecret=ca9257fd5cc3277abb5818cea28c06fe9b3b285d73d06;Subdomain=OneLoginSubDomain;AppId=1433920';

Using OAuth2 Authentication

OAuth 2.0 requires the authenticating user to interact with Microsoft Project using the browser. The connector facilitates this in various ways as described below.

Custom Credentials

You can register an app to obtain the OAuthClientId and OAuthClientSecret.

When to Create a Custom OAuth App

You need to create a custom OAuth app in the desktop flow.

Custom Credentials

When to Use a Custom OAuth App

Desktop Authentication with a Custom OAuth App

Follow the steps below to authenticate with the credentials for a custom OAuth app. See Creating a Custom OAuth App for more information.

Get and Refresh the OAuth Access Token

After setting the following, you are ready to connect:

  • OAuthClientId: Set this to the client ID assigned when you registered your app.
  • OAuthClientSecret: Set this to the client secret assigned when you registered your app.
  • CallbackURL: Set this to the Callback defined when you registered your app.
  • URL: Set this to the URL of your service instance.
  • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:
  1. Extracts the access token from the callback URL and authenticates requests.
  2. Refreshes the access token when it expires.
  3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

  • OAuthClientId: Set this to the Client ID in your app settings.
  • OAuthClientSecret: Set this to the Client Secret in your app settings.
  • CallbackURL: Set this to the Callback defined when you registered your app.
  • URL: Set this to the URL of your service instance.

You can then call stored procedures* to complete the OAuth exchange:

  1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Callback you specified in your app settings.

    The stored procedure* returns the URL to the OAuth endpoint.

  2. Open the URL, log in, and authorize the application. You are redirected back to the callback URL.
  3. Call the GetOAuthAccessToken stored procedure. Set the Verifier input to the "code" parameter in the query string of the callback URL.

Refresh the OAuth Access Token

You can set InitiateOAuth to REFRESH to automatically refresh the OAuth access token when it expires, or you can call the RefreshOAuthAccessToken stored procedure* to refresh the token manually.

Automatic Refresh

To refresh the token with InitiateOAuth, set the following on the first data connection:

  • OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
  • InitiateOAuth: Set this to REFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
  • OAuthSettingsLocation: Set this to the path where the connector will save the OAuth values, to be persisted across connections.
  • OAuthClientId: Set this to the Client ID in your app settings.
  • OAuthClientSecret: Set this to the Client Secret in your app settings.
  • OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
  • URL: Set this to the URL of your service instance.

On subsequent data connections, set the following:

  • InitiateOAuth
  • OAuthSettingsLocation
  • URL: Set this to the URL of your service instance.

Manual Refresh

You can use the RefreshOAuthAccessToken stored procedure* to manually refresh the OAuthAccessToken. Call the stored procedure* after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed. You need the following connection properties to be set:

  • OAuthClientId: Set this to the Client ID in your app settings.
  • OAuthClientSecret: Set this to the Client Secret in your app settings.

You can then call RefreshOAuthAccessToken with the following parameter set:

Headless Machines

Using OAuth on a Headless Machine

To establish a connection to the Microsoft Project data source on headless servers or other machines on which the connector cannot open a browser, you need to authenticate from another machine. Authentication is a two-step process.

  1. Instead of installing the connector on another machine, you can follow the steps below to obtain the OAuthVerifier value. Or, you can install the connector on another machine and transfer the OAuth authentication values, after you authenticate through the usual browser-based flow.
  2. You can then configure the connector to automatically refresh the access token from the headless machine.
You can follow the headless OAuth authentication flow using the OAuth credentials for your custom OAuth app.

Using the Credentials for a Custom OAuth App

Create a Custom OAuth App

See Creating a Custom OAuth App for a procedure. You can then follow the procedures below to authenticate and connect to data.

Obtain a Verifier Code

Set the following properties on the headless machine:

  • InitiateOAuth = OFF.
  • OAuthClientId = Client Id.
  • OAuthClientSecret = Client Secret.
  • URL = The URL of your site (https://{site}.sharepoint.com/sites/pwa).

You can then follow the steps below to authenticate from another machine and obtain the OAuthVerifier connection property.

  1. Call the GetOAuthAuthorizationURL stored procedure* with the CallbackURL input parameter set to the exact Redirect URI you specified in your app settings.
  2. Open the returned URL in a browser. Log in and grant permissions to the connector. You are then redirected to the callback URL, which contains the verifier code.
  3. Save the value of the verifier code. You will set this in the OAuthVerifier connection property.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

  • OAuthClientId = Client Id.
  • OAuthClientSecret = Client Secret.
  • URL = The URL of your site (https://{site}.sharepoint.com/sites/pwa).
  • OAuthVerifier: Set this to the verifier code.
  • OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified file.
  • InitiateOAuth: Set this to REFRESH.

Connect to Data

After the OAuth settings file is generated, set the following properties to connect to data:

  • OAuthSettingsLocation: Set this to the file containing the encrypted OAuth authentication values. Make sure this file gives read and write permissions to the provider to enable the automatic refreshing of the access token.
  • InitiateOAuth: Set this to REFRESH.

Transfer OAuth Settings

Follow the steps below to install the connector on another machine, authenticate, and then transfer the resulting OAuth values.

On a second machine, install the connector and connect with the following properties set:

  • OAuthSettingsLocation: Set this to a writable text file.
  • InitiateOAuth: Set this to GETANDREFRESH.
  • OAuthClientId: Set this to the Client ID in your app settings.
  • OAuthClientSecret: Set this to the Client Secret in your app settings.
  • CallbackURL: Set this to the Callback URL in your app settings.
  • URL = The URL of your site (https://{site}.sharepoint.com/sites/pwa).

Test the connection to authenticate. The resulting authentication values are written, encrypted, to the path specified by OAuthSettingsLocation. Once you have successfully tested the connection, copy the OAuth settings file to your headless machine. On the headless machine, set the following connection properties to connect to data:

  • InitiateOAuth: Set this to REFRESH.
  • OAuthSettingsLocation: Set this to the path to your OAuth settings file. Make sure this file gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Creating a Custom OAuth App

Authenticate using Custom OAuth Credentials

You can create an OAuth application through your site's UI, or by manually entering your site's create app link into your browser: https://{site}.sharepoint.com/sites/{Project_Web_App_Site}/_layouts/15/appregnew.aspx.

After filling in the required fields, you may click "Create" and the following credentials will be displayed.

Note that while creating the OAuth application, you will be required to specify a Callback URL.

This is the URL you will be redirected to after explicitly granting access. Microsoft Project requires this callback to use 'https://'.

You can work around this restriction by supplying 'https://oauth.cdata.com/oauth/' as callback, and adding an extra state connection property which will contain the actual redirect callback. Copy the ClientID and ClientSecret which will be needed later.
In order to access the microsoft project api we have to give scope permissions to the created app. In order to do that go to the following URL: https://{site}.sharepoint.com/sites/{Project_Web_App_Site}/_layouts/15/AppInv.aspx.
In the App ID field enter the ClientID obtained when creating the app and click on Lookup. The other fields should automatically be filled with the apps details like Title, App Domain and Redirect URL. In the permissions request XML enter the following:

<AppPermissionRequests>
    <AppPermissionRequest Scope="http://sharepoint/projectserver" Right="Manage"/>
    <AppPermissionRequest Scope="http://sharepoint/projectserver/projects" Right="Write"/>
    <AppPermissionRequest Scope="http://sharepoint/projectserver/enterpriseresources" Right="Write"/>
    <AppPermissionRequest Scope="http://sharepoint/projectserver/reporting" Right="Read"/>
</AppPermissionRequests>

These are the default permissions needed to have full access to the api. Also the logged in user should make sure they have the appropriate permissions to access the api. In case your Project Web App (PWA) is in Project permission mode, you can explicitly grant access to the Web Services for specified PWA users. To do this, you can follow the steps below.
  1. Open the 'PWA Settings' page on your PWA.
  2. Click on 'Manage Users' under the security section.
  3. Click on the 'User Name' of the interested user.
  4. On the 'Edit User' page expand the Global Permissions section.
  5. Make sure the 'Allow' checkbox of the 'Access Project Server Reporting Service' option under the 'General' section is checked.

In case your PWA is in SharePoint permission mode, you will need to add the interested user in a group with at least the 'Access Project Server Reporting Service' permission enabled, such as: Portfolio Viewers, Portfolio Managers, and Administrators. To do this, you can follow the steps below.

  1. Open the 'PWA Settings' page on your PWA.
  2. Click on 'Manage Users' under the security section.
  3. Click on the 'User Name' of the interested user.
  4. On the 'Edit User' and under the 'Security Groups' select one of the groups with the required access permissions (such as: Portfolio Viewers, Portfolio Managers, and Administrators), and click on the '>' button to add the current user.

You can now use these credentials to connect to Microsoft Project by setting them as the following connection properties:

  • InitiateOAuth = GETANDREFRESH.
  • OAuthClientId = Client Id.
  • OAuthClientSecret = Client Secret.
  • CallbackURL = Your callback URL.
  • URL = The URL of your site (https://{site}.sharepoint.com/sites/{Project_Web_App_Site}).

Alternatively, to use the callback:

You can now use these credentials to connect to Microsoft Project by setting them as the following connection properties:
  • InitiateOAuth = GETANDREFRESH.
  • OAuthClientId = Client Id.
  • OAuthClientSecret = Client Secret.
  • CallbackURL = https://oauth.cdata.com/oauth/.
  • State = Your actual callback URL (for example http://localhost:3333).
  • URL = The URL of your site (https://{site}.sharepoint.com/sites/{Project_Web_App_Site}).

CSOM Deprecation

There have been some changes made to the connector in the 2020 edition that should be taken into consideration.

CSOM Support Discontinued

Prior to the 2020 edition of the connector, connections to Microsoft Project using both the CSOM and OData interface were supported, which lead to difficulties in maintaining the two schemas concurrently.

By removing support for the CSOM, connections to Microsoft Project are streamlined due to the lack of special connection properties made specifically for each schema. Also, the OData interface can now perform all of the same CRUD operations that the CSOM is capable of. Lastly, the CSOM is poorly documented and more error-prone than OData.

For these reasons, the decision was made to deprecate support for the CSOM in favor of exclusively supporting the OData interface.

Improved Performance with OData Bulk Operations

One additional advantage of using the Microsoft Project OData interface exclusively is the support for efficient bulk operations.

Microsoft Project data must be checked out, an INSERT/UPDATE/DELETE operation is performed, the changes are published, and finally the changes are checked in.

At times, the checkout step may fail, leading to numerous requests per single INSERT/UPDATE/DELETE operation.

With the connector's batch support, you can dramatically reduce the numbers of requests submitted, since several records can be submitted through the checkin/checkout process at a time.

See Batch Processing for details on batch operations.

Advanced Features

This section details a selection of advanced features of the Microsoft Project connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

User Defined Views

The Jitterbit Connector for Microsoft Project allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:
  • Create a JSON-formatted configuration file defining the views you want.
  • DDL statements.

Defining Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:
  • Each root element defines the name of a view.
  • Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
	"MyView": {
		"query": "SELECT * FROM Projects WHERE MyColumn = 'value'"
	},
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
	}
}
Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Working with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:
SELECT * FROM Customers WHERE City = 'Raleigh';
An example of a query to the driver:
SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';
Resulting in the effective query to the source:
SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';
That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customizing the SSL Configuration

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model

Overview

The Jitterbit Connector for Microsoft Project models Microsoft Project objects as relational tables and views. A Microsoft Project object has relationships to other objects; in the tables, these relationships are expressed through foreign keys.

Connecting with the ODataV2 Schema

The ODataV2 Data Model (default) can be used for full CRUD operations. Main columns metadata is dynamically retrieved for each entity, but the schema is fixed and custom fields are not included.

Tables and Views describe a sample the tables available in the ODataV2 API.

Connecting with the ODataV1 Schema

The ODataV1 Data Model can only be used for reading data. Metadata is dynamically retrieved, including the custom fields for this schema.

Views describe a sample the views available in the ODataV1 API.

Key Features

  • The connector models Microsoft Project entities as relational tables and views, allowing you to write SQL to query Microsoft Project data.
  • Stored procedures* allow you to execute operations to Microsoft Project.
  • Live connectivity to these objects means any changes to your Microsoft Project account are immediately reflected when using the connector.

Collaborative Query Processing

The connector offloads as much of the SELECT statement processing as possible to Microsoft Project and then processes the rest of the query in memory. See SupportEnhancedSQL for more information on how the connector circumvents API limitations with in-memory client-side processing.

SELECT statements for the main tables are mostly supported server-side. For example:

SELECT * FROM Tasks WHERE Id = '61a6cc2d-cff5-e911-b093-00155ddc7224'
will be run server-side.

Note: When executing SELECT statements on child tables, everything is processed client side.

ODataV2 Data Model

The ODataV2 schema implements the ProjectServer service endpoints, which has support for full CRUD operations.

Tables

Tables are retrieved dynamically from the authenticated Microsoft Project account.

Views

Read-only Views are retrieved dynamically from the authenticated Microsoft Project account.

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Stored Procedures are function-like interfaces to Microsoft Project. Stored procedures* allow you to execute operations to Microsoft Project.

ODataV2 advantages compared to ODataV1

  • Supports write operations (INSERT, UPDATE and DELETE) on the exposed tables.

ODataV2 limitations

  • In order to query some tables and views, such as Assignments, ProjectResources, TaskLinks and Tasks,

    the ProjectId must be specified either in the query's WHERE clause criteria or via the ProjectId connection property.

  • The custom fields are not exposed.

Tables

The connector models the data in Microsoft Project into a list of tables that can be queried using standard SQL statements.

Generally, querying Microsoft Project tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Jitterbit Connector for Microsoft Project Tables

Name Description
Assignments Retrieve and modify assignments.
Calendars Retrieve and modify calendars.
EnterpriseResources Retrieve and modify resources.
Phases Retrieve and modify project phases.
ProjectResources Retrieve and modify project resources.
Projects Retrieve and modify projects.
Stages Retrieve and modify project stages.
TaskLinks Retrieve and modify task links.
Tasks Retrieve and modify tasks.
TimeSheetLines Retrieve and modify timesheets.
TimeSheets Retrieve and modify timesheets.

Assignments

Retrieve and modify assignments.

Table Specific Information

To query this table, the ProjectId must be specified either in the query's WHERE clause criteria or via the ProjectId connection property.

SELECT * FROM Assignments WHERE ProjectId = '99a38309-dc1d-eb11-bf8a-00155d9c6312'

Columns

Name Type ReadOnly References Required Description
Id [KEY] String True False
ActualCostWorkPerformed Double True False
ActualOvertimeCost Double True False
BaselineCost Double True False
BaselineCostPerUse Double True False
BaselineFinish Datetime True False
BaselineStart Datetime True False
BaselineWork String True False
BaselineWorkMilliseconds Int True False
BaselineWorkTimeSpan Time True False
BudgetedCostWorkPerformed Double True False
BudgetedCostWorkScheduled Double True False
CostVariance Double True False
CostVarianceAtCompletion Double True False
Created Datetime True False
CurrentCostVariance Double True False
Finish Datetime True False
FinishVariance String True False
FinishVarianceMilliseconds Int True False
FinishVarianceTimeSpan Time True False
IsConfirmed Bool True False
IsOverAllocated Bool True False
IsPublished Bool True False
IsResponsePending Bool True False
IsUpdateNeeded Bool True False
LevelingDelay String True False
LevelingDelayMilliseconds Int True False
LevelingDelayTimeSpan Time True False
Modified Datetime True False
Notes String True False
OvertimeCost Double True False
RemainingCost Double True False
RemainingOvertimeCost Double True False
Resume Datetime True False
ScheduleCostVariance Double True False
Start Datetime True False
StartVariance String True False
StartVarianceMilliseconds Int True False
StartVarianceTimeSpan Time True False
Stop Datetime True False
WorkContourType Int True False
WorkVariance String True False
WorkVarianceMilliseconds Int True False
WorkVarianceTimeSpan Time True False
LinkedCustomFields String False False
ActualCost Double False False
ActualFinish Datetime False False
ActualOvertimeWork String False False
ActualOvertimeWorkMilliseconds Int False False
ActualOvertimeWorkTimeSpan Time False False
ActualStart Datetime False False
ActualWork String False False
ActualWorkMilliseconds Int False False
ActualWorkTimeSpan Time False False
BudgetedCost Double False False
BudgetedWork String False False
BudgetedWorkMilliseconds Int False False
BudgetedWorkTimeSpan Time False False
Cost Double False False
CostRateTable Int False False
DefaultBookingType Int False False
Delay String False False
DelayMilliseconds Int False False
DelayTimeSpan Time False False
IsLockedByManager Bool False False
IsWorkResource Bool False False
OvertimeWork String False False
OvertimeWorkMilliseconds Int False False
OvertimeWorkTimeSpan Time False False
PercentWorkComplete Int False False
RegularWork String False False
RegularWorkMilliseconds Int False False
RegularWorkTimeSpan Time False False
RemainingOvertimeWork String False False
RemainingOvertimeWorkMilliseconds Int False False
RemainingOvertimeWorkTimeSpan Time False False
RemainingWork String False False
RemainingWorkMilliseconds Int False False
RemainingWorkTimeSpan Time False False
ResourceCapacity Double False False
ResourceId String False False
TaskId String False False
Work String False False
WorkMilliseconds Int False False
WorkTimeSpan Time False False
LinkedOwner String False False
LinkedParent String False False
LinkedResource String False False
LinkedTask String False False
ProjectId [KEY] String True False

Id of the parent.

Calendars

Retrieve and modify calendars.

Columns

Name Type ReadOnly References Required Description
Id [KEY] String False False
Created Datetime False False
IsStandardCalendar Bool False False
Modified Datetime False False
Name String False False
OriginalId String False False
LinkedBaseCalendarExceptions String False False

EnterpriseResources

Retrieve and modify resources.

Columns

Name Type ReadOnly References Required Description
Id [KEY] String False False
CanLevel Bool False False
Code String False False
CostAccrual Int False False
CostCenter String False False
Created Datetime False False
DefaultBookingType Int False False
Email String False False
ExternalId String False False
Group String False False
HireDate Datetime False False
HyperlinkName String False False
HyperlinkUrl String False False
Initials String False False
IsActive Bool False False
IsBudget Bool False False
IsCheckedOut Bool False False
IsGeneric Bool False False
IsTeam Bool False False
MaterialLabel String False False
Modified Datetime False False
Name String False False
Phonetics String False False
RequiresEngagements Bool False False
ResourceIdentifier Int False False
ResourceType Int False False
TerminationDate Datetime False False
LinkedAssignments String False False
LinkedBaseCalendar String False False
LinkedCostRateTables String False False
LinkedCustomFields String False False
LinkedDefaultAssignmentOwner String False False
LinkedEngagements String False False
LinkedResourceCalendarExceptions String False False
LinkedSelf String False False
LinkedTimesheetManager String False False
LinkedUser String False False
LinkedUserPermissions String False False

Phases

Retrieve and modify project phases.

Columns

Name Type ReadOnly References Required Description
Id [KEY] String False False
Description String False False
Name String False False
LinkedStages String False False

ProjectResources

Retrieve and modify project resources.

Table Specific Information

To query this table, the ProjectId must be specified either in the query's WHERE clause criteria or via the ProjectId connection property.

SELECT * FROM ProjectResources WHERE ProjectId = '99a38309-dc1d-eb11-bf8a-00155d9c6312'

Columns

Name Type ReadOnly References Required Description
Id [KEY] String True False
ActualCost Double True False
ActualCostWorkPerformed String True False
ActualCostWorkPerformedMilliseconds Int True False
ActualCostWorkPerformedTimeSpan Time True False
ActualOvertimeCost Double True False
ActualOvertimeWork String True False
ActualOvertimeWorkMilliseconds Int True False
ActualOvertimeWorkTimeSpan Time True False
ActualWork String True False
ActualWorkMilliseconds Int True False
ActualWorkTimeSpan Time True False
AvailableFrom Datetime True False
AvailableTo Datetime True False
BaselineCost Double True False
BaselineWork String True False
BaselineWorkMilliseconds Int True False
BaselineWorkTimeSpan Time True False
BudetCostWorkPerformed Double True False
BudgetedCost Double True False
BudgetedCostWorkScheduled Double True False
BudgetedWork String True False
BudgetedWorkMilliseconds Int True False
BudgetedWorkTimeSpan Time True False
Cost Double True False
CostVariance Double True False
CostVarianceAtCompletion Double True False
Created Datetime True False
CurrentCostVariance Double True False
Finish Datetime True False
IsBudgeted Bool True False
IsGenericResource Bool True False
IsOverAllocated Bool True False
Modified Datetime True False
Notes String True False
OvertimeCost Double True False
OvertimeWork String True False
OvertimeWorkMilliseconds Int True False
OvertimeWorkTimeSpan Time True False
PeakWork String True False
PeakWorkMilliseconds Int True False
PeakWorkTimeSpan Time True False
PercentWorkComplete Int True False
RegularWork String True False
RegularWorkMilliseconds Int True False
RegularWorkTimeSpan Time True False
RemainingCost Double True False
RemainingOvertimeCost Double True False
RemainingOvertimeWork String True False
RemainingOvertimeWorkMilliseconds Int True False
RemainingOvertimeWorkTimeSpan Time True False
RemainingWork String True False
RemainingWorkMilliseconds Int True False
RemainingWorkTimeSpan Time True False
ScheduleCostVariance Double True False
Start Datetime True False
Work String True False
WorkMilliseconds Int True False
WorkTimeSpan Time True False
WorkVariance String True False
WorkVarianceMilliseconds Int True False
WorkVarianceTimeSpan Time True False
LinkedCustomFields String False False
LinkedEnterpriseResource String False False
CanLevel Bool False False
Code String False False
CostAccrual Int False False
CostCenter String False False
CostPerUse Double False False
DefaultBookingType Int False False
Email String False False
Group String False False
Initials String False False
MaterialLabel String False False
MaximumCapacity Double False False
Name String False False
OvertimeRate Double False False
OvertimeRateUnits Int False False
Phonetics String False False
StandardRate Double False False
StandardRateUnits Int False False
LinkedAssignments String False False
LinkedDefaultAssignmentOwner String False False
ProjectId [KEY] String True False

Id of the parent.

Projects

Retrieve and modify projects.

Columns

Name Type ReadOnly References Required Description
Id [KEY] String True False
ApprovedEnd Datetime True False
ApprovedStart Datetime True False
CalculateActualCosts Bool True False
CalculatesActualCosts Bool True False
CheckedOutDate Datetime True False
CheckOutDescription String True False
CheckOutId String True False
CreatedDate Datetime True False
CriticalSlackLimit Int True False
DefaultFinishTime Datetime True False
DefaultOvertimeRateUnits Int True False
DefaultStandardRateUnits Int True False
DefaultStartTime Datetime True False
HasMppPendingImport Bool True False
HonorConstraints Bool True False
IsCheckedOut Bool True False
LastPublishedDate Datetime True False
LastSavedDate Datetime True False
ListId String True False
MoveActualIfLater Bool True False
MoveActualToStatus Bool True False
MoveRemainingIfEarlier Bool True False
MoveRemainingToStatus Bool True False
MultipleCriticalPaths Bool True False
OptimizerDecision Int True False
PercentComplete Int True False
PlannerDecision Int True False
ProjectSiteUrl String True False
ProjectType Int True False
SplitInProgress Bool True False
SpreadActualCostsToStatus Bool True False
SpreadPercentCompleteToStatus Bool True False
SummaryTaskId String True False
TaskListId String True False
WebId String True False
LinkedCheckedOutBy String False False
LinkedCustomFields String False False
LinkedEngagements String False False
LinkedEnterpriseProjectType String False False
LinkedPhase String False False
LinkedProjectSummaryTask String False False
LinkedQueueJobs String False False
LinkedStage String False False
LinkedUserPermissions String False False
CurrencyCode String False False
CurrencyDigits Int False False
CurrencyPosition Int False False
CurrencySymbol String False False
CurrentDate Datetime False False
DaysPerMonth Int False False
DefaultEffortDriven Bool False False
DefaultEstimatedDuration Bool False False
DefaultFixedCostAccrual Int False False
DefaultOvertimeRate Double False False
DefaultStandardRate Double False False
DefaultTaskType Int False False
DefaultWorkFormat Int False False
Description String False False
FinishDate Datetime False False
FiscalYearStartMonth Int False False
MinutesPerDay Int False False
MinutesPerWeek Int False False
Name String False False
NewTasksAreManual Bool False False
NumberFiscalYearFromStart Bool False False
OwnerId String False False
ProjectIdentifier String False False
ProtectedActualsSynch Bool False False
ScheduledFromStart Bool False False
ShowEstimatedDurations Bool False False
StartDate Datetime False False
StatusDate Datetime False False
TrackingMode Int False False
UtilizationDate Datetime False False
UtilizationType Int False False
WeekStartDay Int False False
WinprojVersion Decimal False False
LinkedAssignments String False False
LinkedCalendar String False False
LinkedIncludeCustomFields String False False
LinkedOwner String False False
LinkedProjectResources String False False
LinkedTaskLinks String False False
LinkedTasks String False False

Stages

Retrieve and modify project stages.

Columns

Name Type ReadOnly References Required Description
Id [KEY] String False False
Behavior Int False False
CheckInRequired Bool False False
Description String False False
Name String False False
SubmitDescription String False False
LinkedCustomFields String False False
LinkedPhase String False False
LinkedProjectDetailPages String False False
LinkedWorkflowStatusPage String False False

TaskLinks

Tasks

Retrieve and modify tasks.

Table Specific Information

To query this table, the ProjectId must be specified either in the query's WHERE clause criteria or via the ProjectId connection property.

SELECT * FROM Tasks WHERE ProjectId = '99a38309-dc1d-eb11-bf8a-00155d9c6312'

Columns

Name Type ReadOnly References Required Description
Id [KEY] String True False
ActualCostWorkPerformed Double True False
ActualDuration String True False
ActualDurationMilliseconds Int True False
ActualDurationTimeSpan Time True False
ActualOvertimeCost Double True False
ActualOvertimeWork String True False
ActualOvertimeWorkMilliseconds Int True False
ActualOvertimeWorkTimeSpan Time True False
BaselineCost Double True False
BaselineDuration String True False
BaselineDurationMilliseconds Int True False
BaselineDurationTimeSpan Time True False
BaselineFinish Datetime True False
BaselineStart Datetime True False
BaselineWork String True False
BaselineWorkMilliseconds Int True False
BaselineWorkTimeSpan Time True False
BudgetCost Double True False
BudgetedCostWorkPerformed Double True False
BudgetedCostWorkScheduled Double True False
Contact String True False
CostPerformanceIndex Double True False
CostVariance Double True False
CostVarianceAtCompletion Double True False
CostVariancePercentage Int True False
Created Datetime True False
CurrentCostVariance Double True False
DurationVariance String True False
DurationVarianceMilliseconds Int True False
DurationVarianceTimeSpan Time True False
EarliestFinish Datetime True False
EarliestStart Datetime True False
EstimateAtCompletion Double True False
ExternalProjectUid String True False
ExternalTaskUid String True False
FinishSlack String True False
FinishSlackMilliseconds Int True False
FinishSlackTimeSpan Time True False
FinishVariance String True False
FinishVarianceMilliseconds Int True False
FinishVarianceTimeSpan Time True False
FixedCostAccrual Int True False
FreeSlack String True False
FreeSlackMilliseconds Int True False
FreeSlackTimeSpan Time True False
IgnoreResourceCalendar Bool True False
IsCritical Bool True False
IsDurationEstimate Bool True False
IsExternalTask Bool True False
IsOverAllocated Bool True False
IsRecurring Bool True False
IsRecurringSummary Bool True False
IsRolledUp Bool True False
IsSubProject Bool True False
IsSubProjectReadOnly Bool True False
IsSubProjectScheduledFromFinish Bool True False
IsSummary Bool True False
LatestFinish Datetime True False
LatestStart Datetime True False
LevelingDelay String True False
LevelingDelayMilliseconds Int True False
LevelingDelayTimeSpan Time True False
Modified Datetime True False
Notes String True False
OutlinePosition String True False
OvertimeCost Double True False
OvertimeWork String True False
OvertimeWorkMilliseconds Int True False
OvertimeWorkTimeSpan Time True False
PercentWorkComplete Int True False
PreLevelingFinish Datetime True False
PreLevelingStart Datetime True False
RegularWork String True False
RegularWorkMilliseconds Int True False
RegularWorkTimeSpan Time True False
RemainingCost Double True False
RemainingOvertimeCost Double True False
RemainingOvertimeWork String True False
RemainingOvertimeWorkMilliseconds Int True False
RemainingOvertimeWorkTimeSpan Time True False
RemainingWork String True False
RemainingWorkMilliseconds Int True False
RemainingWorkTimeSpan Time True False
Resume Datetime True False
ScheduleCostVariance Double True False
ScheduledDuration String True False
ScheduledDurationMilliseconds Int True False
ScheduledDurationTimeSpan Time True False
ScheduledFinish Datetime True False
ScheduledStart Datetime True False
SchedulePerformanceIndex Double True False
ScheduleVariancePercentage Int True False
StartSlack String True False
StartSlackMilliseconds Int True False
StartSlackTimeSpan Time True False
StartVariance String True False
StartVarianceMilliseconds Int True False
StartVarianceTimeSpan Time True False
Stop Datetime True False
ToCompletePerformanceIndex Double True False
TotalSlack String True False
TotalSlackMilliseconds Int True False
TotalSlackTimeSpan Time True False
WorkBreakdownStructure String True False
WorkVariance String True False
WorkVarianceMilliseconds Int True False
WorkVarianceTimeSpan Time True False
LinkedCustomFields String False False
LinkedSubProject String False False
ActualCost Double False False
ActualFinish Datetime False False
ActualStart Datetime False False
ActualWork String False False
ActualWorkMilliseconds Int False False
ActualWorkTimeSpan Time False False
AddAfterId String False False
BudgetWork String False False
BudgetWorkMilliseconds Int False False
BudgetWorkTimeSpan Time False False
Completion Datetime False False
ConstraintStartEnd Datetime False False
ConstraintType Int False False
Cost Double False False
Deadline Datetime False False
Duration String False False
DurationMilliseconds Int False False
DurationTimeSpan Time False False
Finish Datetime False False
FinishText String False False
FixedCost Double False False
IsActive Bool False False
IsEffortDriven Bool False False
IsLockedByManager Bool False False
IsManual Bool False False
IsMarked Bool False False
IsMilestone Bool False False
LevelingAdjustsAssignments Bool False False
LevelingCanSplit Bool False False
Name String False False
OutlineLevel Int False False
ParentId String False False
PercentComplete Int False False
PercentPhysicalWorkComplete Int False False
Priority Int False False
RemainingDuration String False False
RemainingDurationMilliseconds Int False False
RemainingDurationTimeSpan Time False False
Start Datetime False False
StartText String False False
TaskType Int False False
UsePercentPhysicalWorkComplete Bool False False
Work String False False
WorkMilliseconds Int False False
WorkTimeSpan Time False False
LinkedAssignments String False False
LinkedCalendar String False False
LinkedParent String False False
LinkedPredecessors String False False
LinkedStatusManager String False False
LinkedSuccessors String False False
ProjectId [KEY] String True False

Id of the parent.

TimeSheetLines

Retrieve and modify timesheets.

Columns

Name Type ReadOnly References Required Description
Id [KEY] String False False
Comment String False False
LineClass Int False False
ProjectName String False False
Status Int False False
TaskName String False False
TotalWork String False False
TotalWorkMilliseconds Int False False
TotalWorkTimeSpan Time False False
ValidationType Int False False
LinkedAssignment String False False
LinkedTimeSheet String False False
LinkedWork String False False
PeriodId [KEY] String True False

Id of the parent.

TimeSheets

Retrieve and modify timesheets.

Columns

Name Type ReadOnly References Required Description
PeriodId [KEY] String False False
Comments String False False
EntryMode Int False False
IsControlledByOwner Bool False False
IsProcessed Bool False False
Name String False False
Status Int False False
TotalActualWork String False False
TotalActualWorkMilliseconds Int False False
TotalActualWorkTimeSpan Time False False
TotalNonBillableOvertimeWork String False False
TotalNonBillableOvertimeWorkMilliseconds Int False False
TotalNonBillableOvertimeWorkTimeSpan Time False False
TotalNonBillableWork String False False
TotalNonBillableWorkMilliseconds Int False False
TotalNonBillableWorkTimeSpan Time False False
TotalOvertimeWork String False False
TotalOvertimeWorkMilliseconds Int False False
TotalOvertimeWorkTimeSpan Time False False
TotalWork String False False
TotalWorkMilliseconds Int False False
TotalWorkTimeSpan Time False False
WeekStartsOn Int False False
LinkedCreator String False False
LinkedLines String False False
LinkedManager String False False
LinkedPeriod String False False

Views

Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.

Jitterbit Connector for Microsoft Project Views

Name Description
Assignment_Resource Retrieve and modify assignments.
Assignment_Task Retrieve and modify assignments.
Events Retrieve events.
ProjectDetailPages Retrieve project details.
StageDetailPages Retrieve project details..
Tasks_Assignments Retrieve and modify assignments.
Tasks_Calendar Retrieve and modify assignments.
TimeSheetPeriods Retrieve timesheet periods.

Assignment_Resource

Retrieve and modify assignments.

Columns

Name Type References Description
Id [KEY] String The ID of the table.
Name String The name of the association.
ProjectId String The project Id.
AssignmentId String The assignment Id.

Assignment_Task

Retrieve and modify assignments.

Columns

Name Type References Description
Id [KEY] String The ID of the table.
Name String The name of the association.
ProjectId String The project Id.
AssignmentId String The assignment Id.

Events

Retrieve events.

Columns

Name Type References Description
Id [KEY] Int
EventName String
SourceName String

ProjectDetailPages

Retrieve project details.

Columns

Name Type References Description
Id [KEY] String
Name String
PageType Int
LinkedItem String

StageDetailPages

Retrieve project details..

Columns

Name Type References Description
Id [KEY] String
Name String
PageType Int
LinkedItem String
StageId [KEY] String Id of the parent.

Tasks_Assignments

Retrieve and modify assignments.

Columns

Name Type References Description
Id [KEY] String The ID of the table.
ProjectId String The project Id.
TaskId String The task Id.

Tasks_Calendar

Retrieve and modify assignments.

Columns

Name Type References Description
Id [KEY] String The ID of the table.
Name String The name of the association.
ProjectId String The project Id.
TaskId String The task Id.

TimeSheetPeriods

Retrieve timesheet periods.

Columns

Name Type References Description
Id [KEY] String
End Datetime
Name String
Start Datetime
LinkedTimeSheet String

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Stored procedures* are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure* because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures* take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.

Jitterbit Connector for Microsoft Project Stored Procedures

Name Description
CheckIn Check in the specified project.
CheckOut Check out the specified project.
GetOAuthAccessToken Gets an authentication token from Microsoft Project.
GetOAuthAuthorizationURL Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.
GetOnlineCookies Retrieves the cookies for authentication with SharePoint Online.
Publish Publish the specified project.
RefreshOAuthAccessToken Refreshes the OAuth access token used for authentication with Microsoft Project.
Search Searches OData using the given URL.

CheckIn

Check in the specified project.

Input

Name Type Description
ProjectId String The ID of the project you are checking in.
Force String Set to true to force check in. Default is false.

Result Set Columns

Name Type Description
Success String The result of the operation.

CheckOut

Check out the specified project.

Input

Name Type Description
ProjectId String The ID of the project you are checking out.

Result Set Columns

Name Type Description
Success String The result of the operation.

GetOAuthAccessToken

Gets an authentication token from Microsoft Project.

Input

Name Type Description
Scope String A comma-separated list of permissions to request from the user. Please check the Microsoft Project API for a list of available permissions.
CallbackUrl String The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL you have specified in the Microsoft Project app settings. Only needed when the Authmode parameter is Web.
Verifier String The verifier returned from Microsoft Project after the user has authorized your app to have access to their data. This value will be returned as a parameter to the callback URL.
State String Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Microsoft Project authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations.

Result Set Columns

Name Type Description
OAuthAccessToken String The access token used for communication with Microsoft Project.
OAuthRefreshToken String The OAuth refresh token. This is the same as the access token in the case of Microsoft Project.
ExpiresIn String The remaining lifetime on the access token. A -1 denotes that it will not expire.

GetOAuthAuthorizationURL

Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.

Input

Name Type Description
CallbackUrl String The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL in the Microsoft Project app settings.
Scope String A comma-separated list of scopes to request from the user. Please check the Microsoft Project API documentation for a list of available permissions.
State String Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Microsoft Project authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations.

Result Set Columns

Name Type Description
URL String The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app.

GetOnlineCookies

Retrieves the cookies for authentication with SharePoint Online.

Input

Name Type Description
User String Your SharePoint Online username.
Password String Your SharePoint Online password.
STSURL String Your SharePoint Online STSURL. This only needs to be set if your organization uses a local ADFS server for authentication.

Result Set Columns

Name Type Description
Cookie String The cookies that will need to be entered in the AuthCookie connection string property.

Publish

Publish the specified project.

Input

Name Type Description
ProjectId String The ID of the project you are publishing.
CheckIn String Set to true to publish the proejct. Default value is false.

Result Set Columns

Name Type Description
Success String The result of the operation.

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with Microsoft Project.

Input

Name Type Description
OAuthRefreshToken String Set this to the token value that expired.

Result Set Columns

Name Type Description
OAuthAccessToken String The authentication token returned from Microsoft Project. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken String This is the same as the access token.
ExpiresIn String The remaining lifetime on the access token.

Search

ODataV1 Data Model

The ODataV1 schema implement the ProjectData service endpoints, which is read-only and can only be used for reporting.

Views

Read-only Views are retrieved dynamically from the authenticated Microsoft Project account.

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Stored Procedures are function-like interfaces to Microsoft Project. Stored procedures* allow you to execute operations to Microsoft Project.

ODataV1 advantages compared to ODataV2

  • The custom fields are exposed as view columns.
  • The ProjectId is not required to query any of the exposed views, since the ProjectData endpoints return data for all the available projects.

ODataV1 limitations

  • No write operation is supported as this schema is read-only.

Views

Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.

Jitterbit Connector for Microsoft Project Views

Name Description
AssignmentBaselines Retrieve assignment baselines.
AssignmentBaselineTimephasedDataSet Retrieve assignment baseline timephased dataset.
Assignments Retrieve assignments.
AssignmentTimephasedDataSet Retrieve assignment timephased dataset.
BusinessDriverDepartments Retrieve business driver departments.
BusinessDrivers Retrieve business drivers.
CostConstraintScenarios Retrieve cost constraint scenarios.
CostScenarioProjects Retrieve cost scenario projects.
Deliverables Retrieve deliverables.
Engagements Retrieve engagements.
EngagementsComments Retrieve engagements comments.
EngagementsTimephasedDataSet Retrieve engagements timephased dataset.
FiscalPeriods Retrieve fiscal periods.
Issues Retrieve issues.
IssueTaskAssociations Retrieve issue-task associations.
PortfolioAnalyses Retrieve portfolio analyses.
PortfolioAnalysisProjects Retrieve portfolio analysis projects.
PrioritizationDriverRelations Retrieve prioritization driver relations.
PrioritizationDrivers Retrieve prioritization drivers.
Prioritizations Retrieve prioritizations.
ProjectBaselines Retrieve project baselines.
Projects Retrieve projects.
ProjectWorkflowStageDataSet Retrieve project workflow stage dataset.
ResourceConstraintScenarios Retrieve resource constraint scenarios.
ResourceDemandTimephasedDataSet Retrieve resource demand timephased dataset.
Resources Retrieve resources.
ResourceScenarioProjects Retrieve resource scenario projects.
ResourceTimephasedDataSet Retrieve resource timephased dataset.
Risks Retrieve risks.
RiskTaskAssociations Retrieve risk task associations.
TaskBaselines Retrieve task baselines.
TaskBaselineTimephasedDataSet Retrieve task baseline timephased dataset.
Tasks Retrieve tasks.
TaskTimephasedDataSet Retrieve task timephased dataset.
TimeSet Retrieve timeset.
TimesheetClasses Retrieve timesheet classes.
TimesheetLineActualDataSet Retrieve timesheet line actual dataset.
TimesheetLines Retrieve timesheet lines.
TimesheetPeriods Retrieve timesheet periods.
Timesheets Retrieve timesheets.

AssignmentBaselines

Retrieve assignment baselines.

Columns

Name Type References Description
AssignmentId [KEY] String
BaselineNumber [KEY] Int
ProjectId [KEY] String
AssignmentBaselineBudgetCost Decimal
AssignmentBaselineBudgetMaterialWork Decimal
AssignmentBaselineBudgetWork Decimal
AssignmentBaselineCost Decimal
AssignmentBaselineFinishDate Datetime
AssignmentBaselineMaterialWork Decimal
AssignmentBaselineModifiedDate Datetime
AssignmentBaselineStartDate Datetime
AssignmentBaselineWork Decimal
AssignmentType Int
ProjectName String
TaskId String
TaskName String
LinkedAssignment String
LinkedAssignmentBaselineTimephasedDataSet String
LinkedProject String
LinkedTask String

AssignmentBaselineTimephasedDataSet

Retrieve assignment baseline timephased dataset.

Columns

Name Type References Description
AssignmentId [KEY] String
BaselineNumber [KEY] Int
ProjectId [KEY] String
TimeByDay [KEY] Datetime
AssignmentBaselineBudgetCost Decimal
AssignmentBaselineBudgetMaterialWork Decimal
AssignmentBaselineBudgetWork Decimal
AssignmentBaselineCost Decimal
AssignmentBaselineMaterialWork Decimal
AssignmentBaselineModifiedDate Datetime
AssignmentBaselineWork Decimal
FiscalPeriodId String
ProjectName String
ResourceId String
TaskId String
TaskName String
LinkedAssignment String
LinkedBaseline String
LinkedProject String
LinkedTasks String
LinkedTime String

Assignments

Retrieve assignments.

Columns

Name Type References Description
AssignmentId [KEY] String
ProjectId [KEY] String
AssignmentActualCost Decimal
AssignmentActualFinishDate Datetime
AssignmentActualOvertimeCost Decimal
AssignmentActualOvertimeWork Decimal
AssignmentActualRegularCost Decimal
AssignmentActualRegularWork Decimal
AssignmentActualStartDate Datetime
AssignmentActualWork Decimal
AssignmentACWP Decimal
AssignmentAllUpdatesApplied Bool
AssignmentBCWP Decimal
AssignmentBCWS Decimal
AssignmentBookingDescription String
AssignmentBookingId Int
AssignmentBookingName String
AssignmentBudgetCost Decimal
AssignmentBudgetMaterialWork Decimal
AssignmentBudgetWork Decimal
AssignmentCost Decimal
AssignmentCostVariance Decimal
AssignmentCreatedDate Datetime
AssignmentCreatedRevisionCounter Int
AssignmentCV Decimal
AssignmentDelay Decimal
AssignmentFinishDate Datetime
AssignmentFinishVariance Decimal
AssignmentIsOverallocated Bool
AssignmentIsPublished Bool
AssignmentMaterialActualWork Decimal
AssignmentMaterialWork Decimal
AssignmentModifiedDate Datetime
AssignmentModifiedRevisionCounter Int
AssignmentOvertimeCost Decimal
AssignmentOvertimeWork Decimal
AssignmentPeakUnits Decimal
AssignmentPercentWorkCompleted Int
AssignmentRegularCost Decimal
AssignmentRegularWork Decimal
AssignmentRemainingCost Decimal
AssignmentRemainingOvertimeCost Decimal
AssignmentRemainingOvertimeWork Decimal
AssignmentRemainingRegularCost Decimal
AssignmentRemainingRegularWork Decimal
AssignmentRemainingWork Decimal
AssignmentResourcePlanWork Decimal
AssignmentResourceType Int
AssignmentStartDate Datetime
AssignmentStartVariance Decimal
AssignmentSV Decimal
AssignmentType Int
AssignmentUpdatesAppliedDate Datetime
AssignmentVAC Decimal
AssignmentWork Decimal
AssignmentWorkVariance Decimal
CostType_R String
FlagStatus_T Bool
Health_T String
IsPublic Bool
ProjectName String
RBS_R String
ResourceId String
ResourceName String
TaskId String
TaskIsActive Bool
TaskName String
TimesheetClassId String
TypeDescription String
TypeName String
LinkedBaseline String
LinkedProject String
LinkedResource String
LinkedTask String
LinkedTimephasedData String

AssignmentTimephasedDataSet

Retrieve assignment timephased dataset.

Columns

Name Type References Description
AssignmentId [KEY] String
ProjectId [KEY] String
TimeByDay [KEY] Datetime
AssignmentActualCost Decimal
AssignmentActualOvertimeCost Decimal
AssignmentActualOvertimeWork Decimal
AssignmentActualRegularCost Decimal
AssignmentActualRegularWork Decimal
AssignmentActualWork Decimal
AssignmentBudgetCost Decimal
AssignmentBudgetMaterialWork Decimal
AssignmentBudgetWork Decimal
AssignmentCombinedWork Decimal
AssignmentCost Decimal
AssignmentMaterialActualWork Decimal
AssignmentMaterialWork Decimal
AssignmentModifiedDate Datetime
AssignmentOvertimeCost Decimal
AssignmentOvertimeWork Decimal
AssignmentRegularCost Decimal
AssignmentRegularWork Decimal
AssignmentRemainingCost Decimal
AssignmentRemainingOvertimeCost Decimal
AssignmentRemainingOvertimeWork Decimal
AssignmentRemainingRegularCost Decimal
AssignmentRemainingRegularWork Decimal
AssignmentRemainingWork Decimal
AssignmentResourcePlanWork Decimal
AssignmentWork Decimal
FiscalPeriodId String
ProjectName String
ResourceId String
TaskId String
TaskIsActive Bool
TaskName String
LinkedAssignment String
LinkedProject String
LinkedTask String
LinkedTime String

BusinessDriverDepartments

Retrieve business driver departments.

Columns

Name Type References Description
BusinessDriverId [KEY] String
DepartmentId [KEY] String
BusinessDriverName String
DepartmentName String
LinkedBusinessDriver String

BusinessDrivers

Retrieve business drivers.

Columns

Name Type References Description
BusinessDriverId [KEY] String
BusinessDriverCreatedDate Datetime
BusinessDriverDescription String
BusinessDriverIsActive Bool
BusinessDriverModifiedDate Datetime
BusinessDriverName String
CreatedByResourceId String
CreatedByResourceName String
ImpactDescriptionExtreme String
ImpactDescriptionLow String
ImpactDescriptionModerate String
ImpactDescriptionNone String
ImpactDescriptionStrong String
ModifiedByResourceId String
ModifiedByResourceName String
LinkedCreatedByResource String
LinkedDepartments String
LinkedModifiedByResource String

CostConstraintScenarios

Retrieve cost constraint scenarios.

Columns

Name Type References Description
ScenarioId [KEY] String
AnalysisId String
AnalysisName String
CreatedByResourceId String
CreatedByResourceName String
CreatedDate Datetime
ModifiedByResourceId String
ModifiedByResourceName String
ModifiedDate Datetime
ScenarioDescription String
ScenarioName String
SelectedProjectsCost Decimal
SelectedProjectsPriority Double
UnselectedProjectsCost Decimal
UnselectedProjectsPriority Double
UseDependencies Bool
LinkedAnalysis String
LinkedCostScenarioProjects String
LinkedCreatedByResource String
LinkedModifiedByResource String
LinkedResourceConstraintScenarios String

CostScenarioProjects

Retrieve cost scenario projects.

Columns

Name Type References Description
ProjectId [KEY] String
ScenarioId [KEY] String
AbsolutePriority Double
AnalysisId String
AnalysisName String
ForceAliasLookupTableId String
ForceAliasLookupTableName String
ForceStatus Int
HardConstraintValue Decimal
Priority Double
ProjectName String
ScenarioName String
Status Int
LinkedAnalysis String
LinkedCostConstraintScenario String
LinkedProject String

Deliverables

Retrieve deliverables.

Columns

Name Type References Description
DeliverableId [KEY] String
ProjectId [KEY] String
CreateByResource String
CreatedDate Datetime
Description String
FinishDate Datetime
IsFolder Bool
ItemRelativeUrlPath String
ModifiedByResource String
ModifiedDate Datetime
ProjectName String
StartDate Datetime
Title String
LinkedDependentProjects String
LinkedDependentTasks String
LinkedParentProjects String
LinkedParentTasks String
LinkedProject String

Engagements

Retrieve engagements.

Columns

Name Type References Description
EngagementId [KEY] String
CommittedFinishDate Datetime
CommittedMaxUnits Decimal
CommittedStartDate Datetime
CommittedWork Decimal
EngagementCreatedDate Datetime
EngagementModifiedDate Datetime
EngagementName String
EngagementReviewedDate Datetime
EngagementStatus Int
EngagementSubmittedDate Datetime
ModifiedByResourceId String
ModifiedByResourceName String
ProjectId String
ProjectName String
ProposedFinishDate Datetime
ProposedMaxUnits Decimal
ProposedStartDate Datetime
ProposedWork Decimal
ResourceId String
ResourceName String
ReviewedByResourceId String
ReviewedByResourceName String
SubmittedByResourceId String
SubmittedByResourceName String
LinkedComment String
LinkedTimephasedInfo String

EngagementsComments

Retrieve engagements comments.

Columns

Name Type References Description
CommentId [KEY] String
AuthorId String
AuthorName String
CommentCreatedDate Datetime
CommentMessage String
EngagementId String
EngagementName String
LinkedEngagement String

EngagementsTimephasedDataSet

Retrieve engagements timephased dataset.

Columns

Name Type References Description
EngagementId [KEY] String
TimeByDay [KEY] Datetime
CommittedMaxUnits Decimal
CommittedWork Decimal
EngagementModifiedDate Datetime
EngagementName String
ProjectId String
ProjectName String
ProposedMaxUnits Decimal
ProposedWork Decimal
ResourceId String
ResourceName String
LinkedEngagement String

FiscalPeriods

Retrieve fiscal periods.

Columns

Name Type References Description
FiscalPeriodId [KEY] String
CreatedDate Datetime
FiscalPeriodFinish Datetime
FiscalPeriodModifiedDate Datetime
FiscalPeriodName String
FiscalPeriodQuarter Int
FiscalPeriodStart Datetime
FiscalPeriodYear Int

Issues

Retrieve issues.

Columns

Name Type References Description
IssueId [KEY] String
ProjectId [KEY] String
AssignedToResource String
Category String
CreateByResource String
CreatedDate Datetime
Discussion String
DueDate Datetime
IsFolder Bool
ItemRelativeUrlPath String
ModifiedByResource String
ModifiedDate Datetime
NumberOfAttachments Int
Owner String
Priority String
ProjectName String
Resolution String
Status String
Title String
LinkedProject String
LinkedRelatedRisks String
LinkedSubIssues String
LinkedTasks String

IssueTaskAssociations

Retrieve issue-task associations.

Columns

Name Type References Description
IssueId [KEY] String
ProjectId [KEY] String
RelationshipType [KEY] Int
TaskId [KEY] String
ProjectName String
RelatedProjectId String
RelatedProjectName String
TaskName String
Title String
LinkedIssue String
LinkedProject String
LinkedRelatedProject String
LinkedTask String

PortfolioAnalyses

Retrieve portfolio analyses.

Columns

Name Type References Description
AnalysisId [KEY] String
AnalysisDescription String
AnalysisName String
AnalysisType Int
BookingType Int
CreatedByResourceId String
CreatedByResourceName String
CreatedDate Datetime
DepartmentId String
DepartmentName String
FilterResourcesByDepartment Bool
FilterResourcesByRBS Bool
FilterResourcesByRBSValueId String
FilterResourcesByRBSValueText String
ForcedInAliasLookupTableId String
ForcedInAliasLookupTableName String
ForcedOutAliasLookupTableId String
ForcedOutAliasLookupTableName String
HardConstraintCustomFieldId String
HardConstraintCustomFieldName String
ModifiedByResourceId String
ModifiedByResourceName String
ModifiedDate Datetime
PlanningHorizonEndDate Datetime
PlanningHorizonStartDate Datetime
PrioritizationId String
PrioritizationName String
PrioritizationType Int
RoleCustomFieldId String
RoleCustomFieldName String
TimeScale Int
UseAlternateProjectDatesForResourcePlans Bool
LinkedAnalysisProjects String
LinkedCostConstraintScenarios String
LinkedCreatedByResource String
LinkedModifiedByResource String
LinkedPrioritization String
LinkedResourceConstraintScenarios String

PortfolioAnalysisProjects

Retrieve portfolio analysis projects.

Columns

Name Type References Description
AnalysisId [KEY] String
ProjectId [KEY] String
AbsolutePriority Double
AnalysisName String
Duration Decimal
FinishNoLaterThan Datetime
Locked Int
OriginalEndDate Datetime
OriginalStartDate Datetime
Priority Double
ProjectName String
StartDate Datetime
StartNoEarlierThan Datetime
LinkedAnalysis String
LinkedProject String

PrioritizationDriverRelations

Retrieve prioritization driver relations.

Columns

Name Type References Description
BusinessDriver1Id [KEY] String
BusinessDriver2Id [KEY] String
PrioritizationId [KEY] String
BusinessDriver1Name String
BusinessDriver2Name String
PrioritizationName String
RelationValue String
LinkedBusinessDriver1 String
LinkedBusinessDriver2 String
LinkedPrioritization String

PrioritizationDrivers

Retrieve prioritization drivers.

Columns

Name Type References Description
BusinessDriverId [KEY] String
PrioritizationId [KEY] String
BusinessDriverName String
BusinessDriverPriority Double
PrioritizationName String
LinkedBusinessDriver String
LinkedPrioritization String

Prioritizations

Retrieve prioritizations.

Columns

Name Type References Description
PrioritizationId [KEY] String
ConsistencyRatio Double
CreatedByResourceId String
CreatedByResourceName String
DepartmentId String
DepartmentName String
ModifiedByResourceId String
ModifiedByResourceName String
PrioritizationCreatedDate Datetime
PrioritizationDescription String
PrioritizationIsManual Bool
PrioritizationModifiedDate Datetime
PrioritizationName String
LinkedCreatedByResource String
LinkedModifiedByResource String
LinkedPrioritizationDriverRelations String
LinkedPrioritizationDrivers String

ProjectBaselines

Retrieve project baselines.

Columns

Name Type References Description
BaselineNumber [KEY] Int
ProjectId [KEY] String
ProjectBaselineBudgetCost Decimal
ProjectBaselineBudgetWork Decimal
ProjectBaselineCost Decimal
ProjectBaselineDeliverableFinishDate Datetime
ProjectBaselineDeliverableStartDate Datetime
ProjectBaselineDuration Decimal
ProjectBaselineDurationString String
ProjectBaselineFinishDate Datetime
ProjectBaselineFinishDateString String
ProjectBaselineFixedCost Decimal
ProjectBaselineModifiedDate Datetime
ProjectBaselineStartDate Datetime
ProjectBaselineStartDateString String
ProjectBaselineWork Decimal
ProjectName String
TaskId String
LinkedProject String

Projects

Retrieve projects.

Columns

Name Type References Description
ProjectId [KEY] String
EnterpriseProjectTypeDescription String
EnterpriseProjectTypeId String
EnterpriseProjectTypeIsDefault Bool
EnterpriseProjectTypeName String
OptimizerCommitDate Datetime
OptimizerDecisionAliasLookupTableId String
OptimizerDecisionAliasLookupTableValueId String
OptimizerDecisionID Int
OptimizerDecisionName String
OptimizerSolutionName String
ParentProjectId String
PlannerCommitDate Datetime
PlannerDecisionAliasLookupTableId String
PlannerDecisionAliasLookupTableValueId String
PlannerDecisionID Int
PlannerDecisionName String
PlannerEndDate Datetime
PlannerSolutionName String
PlannerStartDate Datetime
ProjectActualCost Decimal
ProjectActualDuration Decimal
ProjectActualFinishDate Datetime
ProjectActualOvertimeCost Decimal
ProjectActualOvertimeWork Decimal
ProjectActualRegularCost Decimal
ProjectActualRegularWork Decimal
ProjectActualStartDate Datetime
ProjectActualWork Decimal
ProjectACWP Decimal
ProjectAuthorName String
ProjectBCWP Decimal
ProjectBCWS Decimal
ProjectBudgetCost Decimal
ProjectBudgetWork Decimal
ProjectCalculationsAreStale Bool
ProjectCalendarDuration Int
ProjectCategoryName String
ProjectCompanyName String
ProjectCost Decimal
ProjectCostVariance Decimal
ProjectCPI Decimal
ProjectCreatedDate Datetime
ProjectCurrency String
ProjectCV Decimal
ProjectCVP Decimal
ProjectDepartments String
ProjectDescription String
ProjectDuration Decimal
ProjectDurationVariance Decimal
ProjectEAC Decimal
ProjectEarlyFinish Datetime
ProjectEarlyStart Datetime
ProjectEarnedValueIsStale Bool
ProjectEnterpriseFeatures Bool
ProjectFinishDate Datetime
ProjectFinishVariance Decimal
ProjectFixedCost Decimal
ProjectIdentifier String
ProjectKeywords String
ProjectLastPublishedDate Datetime
ProjectLateFinish Datetime
ProjectLateStart Datetime
ProjectManagerName String
ProjectModifiedDate Datetime
ProjectName String
ProjectOvertimeCost Decimal
ProjectOvertimeWork Decimal
ProjectOwnerId String
ProjectOwnerName String
ProjectPercentCompleted Int
ProjectPercentWorkCompleted Int
ProjectRegularCost Decimal
ProjectRegularWork Decimal
ProjectRemainingCost Decimal
ProjectRemainingDuration Decimal
ProjectRemainingOvertimeCost Decimal
ProjectRemainingOvertimeWork Decimal
ProjectRemainingRegularCost Decimal
ProjectRemainingRegularWork Decimal
ProjectRemainingWork Decimal
ProjectResourcePlanWork Decimal
ProjectSPI Decimal
ProjectStartDate Datetime
ProjectStartVariance Decimal
ProjectStatusDate Datetime
ProjectSubject String
ProjectSV Decimal
ProjectSVP Decimal
ProjectTCPI Decimal
ProjectTimephased String
ProjectTitle String
ProjectType Int
ProjectVAC Decimal
ProjectWork Decimal
ProjectWorkspaceInternalUrl String
ProjectWorkVariance Decimal
ResourcePlanUtilizationDate Datetime
ResourcePlanUtilizationType Int
WorkflowError String
WorkflowErrorResponseCode Int
WorkflowInstanceId String
WorkflowOwnerId String
WorkflowOwnerName String
LinkedAssignmentBaselines String
LinkedAssignments String
LinkedDeliverables String
LinkedDependencies String
LinkedIssues String
LinkedRisks String
LinkedStagesInfo String
LinkedTasks String

ProjectWorkflowStageDataSet

Retrieve project workflow stage dataset.

Columns

Name Type References Description
ProjectId [KEY] String
StageId [KEY] String
LastModifiedDate Datetime
LCID Int
PhaseDescription String
PhaseName String
ProjectName String
StageCompletionDate Datetime
StageDescription String
StageEntryDate Datetime
StageInformation String
StageLastSubmittedDate Datetime
StageName String
StageOrder Int
StageStateDescription String
StageStatus Int
LinkedProject String

ResourceConstraintScenarios

Retrieve resource constraint scenarios.

Columns

Name Type References Description
ScenarioId [KEY] String
AllocationThreshold Decimal
AnalysisId String
AnalysisName String
ConstraintType Int
ConstraintValue Decimal
CostConstraintScenarioId String
CostConstraintScenarioName String
CreatedByResourceId String
CreatedByResourceName String
CreatedDate Datetime
EnforceProjectDependencies Bool
EnforceSchedulingConstraints Bool
HiringType Int
ModifiedByResourceId String
ModifiedByResourceName String
ModifiedDate Datetime
RateTable Int
ScenarioDescription String
ScenarioName String
LinkedAnalysis String
LinkedCostConstraintScenario String
LinkedCreatedByResource String
LinkedModifiedByResource String
LinkedResourceScenarioProjects String

ResourceDemandTimephasedDataSet

Retrieve resource demand timephased dataset.

Columns

Name Type References Description
ProjectId [KEY] String
ResourceId [KEY] String
TimeByDay [KEY] Datetime
FiscalPeriodId String
ProjectName String
ResourceDemand Decimal
ResourceDemandModifiedDate Datetime
ResourceName String
ResourcePlanUtilizationDate Datetime
ResourcePlanUtilizationType Int
LinkedProject String
LinkedResource String
LinkedTime String

Resources

Retrieve resources.

Columns

Name Type References Description
ResourceId [KEY] String
CostType String
RBS String
ResourceBaseCalendar String
ResourceBookingType Int
ResourceCanLevel Bool
ResourceCode String
ResourceCostCenter String
ResourceCostPerUse Decimal
ResourceCreatedDate Datetime
ResourceDepartments String
ResourceEarliestAvailableFrom Datetime
ResourceEmailAddress String
ResourceGroup String
ResourceHyperlink String
ResourceHyperlinkHref String
ResourceInitials String
ResourceIsActive Bool
ResourceIsGeneric Bool
ResourceIsTeam Bool
ResourceLatestAvailableTo Datetime
ResourceMaterialLabel String
ResourceMaxUnits Decimal
ResourceModifiedDate Datetime
ResourceName String
ResourceNTAccount String
ResourceOvertimeRate Decimal
ResourceStandardRate Decimal
ResourceStatusId String
ResourceStatusName String
ResourceTimesheetManageId String
ResourceType Int
ResourceWorkgroup Int
TypeDescription String
TypeName String
LinkedAssignments String
LinkedResourceDemandTimephasedInfo String
LinkedTimephasedInfoDataSet String

ResourceScenarioProjects

Retrieve resource scenario projects.

Columns

Name Type References Description
ProjectId [KEY] String
ScenarioId [KEY] String
AbsolutePriority Double
AnalysisId String
AnalysisName String
CostConstraintScenarioId String
CostConstraintScenarioName String
ForceAliasLookupTableId String
ForceAliasLookupTableName String
ForceStatus Int
HardConstraintValue Decimal
NewStartDate Datetime
Priority Double
ProjectName String
ResourceCost Decimal
ResourceWork Decimal
ScenarioName String
Status Int
LinkedAnalysis String
LinkedCostConstraintScenario String
LinkedProject String
LinkedResourceConstraintScenario String

ResourceTimephasedDataSet

Retrieve resource timephased dataset.

Columns

Name Type References Description
ResourceId [KEY] String
TimeByDay [KEY] Datetime
BaseCapacity Decimal
Capacity Decimal
FiscalPeriodId String
ResourceModifiedDate Datetime
ResourceName String
LinkedResource String
LinkedTime String

Risks

Retrieve risks.

Columns

Name Type References Description
ProjectId [KEY] String
RiskId [KEY] String
AssignedToResource String
Category String
ContingencyPlan String
Cost Double
CostExposure Double
CreateByResource String
CreatedDate Datetime
Description String
DueDate Datetime
Exposure Double
Impact Double
IsFolder Bool
ItemRelativeUrlPath String
MitigationPlan String
ModifiedByResource String
ModifiedDate Datetime
NumberOfAttachments Int
Owner String
Probability Double
ProjectName String
Status String
Title String
TriggerDescription String
TriggerTask String
LinkedProject String
LinkedRelatedIssues String
LinkedSubRisks String
LinkedTasks String

RiskTaskAssociations

Retrieve risk task associations.

Columns

Name Type References Description
ProjectId [KEY] String
RelationshipType [KEY] Int
RiskId [KEY] String
TaskId [KEY] String
ProjectName String
RelatedProjectId String
RelatedProjectName String
TaskName String
Title String
LinkedProject String
LinkedRelatedProject String
LinkedRisk String
LinkedTask String

TaskBaselines

Retrieve task baselines.

Columns

Name Type References Description
BaselineNumber [KEY] Int
ProjectId [KEY] String
TaskId [KEY] String
ProjectName String
TaskBaselineBudgetCost Decimal
TaskBaselineBudgetWork Decimal
TaskBaselineCost Decimal
TaskBaselineDeliverableFinishDate Datetime
TaskBaselineDeliverableStartDate Datetime
TaskBaselineDuration Decimal
TaskBaselineDurationString String
TaskBaselineFinishDate Datetime
TaskBaselineFinishDateString String
TaskBaselineFixedCost Decimal
TaskBaselineModifiedDate Datetime
TaskBaselineStartDate Datetime
TaskBaselineStartDateString String
TaskBaselineWork Decimal
TaskName String
LinkedProject String
LinkedTask String
LinkedTaskBaselineTimephasedDataSet String

TaskBaselineTimephasedDataSet

Retrieve task baseline timephased dataset.

Columns

Name Type References Description
BaselineNumber [KEY] Int
ProjectId [KEY] String
TaskId [KEY] String
TimeByDay [KEY] Datetime
FiscalPeriodId String
ProjectName String
TaskBaselineBudgetCost Decimal
TaskBaselineBudgetWork Decimal
TaskBaselineCost Decimal
TaskBaselineFixedCost Decimal
TaskBaselineModifiedDate Datetime
TaskBaselineWork Decimal
TaskName String
LinkedProject String
LinkedTask String
LinkedTaskBaselines String
LinkedTime String

Tasks

Retrieve tasks.

Columns

Name Type References Description
ProjectId [KEY] String
TaskId [KEY] String
FlagStatus Bool
Health String
ParentTaskId String
ParentTaskName String
ProjectName String
TaskActualCost Decimal
TaskActualDuration Decimal
TaskActualFinishDate Datetime
TaskActualFixedCost Decimal
TaskActualOvertimeCost Decimal
TaskActualOvertimeWork Decimal
TaskActualRegularCost Decimal
TaskActualRegularWork Decimal
TaskActualStartDate Datetime
TaskActualWork Decimal
TaskACWP Decimal
TaskBCWP Decimal
TaskBCWS Decimal
TaskBudgetCost Decimal
TaskBudgetWork Decimal
TaskClientUniqueId Int
TaskCost Decimal
TaskCostVariance Decimal
TaskCPI Decimal
TaskCreatedDate Datetime
TaskCreatedRevisionCounter Int
TaskCV Decimal
TaskCVP Decimal
TaskDeadline Datetime
TaskDeliverableFinishDate Datetime
TaskDeliverableStartDate Datetime
TaskDuration Decimal
TaskDurationIsEstimated Bool
TaskDurationString String
TaskDurationVariance Decimal
TaskEAC Decimal
TaskEarlyFinish Datetime
TaskEarlyStart Datetime
TaskFinishDate Datetime
TaskFinishDateString String
TaskFinishVariance Decimal
TaskFixedCost Decimal
TaskFixedCostAssignmentId String
TaskFreeSlack Decimal
TaskHyperLinkAddress String
TaskHyperLinkFriendlyName String
TaskHyperLinkSubAddress String
TaskIgnoresResourceCalendar Bool
TaskIndex Int
TaskIsActive Bool
TaskIsCritical Bool
TaskIsEffortDriven Bool
TaskIsExternal Bool
TaskIsManuallyScheduled Bool
TaskIsMarked Bool
TaskIsMilestone Bool
TaskIsOverallocated Bool
TaskIsProjectSummary Bool
TaskIsRecurring Bool
TaskIsSummary Bool
TaskLateFinish Datetime
TaskLateStart Datetime
TaskLevelingDelay Decimal
TaskModifiedDate Datetime
TaskModifiedRevisionCounter Int
TaskName String
TaskOutlineLevel Int
TaskOutlineNumber String
TaskOvertimeCost Decimal
TaskOvertimeWork Decimal
TaskPercentCompleted Int
TaskPercentWorkCompleted Int
TaskPhysicalPercentCompleted Int
TaskPriority Int
TaskRegularCost Decimal
TaskRegularWork Decimal
TaskRemainingCost Decimal
TaskRemainingDuration Decimal
TaskRemainingOvertimeCost Decimal
TaskRemainingOvertimeWork Decimal
TaskRemainingRegularCost Decimal
TaskRemainingRegularWork Decimal
TaskRemainingWork Decimal
TaskResourcePlanWork Decimal
TaskSPI Decimal
TaskStartDate Datetime
TaskStartDateString String
TaskStartVariance Decimal
TaskStatusManagerUID String
TaskSV Decimal
TaskSVP Decimal
TaskTCPI Decimal
TaskTotalSlack Decimal
TaskVAC Decimal
TaskWBS String
TaskWork Decimal
TaskWorkVariance Decimal
LinkedAssignments String
LinkedAssignmentsBaselines String
LinkedAssignmentsBaselineTimephasedData String
LinkedBaselines String
LinkedBaselinesTimephasedDataSet String
LinkedIssues String
LinkedProject String
LinkedRisks String
LinkedTimephasedInfo String

TaskTimephasedDataSet

Retrieve task timephased dataset.

Columns

Name Type References Description
ProjectId [KEY] String
TaskId [KEY] String
TimeByDay [KEY] Datetime
FiscalPeriodId String
ProjectName String
TaskActualCost Decimal
TaskActualWork Decimal
TaskBudgetCost Decimal
TaskBudgetWork Decimal
TaskCost Decimal
TaskIsActive Bool
TaskIsProjectSummary Bool
TaskModifiedDate Datetime
TaskName String
TaskOvertimeWork Decimal
TaskResourcePlanWork Decimal
TaskWork Decimal
LinkedProject String
LinkedTask String
LinkedTime String

TimeSet

Retrieve timeset.

Columns

Name Type References Description
TimeByDay [KEY] Datetime
FiscalPeriodId String
FiscalPeriodModifiedDate Datetime
FiscalPeriodName String
FiscalPeriodStart Datetime
FiscalPeriodYear Int
FiscalQuarter Int
TimeDayOfTheMonth Int
TimeDayOfTheWeek Int
TimeMonthOfTheYear Int
TimeQuarter Int
TimeWeekOfTheYear Int

TimesheetClasses

Retrieve timesheet classes.

Columns

Name Type References Description
DepartmentId [KEY] String
TimesheetClassId [KEY] String
DepartmentName String
Description String
LCID Int
TimesheetClassName String
TimesheetClassType Int

TimesheetLineActualDataSet

Retrieve timesheet line actual dataset.

Columns

Name Type References Description
AdjustmentIndex [KEY] Int
TimeByDay [KEY] Datetime
TimesheetLineId [KEY] String
ActualOvertimeWorkBillable Decimal
ActualOvertimeWorkNonBillable Decimal
ActualWorkBillable Decimal
ActualWorkNonBillable Decimal
Comment String
CreatedDate Datetime
LastChangedResourceName String
PlannedWork Decimal
ResourceName String
TimeByDay_DayOfMonth Int
TimeByDay_DayOfWeek Int
TimesheetLineModifiedDate Datetime
LinkedLastChangedByResource String
LinkedTime String
LinkedTimesheetLine String

TimesheetLines

Retrieve timesheet lines.

Columns

Name Type References Description
TimesheetLineId [KEY] String
ActualOvertimeWorkBillable Decimal
ActualOvertimeWorkNonBillable Decimal
ActualWorkBillable Decimal
ActualWorkNonBillable Decimal
AssignmentId String
CreatedDate Datetime
LastSavedWork Decimal
LCID Int
ModifiedDate Datetime
PeriodEndDate Datetime
PeriodStartDate Datetime
PlannedWork Decimal
ProjectId String
ProjectName String
TaskHierarchy String
TaskId String
TaskName String
TimesheetApproverResourceId String
TimesheetApproverResourceName String
TimesheetClassDescription String
TimesheetClassId String
TimesheetClassName String
TimesheetClassType Int
TimesheetId String
TimesheetLineComment String
TimesheetLineStatus String
TimesheetLineStatusId Int
TimesheetName String
TimesheetOwner String
TimesheetOwnerId String
TimesheetPeriodId String
TimesheetPeriodName String
TimesheetPeriodStatus String
TimesheetPeriodStatusId Int
TimesheetStatus String
TimesheetStatusId Int
LinkedActuals String
LinkedApproverResource String
LinkedTimesheet String
LinkedTimesheetClass String

TimesheetPeriods

Retrieve timesheet periods.

Columns

Name Type References Description
PeriodId [KEY] String
Description String
EndDate Datetime
LCID Int
PeriodName String
PeriodStatusId Int
StartDate Datetime

Timesheets

Retrieve timesheets.

Columns

Name Type References Description
TimesheetId [KEY] String
Comment String
Description String
EndDate Datetime
ModifiedDate Datetime
PeriodId String
PeriodName String
PeriodStatusId Int
StartDate Datetime
StatusDescription String
TimesheetName String
TimesheetOwner String
TimesheetOwnerId String
TimesheetStatusId Int
LinkedLines String
LinkedPeriods String

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Stored procedures* are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure* because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures* take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.

Jitterbit Connector for Microsoft Project Stored Procedures

Name Description
GetOAuthAccessToken Gets an authentication token from Microsoft Project.
GetOAuthAuthorizationURL Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.
GetOnlineCookies Retrieves the cookies for authentication with SharePoint Online.
RefreshOAuthAccessToken Refreshes the OAuth access token used for authentication with Microsoft Project.
Search Searches OData using the given URL.

GetOAuthAccessToken

Gets an authentication token from Microsoft Project.

Input

Name Type Description
Scope String A comma-separated list of permissions to request from the user. Please check the Microsoft Project API for a list of available permissions.
CallbackUrl String The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL you have specified in the Microsoft Project app settings. Only needed when the Authmode parameter is Web.
Verifier String The verifier returned from Microsoft Project after the user has authorized your app to have access to their data. This value will be returned as a parameter to the callback URL.
State String Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Microsoft Project authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations.

Result Set Columns

Name Type Description
OAuthAccessToken String The access token used for communication with Microsoft Project.
OAuthRefreshToken String The OAuth refresh token. This is the same as the access token in the case of Microsoft Project.
ExpiresIn String The remaining lifetime on the access token. A -1 denotes that it will not expire.

GetOAuthAuthorizationURL

Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.

Input

Name Type Description
CallbackUrl String The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL in the Microsoft Project app settings.
Scope String A comma-separated list of scopes to request from the user. Please check the Microsoft Project API documentation for a list of available permissions.
State String Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Microsoft Project authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations.

Result Set Columns

Name Type Description
URL String The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app.

GetOnlineCookies

Retrieves the cookies for authentication with SharePoint Online.

Input

Name Type Description
User String Your SharePoint Online username.
Password String Your SharePoint Online password.
STSURL String Your SharePoint Online STSURL. This only needs to be set if your organization uses a local ADFS server for authentication.

Result Set Columns

Name Type Description
Cookie String The cookies that will need to be entered in the AuthCookie connection string property.

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with Microsoft Project.

Input

Name Type Description
OAuthRefreshToken String Set this to the token value that expired.

Result Set Columns

Name Type Description
OAuthAccessToken String The authentication token returned from Microsoft Project. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken String This is the same as the access token.
ExpiresIn String The remaining lifetime on the access token.

Search

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Microsoft Project:

Data Source Tables

The following tables return information about how to connect to and query the data source:

  • sys_connection_props: Returns information on the available connection properties.
  • sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries, including batch operations:

  • sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

Name Type Description
CatalogName String The database name.

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns

Name Type Description
CatalogName String The database name.
SchemaName String The schema name.

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns

Name Type Description
CatalogName String The database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view.
TableType String The table type (table or view).
Description String A description of the table or view.
IsUpdateable Boolean Whether the table can be updated.

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Projects table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Projects' 

Columns

Name Type Description
CatalogName String The name of the database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view containing the column.
ColumnName String The column name.
DataTypeName String The data type name.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The storage size of the column.
DisplaySize Int32 The designated column's normal maximum width in characters.
NumericPrecision Int32 The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The column scale or number of digits to the right of the decimal point.
IsNullable Boolean Whether the column can contain null.
Description String A brief description of the column.
Ordinal Int32 The sequence number of the column.
IsAutoIncrement String Whether the column value is assigned in fixed increments.
IsGeneratedColumn String Whether the column is generated.
IsHidden Boolean Whether the column is hidden.
IsArray Boolean Whether the column is an array.

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns

Name Type Description
CatalogName String The database containing the stored procedure.
SchemaName String The schema containing the stored procedure.
ProcedureName String The name of the stored procedure.
Description String A description of the stored procedure.
ProcedureType String The type of the procedure, such as PROCEDURE or FUNCTION.

sys_procedureparameters

Describes stored procedure* parameters.

The following query returns information about all of the input parameters for the SelectEntries stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='SelectEntries' AND Direction=1 OR Direction=2

Columns

Name Type Description
CatalogName String The name of the database containing the stored procedure.
SchemaName String The name of the schema containing the stored procedure.
ProcedureName String The name of the stored procedure* containing the parameter.
ColumnName String The name of the stored procedure* parameter.
Direction Int32 An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName String The name of the data type.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision Int32 The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The number of digits to the right of the decimal point in numeric data.
IsNullable Boolean Whether the parameter can contain null.
IsRequired Boolean Whether the parameter is required for execution of the procedure.
IsArray Boolean Whether the parameter is an array.
Description String The description of the parameter.
Ordinal Int32 The index of the parameter.

sys_keycolumns

Describes the primary and foreign keys. The following query retrieves the primary key for the Projects table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Projects' 

Columns

Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
IsKey Boolean Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey Boolean Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.

sys_foreignkeys

Describes the foreign keys. The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

Columns

Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.
ForeignKeyType String Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns

Name Type Description
CatalogName String The name of the database containing the index.
SchemaName String The name of the schema containing the index.
TableName String The name of the table containing the index.
IndexName String The index name.
ColumnName String The name of the column associated with the index.
IsUnique Boolean True if the index is unique. False otherwise.
IsPrimary Boolean True if the index is a primary key. False otherwise.
Type Int16 An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder String The sort order: A for ascending or D for descending.
OrdinalPosition Int16 The sequence number of the column in the index.

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:microsoftproject:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

Name Type Description
Name String The name of the connection property.
ShortDescription String A brief description.
Type String The data type of the connection property.
Default String The default value if one is not explicitly set.
Values String A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value String The value you set or a preconfigured default.
Required Boolean Whether the property is required to connect.
Category String The category of the connection property.
IsSessionProperty String Whether the property is a session property, used to save information about the current connection.
Sensitivity String The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName String A camel-cased truncated form of the connection property name.
Ordinal Int32 The index of the parameter.
CatOrdinal Int32 The index of the parameter category.
Hierarchy String Shows dependent properties associated that need to be set alongside this one.
Visible Boolean Informs whether the property is visible in the connection UI.
ETC String Various miscellaneous information about the property.

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Collaborative Query Processing

When working with data sources that do not support SQL-92, you can query the sys_sqlinfo view to determine the query capabilities of the underlying APIs, expressed in SQL syntax. The connector offloads as much of the SELECT statement processing as possible to the server and then processes the rest of the query in memory.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. The following result set indicates the SELECT functionality that the connector can offload to the data source or process client side. Your data source may support additional SQL syntax. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

NameDescriptionPossible Values
AGGREGATE_FUNCTIONSSupported aggregation functions.AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNTWhether COUNT function is supported.YES, NO
IDENTIFIER_QUOTE_OPEN_CHARThe opening character used to escape an identifier.[
IDENTIFIER_QUOTE_CLOSE_CHARThe closing character used to escape an identifier.]
SUPPORTED_OPERATORSA list of supported SQL operators.=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BYWhether GROUP BY is supported, and, if so, the degree of support.NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
STRING_FUNCTIONSSupported string functions.LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONSSupported numeric functions.ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONSSupported date/time functions.NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLESIndicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNSA string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERNString value indicating what string is valid for an identifier.
SUPPORT_TRANSACTIONIndicates if the provider supports transactions such as commit and rollback.YES, NO
DIALECTIndicates the SQL dialect to use.
KEY_PROPERTIESIndicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMASIndicates if multiple schemas may exist for the provider.YES, NO
SUPPORTS_MULTIPLE_CATALOGSIndicates if multiple catalogs may exist for the provider.YES, NO
DATASYNCVERSIONThe Data Sync version needed to access this driver.Standard, Starter, Professional, Enterprise
DATASYNCCATEGORYThe Data Sync category of this driver.Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQLWhether enhanced SQL functionality beyond what is offered by the API is supported.TRUE, FALSE
SUPPORTS_BATCH_OPERATIONSWhether batch operations are supported.YES, NO
SQL_CAPAll supported SQL capabilities for this driver.SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONSA string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERYIndicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side.YES, NO
PSEUDO_COLUMNSA string array indicating the available pseudo columns.
MERGE_ALWAYSIf the value is true, The Merge Mode is forcibly executed in Data Sync.TRUE, FALSE
REPLICATION_MIN_DATE_QUERYA select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTIONAllows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATEAllows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERYA select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTIONAllows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATEA list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTIDIndicates whether the CheckCache statement should be done against the parent key column.TRUE, FALSE
CREATE_SCHEMA_PROCEDURESIndicates stored procedures* that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:
SELECT * FROM sys_sqlinfo WHERE Name='SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns

Name Type Description
NAME String A component of SQL syntax, or a capability that can be processed on the server.
VALUE String Detail on the supported SQL or SQL syntax.

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity

Columns

Name Type Description
Id String The database-generated ID returned from a data modification operation.
Batch String An identifier for the batch. 1 for a single operation.
Operation String The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message String SUCCESS or an error message if the update in the batch failed.

Data Type Mapping

Data Type Mappings

The connector maps types from the data source to the corresponding data type available in the schema. The table below documents these mappings.

Microsoft Project (OData V3) Schema
Edm.Binary binary
Edm.Boolean bool
Edm.DateTime datetime
Edm.Decimal decimal
Edm.Double double
Edm.Guid guid
Edm.Int32 int
Edm.String string
Edm.TimeOfDay time

Advanced Configurations Properties

The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.

Authentication


PropertyDescription
AuthSchemeThe scheme used for authentication. Accepted entries are Auto, OAuth, OKTA, ADFS, and OneLogin.
URLURL to the MS Project Web App. For example, https://MyOrganization.sharepoint.com/sites/pwa/.
UserThe Microsoft Project user account used to authenticate.
PasswordThe password used to authenticate the user.

Connection


PropertyDescription
ProjectIdThe ID of the default project. This ID will be used by default for all the operations which require the project Id. Can be overridden by providing a query specific ProjectId filter.
AuthCookieThe cookie to use for authenticating to the online SharePoint server.

SSO


PropertyDescription
SSOLoginURLThe identity provider's login URL.
SSODomainThe domain of the user when using single sign-on (SSO).
SSOPropertiesAdditional properties required to connect to the identity provider in a semicolon-separated list.

OAuth


PropertyDescription
InitiateOAuthSet this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientIdThe client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecretThe client secret assigned when you register your application with an OAuth authorization server.
OAuthAccessTokenThe access token for connecting using OAuth.
CallbackURLThe OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
ScopeThe scopes used for the OAuth flow. Should be separated with a whitespace. Defaults to the following: ProjectAdmin.Manage Projects.Write ProjectResources.Write ProjectReporting.Read. A list of the available scopes and their aliases can be found here: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/authorization-code-oauth-flow-for-sharepoint-add-ins?redirectedfrom=MSDN#permission-scope-aliases-and-the-oauthauthorizeaspx-page.
StateOptional value for representing extra OAuth state information.
OAuthVerifierThe verifier code returned from the OAuth authorization URL.
OAuthRefreshTokenThe OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresInThe lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestampThe Unix epoch timestamp in milliseconds when the current Access Token was created.

SSL


PropertyDescription
SSLServerCertThe certificate to be accepted from the server when connecting using TLS/SSL.

Schema


PropertyDescription
LocationA path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemasThis property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
TablesThis property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
ViewsRestricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.
SchemaThe type of schema to use: ODataV1 or ODataV2.

Miscellaneous


PropertyDescription
MaxRowsLimits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
OtherThese hidden properties are used only in specific use cases.
PagesizeThe maximum number of results to return per page from Microsoft Project.
PseudoColumnsThis property indicates whether or not to include pseudo columns as columns to the table.
TimeoutThe value in seconds until the timeout error is thrown, canceling the operation.
UseSimpleNamesBoolean determining if simple names should be used for tables and columns.

Authentication

This section provides a complete list of authentication properties you can configure.


PropertyDescription
AuthSchemeThe scheme used for authentication. Accepted entries are Auto, OAuth, OKTA, ADFS, and OneLogin.
URLURL to the MS Project Web App. For example, https://MyOrganization.sharepoint.com/sites/pwa/.
UserThe Microsoft Project user account used to authenticate.
PasswordThe password used to authenticate the user.

AuthScheme

The scheme used for authentication. Accepted entries are Auto,OAuth,OKTA,ADFS, and OneLogin.

Possible Values

Auto, SharePointOnlineCookies, OAuth, ADFS, OKTA, OneLogin

Data Type

string

Default Value

"Auto"

Remarks

This field is used to authenticate to Microsoft Project. Auto is the default option.

  • Auto: Lets the Jitterbit Connector for Microsoft Project determine what to do based on other connection properties.
  • SharePointOnlineCookies: Set the User and Password to perform the cookie based authentication to your SharePoint Online server.
  • OAuth: Set this to use OAuth authentication.
  • ADFS: Set this to use Single Sign-On authentication with ADFS.
  • OKTA: Set this to use Single Sign-On authentication with OKTA.
  • OneLogin: Set this to use Single Sign-On authentication with OneLogin.

URL

URL to the MS Project Web App. For example, https://MyOrganization.sharepoint.com/sites/pwa/.

Data Type

string

Default Value

""

Remarks

URL to the MS Project Web App. For example, https://MyOrganization.sharepoint.com/sites/pwa/.

User

The Microsoft Project user account used to authenticate.

Data Type

string

Default Value

""

Remarks

Together with Password, this field is used to authenticate against the Microsoft Project server.

Password

The password used to authenticate the user.

Data Type

string

Default Value

""

Remarks

The User and Password are together used to authenticate with the server.

Connection

This section provides a complete list of connection properties you can configure.


PropertyDescription
ProjectIdThe ID of the default project. This ID will be used by default for all the operations which require the project Id. Can be overridden by providing a query specific ProjectId filter.
AuthCookieThe cookie to use for authenticating to the online SharePoint server.

ProjectId

The ID of the default project. This ID will be used by default for all the operations which require the project Id. Can be overridden by providing a query specific ProjectId filter.

Data Type

string

Default Value

""

Remarks

The ID of the default project. This ID will be used by default for all the operations which require the project Id. Can be overridden by providing a query specific ProjectId filter.

Note: This property is applicable only for the "ODataV2" schema. For the "ODataV1" schema, no table requires the ProjectId to be specified, as the ProjectData endpoints return data for all the available projects.

AuthCookie

The cookie to use for authenticating to the online SharePoint server.

Data Type

string

Default Value

""

Remarks

After logging in to http://yourdomain.sharepoint.com/TeamSite, the "keep me signed in" setting must be checked in order for the connection to be established.

SSO

This section provides a complete list of SSO properties you can configure.


PropertyDescription
SSOLoginURLThe identity provider's login URL.
SSODomainThe domain of the user when using single sign-on (SSO).
SSOPropertiesAdditional properties required to connect to the identity provider in a semicolon-separated list.

SSOLoginURL

The identity provider's login URL.

Data Type

string

Default Value

""

Remarks

The identity provider's login URL.

SSODomain

The domain of the user when using single sign-on (SSO).

Data Type

string

Default Value

""

Remarks

This property is only applicable when using single sign-on (UseSSO is set to true) and if the domain of the User (e.g. user@mydomain.com) is different than the domain configured within the SSO service (e.g. user@myssodomain.com).

This property may be required when using the ADFS, OneLogin, or OKTA SSO services.

SSOProperties

Additional properties required to connect to the identity provider in a semicolon-separated list.

Data Type

string

Default Value

""

Remarks

Additional properties required to connect to the identity provider in a semicolon-separated list. is used in conjunction with the SSOLoginURL.

SSO configuration is discussed further in .

OAuth

This section provides a complete list of OAuth properties you can configure.


PropertyDescription
InitiateOAuthSet this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientIdThe client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecretThe client secret assigned when you register your application with an OAuth authorization server.
OAuthAccessTokenThe access token for connecting using OAuth.
CallbackURLThe OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
ScopeThe scopes used for the OAuth flow. Should be separated with a whitespace. Defaults to the following: ProjectAdmin.Manage Projects.Write ProjectResources.Write ProjectReporting.Read. A list of the available scopes and their aliases can be found here: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/authorization-code-oauth-flow-for-sharepoint-add-ins?redirectedfrom=MSDN#permission-scope-aliases-and-the-oauthauthorizeaspx-page.
StateOptional value for representing extra OAuth state information.
OAuthVerifierThe verifier code returned from the OAuth authorization URL.
OAuthRefreshTokenThe OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresInThe lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestampThe Unix epoch timestamp in milliseconds when the current Access Token was created.

InitiateOAuth

Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.

Possible Values

OFF, GETANDREFRESH, REFRESH

Data Type

string

Default Value

"OFF"

Remarks

The following options are available:

  1. OFF: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.
  2. GETANDREFRESH: Indicates that the entire OAuth Flow will be handled by the connector. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.
  3. REFRESH: Indicates that the connector will only handle refreshing the OAuthAccessToken. The user will never be prompted by the connector to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.

OAuthClientId

The client ID assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.

OAuthClientSecret

The client secret assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.

OAuthAccessToken

The access token for connecting using OAuth.

Data Type

string

Default Value

""

Remarks

The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.

The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.

CallbackURL

The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.

Data Type

string

Default Value

""

Remarks

During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.

Scope

The scopes used for the OAuth flow. Should be separated with a whitespace. Defaults to the following: ProjectAdmin.Manage Projects.Write ProjectResources.Write ProjectReporting.Read. A list of the available scopes and their aliases can be found here: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/authorization-code-oauth-flow-for-sharepoint-add-ins?redirectedfrom=MSDN#permission-scope-aliases-and-the-oauthauthorizeaspx-page.

Data Type

string

Default Value

"ProjectAdmin.Manage Projects.Write ProjectResources.Write ProjectReporting.Read"

Remarks

The scopes used for the OAuth flow. Should be separated with a whitespace. Defaults to the following: ProjectAdmin.Manage Projects.Write ProjectResources.Write.

State

Optional value for representing extra OAuth state information.

Data Type

string

Default Value

""

Remarks

Optional value for representing extra OAuth state information.

OAuthVerifier

The verifier code returned from the OAuth authorization URL.

Data Type

string

Default Value

""

Remarks

The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.

Authentication on Headless Machines

See to obtain the OAuthVerifier value.

Set OAuthSettingsLocation along with OAuthVerifier. When you connect, the connector exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified file. Set InitiateOAuth to GETANDREFRESH automate the exchange.

Once the OAuth settings file has been generated, you can remove OAuthVerifier from the connection properties and connect with OAuthSettingsLocation set.

To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.

OAuthRefreshToken

The OAuth refresh token for the corresponding OAuth access token.

Data Type

string

Default Value

""

Remarks

The OAuthRefreshToken property is used to refresh the OAuthAccessToken when using OAuth authentication.

OAuthExpiresIn

The lifetime in seconds of the OAuth AccessToken.

Data Type

string

Default Value

""

Remarks

Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.

OAuthTokenTimestamp

The Unix epoch timestamp in milliseconds when the current Access Token was created.

Data Type

string

Default Value

""

Remarks

Pair with OAuthExpiresIn to determine when the AccessToken will expire.

SSL

This section provides a complete list of SSL properties you can configure.


PropertyDescription
SSLServerCertThe certificate to be accepted from the server when connecting using TLS/SSL.

SSLServerCert

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description Example
A full PEM Certificate (example shortened for brevity) -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate C:\cert.cer
The public key (example shortened for brevity) -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB

-----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated) ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated) 34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Schema

This section provides a complete list of schema properties you can configure.


PropertyDescription
LocationA path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemasThis property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
TablesThis property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
ViewsRestricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.
SchemaThe type of schema to use: ODataV1 or ODataV2.

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Data Type

string

Default Value

"%APPDATA%\\MicrosoftProject Data Provider\Schema"

Remarks

The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

Note: Given that this connector supports multiple schemas, the structure for Microsoft Project custom schema files is as follows:

  • Each schema is given a folder corresponding to that schema name.
  • These schema folders are contained in a parent folder.
  • The parent folder should be set as the Location, not an individual schema's folder.

If left unspecified, the default location is "%APPDATA%\\MicrosoftProject Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Mac ~/Library/Application Support
Linux ~/.config

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Schema

The type of schema to use: ODataV1 or ODataV2.

Possible Values

ODataV2, ODataV1

Data Type

string

Default Value

"ODataV2"

Remarks

The schemas available are ODataV2 (MS Project's static schema with full CRUD support) and ODataV1 (MS Project's dynamic schema with read-only capabilities).

  • The ODataV1 schema can only be used to read data, but schemas are read dynamically and the custom fields are exposed.
  • The ODataV2 schema can be used to perform read, insert, update, and delete operations, but schemas are fixed. Main fields are dynamically retrieved, however custom fields are not exposed.

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.


PropertyDescription
MaxRowsLimits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
OtherThese hidden properties are used only in specific use cases.
PagesizeThe maximum number of results to return per page from Microsoft Project.
PseudoColumnsThis property indicates whether or not to include pseudo columns as columns to the table.
TimeoutThe value in seconds until the timeout error is thrown, canceling the operation.
UseSimpleNamesBoolean determining if simple names should be used for tables and columns.

MaxRows

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Other

These hidden properties are used only in specific use cases.

Data Type

string

Default Value

""

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting

DefaultColumnSizeSets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMTDetermines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filenameRecords the underlying socket data transfer to the specified file.

Pagesize

The maximum number of results to return per page from Microsoft Project.

Data Type

int

Default Value

400

Remarks

The Pagesize property affects the maximum number of results to return per page from Microsoft Project. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

PseudoColumns

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

int

Default Value

60

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the connector throws an exception.

UseSimpleNames

Boolean determining if simple names should be used for tables and columns.

Data Type

bool

Default Value

false

Remarks

Microsoft Project tables and columns can use special characters in names that are normally not allowed in standard databases. UseSimpleNames makes the connector easier to use with traditional database tools.

Setting UseSimpleNames to true will simplify the names of tables and columns returned. It will enforce a naming scheme such that only alphanumeric characters and the underscore are valid for the displayed table and column names. Any nonalphanumeric characters will be converted to an underscore.

Copyright © 2022-2023 Jitterbit, Inc. All rights reserved.