Practical Guide to Querying OA Department Info: Pulling Department Master Data from SQL Server to Qeasy

What This Strategy Solves

In many supply-chain integration projects, department master data is the "foundation" that every downstream document depends on: sales orders carry the department code, purchase requisitions carry the cost center, and stock transfers carry the responsible department. This "Query OA Department Information" strategy tackles exactly that foundation problem—stably pulling the department table from the OA system through SQL Server and landing it on the Qeasy data integration platform, so it can serve as the canonical source for downstream systems such than KinDian Cloud.

Data Flow and Field Mapping

The data flow is unidirectional: SQL Server (relay database) → Qeasy Integration Platform. The key fields at both ends are described below.

The source side (SQL Server, WebAPI query interface, POST) is responsible for pulling data from the OA department table HrmDepartmentDefined. The main input is an object-typed main_params (required), and otherRequest carries a free-form SQL field main_sql with the value select * from HrmDepartmentDefined. The response fields are mainly driven by _autoFillResponse; typical outputs include id, deptid, bmfzr and other department master fields, all declared as non-required in the metadata so the platform can auto-fill them from the actual response.

The target side (Qeasy Integration Platform) is a "Write-NoOp" node. Its api is set to the no-op write action, effect is EXECUTE, and it carries no request or response body. It does not persist business data by itself; its real value is acting as the "landing marker" for the entire chain, so the upstream SQL Server query result enters the platform's event stream and can be consumed, transformed, and redistributed to KinDian Cloud by follow-up strategies.

How to Configure It on Qeasy

Configuring this strategy on the Qeasy data integration platform centers on wiring the source "query-style WebAPI" and attaching the target as a no-op landing point.

Source configuration: choose SQL Server as the platform, set the interface type to WebAPI, set effect to QUERY, use POST for method, point both number and id at the id field, disable idCheck, and enable both buildModel and autoFillResponse. With these two auto switches on, the response fields build the model automatically, which saves a lot of manual mapping. For an object-typed input like main_params, fix it to a placeholder object to avoid null pointers.

Target configuration: choose the Qeasy integration platform itself, set api to "Write-NoOp", set effect to EXECUTE, set both number and id to 0, enable idCheck, disable buildModel, and leave the request and response bodies empty. This "no-op node" pattern is a very common landing pattern on Qeasy: it deliberately "catches" the data and hands it over to subsequent transformers or distribution strategies.

Implementation Steps

Step 1: Reconcile the incremental starting point. Department master data does not change frequently, but deletions and adjustments are common. Run main_sql for a full snapshot first to confirm the current state of HrmDepartmentDefined before deciding the incremental key.

Step 2: Configure the full-volume trigger. Set the strategy's crontab to 0 0 * * * (daily at midnight) to establish a complete baseline on the Qeasy side; set the target's crontab to 23 2 * * * (next day at 2 AM), leaving a two-hour window for the upstream.

Step 3: Determine the scheduling frequency. Daily granularity is enough for department data; hourly scheduling only adds unnecessary write pressure. After going live, observe the response body size for one week, then lock the configuration once it stabilizes.

Step 4: Wire upstream and downstream. This step is outside the strategy itself, but it must be reserved: department events landed on Qeasy need to be consumed by downstream "Employee Sync" and "Customer Sync" strategies, forming a "master data → business documents" distribution chain.

Pitfalls and Lessons Learned

First, embedding SQL directly in main_sql is risky. We once saw a customer site where someone changed select * from HrmDepartmentDefined to a version with a WHERE clause, used string concatenation, messed up the quote escaping, and ended up locking the entire table. The safe approach is to extract main_sql into Qeasy's variable management so changes go through review.

Second, disabling idCheck on the source does not mean skipping deduplication. The source turns off idCheck because the id field in the response may be empty, but the target must enable it; otherwise duplicates will pollute the master data in the downstream KinDian Cloud.

Third, a no-op node cannot really be "empty". A typical mistake is to assume "Write-NoOp" needs no configuration. In practice, when the request body is not declared, the platform cannot generate the event schema, and the downstream transformer gets no fields. Even when the payload is empty, explicitly declare the request and response array structures so the model is readable.

Fourth, do not turn off buildModel and autoFillResponse at the same time. These two switches are very helpful capabilities in an integration platform like Qeasy: one builds the model from the request body, the other from the response body. Turning both off forces fully manual field mapping, and the maintenance cost skyrockets later.

Fifth, cross-timezone scheduling must account for windows. The source at midnight and the target at 2 AM are written for the same timezone; if the customer has overseas branches, the timezone must be explicitly noted on the crontab, otherwise during the first week of go-live you will see the classic "data is delayed by two hours" mystery.

When to Use and When Not

This strategy is suitable for: master data, low-frequency changes, source systems with a stable query interface, and scenarios where data must be uniformly landed on Qeasy before being redistributed downstream—typical examples are departments, positions, and cost centers. It is not suitable for: high-frequency business documents, inventory or transaction data that require transactional consistency, or source systems without a stable query interface that instead require CDC.