Microsoft SQL Server Integration - Import Resource Plans

This article details the configuration steps of the Microsoft SQL Server to OnePlan integration to import resource plan data into OnePlan. Resource plan data is the time phased resource allocation data for specific plans in 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_ResourcePlans.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"). 

Hours

Positive values are accepted up to 2 decimal places. Should there be more than 2 decimal places the values will round up visually in the Resource Planner.

Resource Plan Custom Fields 

You can set values for custom Resource Plan fields directly within the integration.

Custom fields for the resource plan will not appear automatically in the field mappings section. To ensure fields are updated/generated correctly, the column name in the source data must exactly match the Custom Field name, including sensitivity.

Identifying Plans

The plan that is associated with the source data must be identified by ID. In order to do that a so called "Plan External Id" must be setup. This is a field that is used to identify a plan using unique ID. 

OnePlan Configuration

Set Plan External ID

Step 1: Create the Custom Field

1. In OnePlan, go to Settings (gear icon) > Fields.
2. Select Plan Fields, then click New Field. 
3. Name something like "Plan External Id". 
4. Set the field type to Text. 

Step 2: Assign as External ID field

1. In OnePlan, go to Settings (gear icon). 
2. Go to Resource Plan > Advanced. 
3. Use the External Id Field dropdown to select your new custom field. 
4. The selection saves automatically. 

Note: This field is now used by OnePlan to identify plans for syncing. 

Step 3: Add field to Plan Details (Optional)

Add the custom field to the Plan Details form so it can be located easily and updated manually if needed. 

1. In OnePlan Configuration page, navigate to Plan Details. 
2. Select a Plan Type you want this field added to.
3. In the far-right column labeled Fields, find your custom field. 
4. Drag and drop the field into the desired section of the Plan Details layout. 
5. Click Save next to the Plan Type dropdown. 

OneConnect Configuration

Field Mappings

BulkResrcImport - ResourcePlans

While any number of fields can be mapped for the import, at minimum, these fields must be mapped for the data to come into OnePlan: Plan External Id, Resource, Type Name, Hours, and Fiscal Month Period.

Example for this mapping:

Strategies

SQLTableToResourcePlanner

This strategy imports records from SQL database as time phased resource plan data 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.ResourcePlans" table or view, and process each record, and bring over any values mapped to OnePlan. 

• OnePlan_ResourcePlans.sql317 Bytes