Microsoft SQL Server Integration - Integration Setup

This integration aims to synchronize data between Microsoft SQL Server and OnePlan.

Data that can be loaded from Microsoft SQL Server are:

• Plans - Project names, statuses, and more by running the SQLTableToPlans strategy. 
• Resources - People, departments, and more by running the SQLTableToResources strategy. 
• Resource Plans - Add resources to cost types within your plans by running the SQLTableToResourcePlanner strategy. 
• Financial Plans - Add financial information across multiple cost types by running the SQLTableToFinancialPlanner strategy. 

OnePlan

To complete the OnePlan setup, you need to be an admin (user with Owner permissions) in OnePlan.

1. Log into your OnePlan group using your OnePlan login credentials.
2. Access the Admin pages by clicking on the settings (gear) icon at the top right of the browser.  
3. Navigate to the Integration section in the left-hand navigation panel.
4. Next to the Authentication Keys section click the + Add Key button.
5. Provide a descriptive name or title for the Authentication Key to denote its purpose, then click ADD. 
6. Store the OnePlan group name and the Authentication Key securely. This grants access to your OnePlan group for any program. Please note that the key will no longer be visible after the page is refreshed.

Note: It's recommended to generate a new authentication key for every integration and assign a descriptive name to each key, enabling clear association with its corresponding integration. This practice enhances security, simplifies management, and facilitates easier tracking and troubleshooting.

SQL Server Setup

The integration pulls data from specific tables in your SQL Server database. These must exist within the expected schema before you proceed with setup. 

Use this table and the scripts provided at the bottom of the page to prepare:

OneConnect Setup Process

To complete the OneConnect setup, you need have Administrator or Editor privileges in the OneConnect group you wish to set the integration up in.   

1. Go to https://my.oneconnect.ai (or https://eu.oneconnect.ai for European customers utilizing eu.oneplan.ai).
2. Navigate to SQL Server to OnePlan. Click on Configure.

OnePlan Connector

Connecting to OnePlan involves three key sections, each with its associated fields. 

1. Connect to OnePlan - 
   1. Optional: Group Name: The name of the OnePlan Group you intend to integrate with.
   2. Authentication Key: Paste the generated OnePlan Authentication Key obtained from OnePlan. 
   3. Optional: Alternate OnePlan Environment URL: If using an environment other than the default environment, select the appropriate URL from the available options. Located in Additional Options. 
2. Additional Integration Settings - These are options relating to the integration that might fulfill your needs.
   1. Do not remove existing data: To be used when running the SQLTablesToResourcePlanner strategy. Located in Additional Options. 
      • Unchecked: All previously imported data will be deleted from OnePlan and replaced with the data from SQL. 
      • Checked: Only the data in SQL will be updated in the Resource Planner. Any existing data for the same resource in other months will remain unchanged. 
   2. Select Delete Scope: This setting is only used when "Do not remove existing data" is unchecked. To be used when running the SQLTablesToResourcePlanner strategy. Located in Additional Options. 
      • All Resources: Removes all resources from the Resource Planner before replacing the data with the SQL data. 
      • Included Resources: Only removes and replaces resources listed in the SQL table, leaving other data intact. 
   3. Process Zero Hours: Works with the "Included Resources" delete scope to allow full resource removal via SQL. To be used when running the SQLTablesToResourcePlanner strategy. Located in Additional Options. 
      • To completely remove a resource from a plan, include a row with the relevant data and set the hours value to 0.
3. Test & Save - Confirm the connection to OnePlan authenticates and save your settings. Located at the bottom of the connector.  
   1. Test the connection to verify correct settings. A notification indicating Connected Successfully should appear in the top-right corner of the browser upon successful testing.
   2. Save the configuration to continue with the SQL Server connection. 

SQL Server Connector

Connecting to SQL Server involves three key sections, each with its associated fields.

1. SQL Server Information - For the integration to connect to SQL Server, the Server and Database information must be entered. 
   1. Server: The name of the SQL server that will host the data.
   2. Database: The name of the database that will host the tables that will be used. 
2. Authentication - 
   1. Username and Password: Ensure that the provided username does not have Multi-Factor Authentication (MFA) associated with it. It is recommended to use a dedicated service account to access SQL Server. Account mist have read/write access to the database. 
   2. Optional: Authentication Method: This integration supports SQL Password and Active Directory Password. Windows Authentication (NTLM) is currently not supported. Located in Additional Options.
      1. SQL Password (Default): Default option for on-premises setups or when using Azure as a Service.
      2. Active Directory Password (Cloud): (aka Microsoft Entra ID) Utilizes OAuth 2.0 for authentication. Client must ensure setup and functionality before configuring this field. 
3. Test & Save - Confirm the connection to SQL authenticates and save your settings. These are at the bottom of your connector. 
   1. Test the connection to verify correct settings. A notification indicating Connected Successfully should appear in the top-right corner of the browser upon successful testing. 
   2. Save the configuration to finalize setup. 

• OnePlan_Plans.sql344 Bytes
• OnePlan_ResourcePlans.sql317 Bytes
• OnePlan_FinancialPlans.sql446 Bytes
• OnePlan_Resources.sql584 Bytes