Microsoft SQL Server Integration - Import Resources

This article details the configuration steps of the Microsoft SQL Server to OnePlan integration to import resource data into OnePlan.

Source Data Requirements

Setup Database

This is a one-way integration: data is imported into OnePlan but not written back to SQL.
The required data must exist in tables or views within a specific schema.
A sample SQL script is attached to this article (OnePlan_Resources.sql) to demonstrate the required structure.
You can add fields to the script's table/view as needed to reflect your source data.

Identifying Resources

Non-generic resources: Identified by email address.
Generic resources: Identified by Display Name (e.g., "Developer").

Entering Numerical Values

To populate or update any desired resource number fields, ensure that values up to 2 decimal places.

Entering User Values

To populate or update any desired resource user field (i.e., manager), the email should be used.

Entering Multi-Choice Values

To populate or update any desired resource multi-choice fields, please ensure that there is a semicolon + number sign (;#) between the values without spaces (i.e., value1;#value2).

Locating GUIDs for Resource Choice and Multi-Choice Fields

To populate or update any desired resource choice field for your resources, the current and only available option is to locate the GUID.

Open the OnePlan Configuration page by clicking on the Settings (gear) icon.
Go to Fields in the Admin Navigation, expand Resource Fields.
Locate the choice field desired and click on the Edit icon. The Edit Choices form will open.
In the column title, click on the dropdown arrow to expand the options available, select Columns, and select Id. These values are the GUIDs for the specific choice options.

Note: If the GUIDs do not match, no value will populate that field in the Resource Center.

Enable OnePlan App Permissions

These steps will require action from the client's Tenant Administrator to enable app permissions and consent on behalf of the organization.

Log into your OnePlan group using your OnePlan login credentials.
Open the OnePlan configuration pages by clicking on the Settings (gear) icon.
- Please note that some steps listed below should have been covered during Technical Readiness.
Select Users in the Admin Navigation panel.
Expand the AD Synchronization section.
At the bottom of the section, locate: To synchronize users you must enable app permissions: here. Click on the blue here button to allow the tenant administrator to grant permissions.
- The tenant administrator will have to provide their tenant credentials to approve the app permissions on behalf of the organization.
Once the permissions have been granted for step 5, go to the OnePlan configuration page, go to Integration section, locate: To allow integration to this tenant, please make sure you are logged in with a tenant admin and click here. Click on the blue here button to allow the tenant administrator to grant permissions.
- The tenant administrator will have to provide their tenant credentials to approve the app permissions on behalf of the organization.
Once the permissions have been granted for step 6, navigate to https://my.oneplan.ai/account/ConsentIntegration to provide tenant admin consent for the OnePlan group.
- The tenant administrator will have to provide their tenant credentials to approve the app permissions on behalf of the organization.

OneConnect Configuration

Field Mappings

Mandatory Fields

Depending on the type of resource, different fields are required for resource creation:

Resource Type	Required Fields
Non-generic	Display Name, Email
Generic	Display Name, Generic
Mixed	Display Name, Email, Generic

Resources - Resources

While any number of fields can be mapped for the import, at minimum, two fields must be mapped: Display Name and Email.

Field	Purpose
Display Name	Used to find the generic resource to update
Email	Used to find the non-generic resources to update

Example for this mapping:

Strategies

SQLTableToResources

This strategy imports records from SQL database as resources into OnePlan.

By default, this strategy has been set to Enabled, however the Schedule Type has been set to None. This means that the strategy will not trigger on a set schedule and will only run when manually triggered through OneConnect.

This strategy will load the records from the "dbo.Resources" table or view, and process each record, and bring over any values mapped to OnePlan.

Resources are match between the source data and OnePlan, based on the email first and then the full name. If a resource cannot be found in OnePlan, it will be added. If it is found, then it will be updated.

Filters can be applied to this strategy on the SQL Server (v2) - OnePlan section within the Resources - Resources table.

Additional OnePlan Resource Fields

This is the list of available out of the box resource fields available for the Resource Center integration.

Available From	Display Name	Inactive*	Timesheet Administrator
Available To	Email	Manager	Timesheet Manager
Business Unit	Generic*	Rate	Quantity
Cost	Global Permissions*	Role

*If these fields are not mapped, the integration will automatically assume these settings: the resources are not generic, the Enterprise Security Group (Global Permissions) are to be set to the default setting in OnePlan, all resources are active, and all resources are not timesheet administrators.

This integration will not sync to the Compliance, Governance Warnings, and Governance Problems fields, as they are calculated fields that are controlled by OnePlan.

Enterprise Security Groups

To set the Enterprise Security Group for a resource, use the Global Permissions field. This will set the group access for the resource. However, to set the Enterprise Security Group for new resources, the front-end name of the security group is used. For example, if the security group has the name of “Timesheet Users Type 2”, then that is the value that should be entered within the Global Permissions column.

If no value is entered within the Global Permissions column, then the default value set within the OnePlan Configuration page will be set automatically. Please note that to update a resources' Enterprise Security Group, a value must be present in the source data, otherwise the previously set value will stay the same.

Mind the Gap

Integration will not Delete Resources

This integration will not delete Resources. Resources can be created, updated, or marked as inactive.

Resource Access is Only Assigned from Resource Center

This integration will only alter fields that are related to the Resource Center. The ability to update what plans the resource has access to cannot be done from this integration.

To alter what OnePlan Modules/Sections are available to the Enterprise Security Group, please navigate to the OnePlan Configuration page and go to Users. The Enterprise Security Group section will reside there.

User Principal Name (UPN) is not Supported as an Alternative to Email

Currently, only email is used to be queried from the clients Microsoft Entra ID to give the person access to OnePlan. User Principal Name cannot be used as an alternative to the email.

Integration will only bring in Resources Within Microsoft Tenant

The integration will only bring in resources that reside within the clients Microsoft tenant organization. This integration currently does not have the capability to add "outside" resources.

Immutability of User Type and Email in OnePlan

OnePlan lacks the capability to change a generic resource to a non-generic resource or vice versa. Once this value has been set, it remains unalterable.

If the user type is accidentally set incorrectly, we encourage deleting the resource in OnePlan, and resyncing the resource again.

If a resource has a changed or updated email, manual editing within OnePlan must be made to the resource.

Related to

Integration

OnePlan_Resources.sql584 Bytes