🔗 MS SQL: Setup and Connection
1. How It Works
The MS SQL database connector empowers you to craft tailored SQL queries to map your data objects directly into Optiply.
The Goal: To retrieve essential inventory and sales data and systematically arrange it, ensuring seamless synchronisation with Optiply's integration platform.
Flexibility: You can create your datasets using either Tables or Views, provided they use the exact names and structural requirements outlined below.
2. Query Names and Descriptions
When crafting your SQL queries (or naming your tables/views), you must utilise the following fixed query names. These names correspond to the specific data categories essential for synchronisation.
Fixed Query Name | Description |
Products | Retrieves detailed information about products, including identifiers, names, and specifications. |
SalesOrders | Gathers all sales order data, encompassing order IDs, dates, and order details. |
SalesOrdersLines | Fetches line item details for each sales order, such as product ID, quantity, and price. |
Suppliers | Collects information on suppliers, including supplier IDs, names, and contact details. |
SupplierProducts | Obtains details on products supplied by each supplier, including product IDs and supplier-specific data. |
BuyOrders | Retrieves purchase order information, covering order IDs, purchase dates, and supplier IDs. |
BuyOrdersLines | Fetches line item details for each purchase order, including product ID, quantity, and cost. |
ItemDeliveries | Gathers data on the delivery of items (receipts), including delivery IDs, dates, and statuses. |
ProductCompositions | Retrieves component details and quantities for each product and its parts. This is essential for accurate inventory and production management of bundled/manufactured items. |
3. Query Usage & Incremental Data Synchronisation
Query Usage: You must utilise tailored SQL queries for each of the exact names listed above to extract the necessary data. These are designed to map data objects directly into Optiply's expected format.
Replication Key: To ensure efficient and incremental data synchronisation, each query utilises a specific column name as its Replication Key. This key is essential for identifying new or updated records since the last synchronisation point. Using a replication key optimises data transfer and minimises the load on your database.
4. Requirements to Connect
Before starting the setup in Optiply, ensure you have the following credentials ready for your MS SQL database:
Host Name
Port
Database (Name)
Schema
User (Username)
Password
5. Optiply Connection Steps
Once your queries/views are prepared and you have your credentials, follow these steps to connect your source system to Optiply:
Log in to your Optiply application.
Navigate to Settings → Integrations in the left-hand menu.
Click the Add a source button.
Select Microsoft SQL from the list of integrations and click Next.
Fill in the 6 required credentials (Host Name, Port, Database, Schema, User, Password) in the connection form.
Click Connect.
That is it! You have successfully connected Optiply to Microsoft SQL.
❓ Frequently Asked Questions (FAQs)
Can I use Views instead of Tables for this integration?
Yes! You can create your datasets using either standard tables or views, as long as they match the exact names and structural requirements needed by Optiply.
Why is a "Replication Key" required?
A replication key is a specific column used to identify new or updated records since your last sync. This allows Optiply to perform an incremental sync rather than pulling your entire database every time, which optimises data transfer and minimises the load on your server.
Can I name my SQL queries or tables whatever I want?
No. You must utilise the fixed query names provided in the documentation (e.g., Products, SalesOrders, Suppliers). Optiply's integration platform specifically looks for these exact names to map the data categories correctly.