SyteLine data migration checklist

Short answer

Treat data migration as a repeatable process, not a one-time export. Extract from a read-only copy, cleanse at the source where you can, map every field in a workbook that the business signs off, validate each load file with scripted checks before it goes anywhere near the target, and reconcile counts and values after every load. Run it as several rehearsal cycles, each cleaner than the last, so the final cutover load is the fourth or fifth time the process has run, not the first.

Decide what to move

Not everything should migrate. Agree this early with the business:

Data	Usually migrated	Notes
Items, BOMs, routings	Yes	The foundation. Most cleansing effort goes here
Customers, ship-tos, vendors	Yes, active ones	Inactive records stay in an archive
Open customer orders and POs	Yes	Only open lines and quantities
On-hand inventory	Yes, at cutover	Loaded last, from a count or a frozen snapshot
Open AR and AP	Yes, as open items	Balances must tie to the GL
GL balances	Opening balances, often by period	Agree the level of detail with finance
Transaction history	Usually not	Keep the old system or a reporting archive for lookups

Moving years of history is expensive and rarely used. A read-only archive is usually the better answer.

Step 1: Extract from a copy

Extract from a restored copy of production, never the live database, using read-only SQL. In SyteLine, read the _mst tables and filter on site_ref. See the SQL table cheat sheet. Keep the extract scripts under version control, because you'll run them every cycle.

Step 2: Cleanse at the source

Fix data in the source system where possible, so it stays fixed for the next extract. The checks that matter most:

Items: obsolete items with no stock, no demand and no use in BOMs, duplicate descriptions, missing unit of measure, product code or cost.
BOMs: components that are obsolete or missing, circular references, quantities of zero.
Customers and vendors: duplicates under slightly different names, missing addresses, tax and payment terms, inactive records with open balances.
Open orders: lines open for years, orders for customers or items that won't migrate.
Units of measure: conversions that don't exist in the target.

A read-only exception report for each check, with an owner per record, keeps the cleanup moving. The data health check covers the same ground.

Step 3: Map every field

Build one mapping workbook, one sheet per object:

Source table.column	Target field	Rule	Default	Owner
`item_mst.item`	Item number	Trim, uppercase	—	Engineering
`item_mst.u_m`	Unit of measure	Map via UoM table	EA	Engineering
`customer_mst.terms_code`	Payment terms	Map via terms table	NET30	Finance

Every transformation is written down, every default is agreed, and every sheet has a business owner who signs it off. This workbook becomes the specification for the load files and the evidence for auditors.

Step 4: Validate every load file before loading

This is the step that saves cutover weekends. Run scripted checks on each load file before it's loaded, and fix the source or the mapping, not the file by hand:

Required fields are present and not blank.
Keys are unique within the file.
References exist: every BOM component exists in the item file, every order's customer exists in the customer file, every ship-to's customer exists.
Values are valid in the target: units of measure, warehouses, sites, terms codes, tax codes.
Lengths and types fit: text not longer than the target field, numbers and dates parse.
No hidden characters: leading and trailing spaces, tabs and non-printing characters.
Load order works: parents before children (customers before ship-tos and orders, items before BOMs and order lines).

Write each check as SQL or a script that outputs a list of failures with file, row and reason. Hand that list to the record owner. Repeat until it's empty.

Step 5: Reconcile after every load

Check	How
Record counts	Source extract vs loaded, per object and site
Totals	Open order value, on-hand quantity and value, AR and AP open balances
Samples	A handful of records checked field by field, including awkward ones
Financial tie-out	Subledgers tie to the GL opening balances

Finance signs off the financial reconciliation, and operations signs off items, BOMs and open orders.

Load through SyteLine, not around it

However the data is loaded, it should go through SyteLine's own import tools, IDOs or the IDO REST API, so validations and business logic run, never straight into the _mst tables with SQL. For single-field corrections after a load, such as a product code on a few hundred items, use the form's Replace Value in Collection rather than an UPDATE. See Connecting external systems with the IDO REST API and Mass update records in SyteLine without SQL.

Step 6: Rehearse, then cut over

Run the whole process, extract to reconciliation, as rehearsal cycles in a test environment. Time each step. By the final cycle the checks should pass first time and the timing should fit the cutover window. Then follow the go-live and cutover checklist.

From experience

On a SyteLine-to-M3 migration I worked on, the data workstream extracted, cleansed, mapped and validated items, BOMs, customers, suppliers, warehouses and orders, with SQL validation scripts run before each load cycle. The business went live with manufacturing and order fulfilment uninterrupted.

FAQ

What data should be migrated from SyteLine?

Usually master data (items, BOMs, routings, active customers and vendors), open orders, open AR and AP, inventory at cutover and GL opening balances. Most transaction history stays in an archive.

How do you validate data before loading it into an ERP?

Run scripted checks on each load file for required fields, unique keys, valid references and values, field lengths and hidden characters, and fix the source until the checks pass.

How many migration rehearsals do you need?

Usually three to five full cycles, so the final load is a repeat of a process that has already worked.

Should we clean data before or after migration?

Before, and at the source where possible, so the fix survives every extract.