This article details the configuration steps of the Microsoft SQL Server to OnePlan integration to import plan 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_Plans.sql) to demonstrate the required structure.
- You can add fields to the script's table/view as needed to reflect your source data.
OnePlan Configuration
Plan External ID
Identifying Plans with External IDs
Each plan must be uniquely identified by a Plan External Id.
- The Id allows OnePlan to match existing plans for updates or create new plans if they don't exist.
- The field must be set up and mapped manually in OnePlan (it is not out-of-the-box).
Step 1: Create the Custom Field
- In OnePlan, go to Settings (gear icon) > Fields.
- Select Plan Fields, then click New Field.
- Name is something like "Plan External Id".
- Set the field type to Text.
Step 2: Assign as External ID Field
- In OnePlan go to Settings (gear icon).
- Go to Financial Plan > Import.
- Use the External Id Field dropdown to select your new custom field.
- 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.
-
In OnePlan Configuration page, navigate to Plan Details.
-
Select a Plan Type you want this field added to.
- In the far-right column labeled Fields, find your custom field.
- Drag and drop the field into the desired section of the Plan Details layout.
- Click Save next to the Plan Type dropdown.
OneConnect Configuration
Field Mappings
Plans - Plans
While any number of fields can be mapped for the import, at minimum, three fields must be mapped for the data to come into OnePlan: Name, Portfolio Plan Type, and the Plan External Id.
| Field | Purpose |
| Name | Used as the name of the Plan |
| Portfolio Plan Type | Used to set the Plan Type |
| Plan External Id | Used to decide if a plan should be updated (if the ID exists) or newly created |
Mapping example:
Strategies
SQLTableToPlans
This strategy imports records from SQL database as plans 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.Plans" table or view, and process each record, and bring over any values mapped to OnePlan.
New plans are created if the External Id does not exist already or existing plans are updated if it does.
This strategy does not have the ability to filter data. All data in the SQL table or view will be processed when the strategy runs. If you need to filter data, you can do that in a database view.
Mind the Gap
Estimated Start/Estimated End
Estimated Start and Estimated End dates in OnePlan are special fields. These dates will change if there are any tasks within the Schedule section of the Work Plan, as those values have a higher precedence than the values within the integration/API call.
Integration Intended for OnePlan Plan Fields
This integration is intended to create and update plans and assign values to the fields listed in the OnePlan Configuration > Fields section.
No other system fields or plan features, such as Favorite, Shared With, Comments, etc., can be updated using this integration.
Exception: The exception to this rule is to update a Name, an Owner, and a Plan Type to the plan.
Not all OnePlan Fields Recommended to Update/Map
Some fields in OnePlan are considered unadvisable to map within the integration, as they incorporate additional backend functionality that may be susceptible to disruptions if modified through an integration. Therefore, it is advised against mapping any plan level field whose function is Calculated, Roll Up, or Trend types.
For plans of the Standard type, the following are the special fields that are not recommended for use.
| Actual Hours | Count | Forecast | Schedule Mode |
| Actuals | Default Task Mode | Governance Problems | Timesheet Charge |
| Benefits | Default Task Type | Governance Warnings | Timesheet Type |
| Budget | Estimated End | Plan Count | |
| Committed Effort | Estimated Start | Program Budget |
Related to
- OnePlan_Plans.sql344 Bytes