Page background
Support item image

Connecting your ODBC Accounting System to Salesmatrix

Salesmatrix · How-to

If your accounting or ERP system runs on your own server rather than in the cloud, SWOT collects its data through a small Windows service called the SWOT Data Transfer Service. This guide takes you from registering your company, through installing the service, to setting up the script that tells it what to send.

Time needed about 30 minutes Service version 2.0.0.0 Dashboard dashboard.salesmatrix.com
Contents
  1. Before you start
  2. Register your company
  3. Install the service
  4. Set up your script
  5. Check it is working
  6. Troubleshooting

Before you start

Have these ready. The service cannot send anything until all of them are in place.

  • A Windows PC or server inside your network that can reach your accounting database. It must be 64-bit and must stay switched on overnight, because that is when SWOT collects your data.
  • Local administrator rights on that machine, to install the service.
  • .NET Framework 4.7.2 or later. Windows 10 (version 1803 onwards), Windows 11 and Windows Server 2019 onwards already include it.
  • An ODBC data source (DSN) for your accounting database, created with the ODBC driver your accounting system supplies. See the note under step 3 of Install the service.
  • Your SQL queries for customers, products, transactions and contacts. Salesmatrix has starter queries for many systems, including Attaché, MYOB Exo, Sage, SAP Business One, Sybiz, Accredo and Microsoft Dynamics. Ask us for yours.

Register your company

  1. Open the registration page

    Go to dashboard.salesmatrix.com/Register/SWOTRegistration.

  2. Enter your company details

    Fill in Company Name, Administration Email Address, Contact person name and, optionally, Contact No. Country is filled in from your location; change it if it is wrong.

    Use an email address you can check now. The administration address receives your registration token, and becomes the first administrator login for your dashboard.

  3. Choose your accounting system

    Pick your system from Accounting Systems. For a system that runs on your own server, the form does not ask for any keys and does not show Test Connection. That is expected, because the connection is made by the service you install next.

  4. Register and confirm your email

    Click Register. You are taken to the Confirm Registration page. Copy the token from the email we send you into Enter token received on email and click Confirm Token. You are then taken to the sign-in page.

You will receive two tokens

The token in your registration email only confirms your email address. Salesmatrix sends you a separate company token for the Data Transfer Service, which you need in the next section.

Treat the company token like a password. Anyone who has it can send data into your SWOT account.

Install the SWOT Data Transfer Service

Do this on the machine that can reach your accounting database.

  1. Download the installer

    Download SWOTServiceSetup 2.0.0.0 (Windows installer, .msi).

    You can also download it from the Salesmatrix downloads page, under Salesmatrix Dashboard Data Upload Service.

  2. Run it as an administrator

    Double-click the file, or right-click it and choose Install. Accept the default installation folder.

  3. Enter your company token

    On the SWOT Agent Configuration screen, replace Enter Token here with the company token Salesmatrix sent you. Every other setting is configured for you. Finish the installer.

  4. Start the service

    The installer does not start the service the first time. Press Windows+R, type services.msc and press Enter. Find SWOTDataTransferService, right-click it and choose Start. From now on it starts by itself whenever the machine starts.

Create a 32-bit System DSN

The service runs as a 32-bit program under the Windows system account. It can only see an ODBC data source created in ODBC Data Sources (32-bit) on the System DSN tab, using your accounting system's 32-bit ODBC driver. A User DSN, or one created in the 64-bit tool, gives a "data source name not found" error. You will need the DSN's exact name in the next section.

Updates are automatic

The service checks for new versions and installs them itself. You do not need to download this installer again.

Set up your script on the Connection page

The script tells the service how to reach your database and which queries to run. It is stored in your dashboard, not on your server, so you can change it at any time without touching the machine. The service picks up the new version on its next run.

  1. Open Connection

    Sign in at dashboard.salesmatrix.com with your administration email and open Connection from the menu. Only administrators can see this page.

  2. Use Advanced Options

    The page has two tabs. Use Advanced Options, which is the only tab that can hold the connection stage your script needs. Each stage is listed as Stage number, type and comment. Click a stage to expand it.

  3. Add the connection stage

    Click Add Stage and fill it in as below, then click Save Stage. Stage 1 runs first, so every other stage uses the connection it opens.

    Field Enter
    Stage 1
    Type rest
    Comments Connection
    Command leave empty
    Parameter1 leave empty
    Parameter2 ODBC
    Parameter3 DSN=YourDSNName;UID=username;PWD=password
    Leave out UID and PWD if your DSN stores them.
    Parameter4 leave empty

  4. Enter your queries

    Your script already contains a stage for each kind of data. Click the edit icon on each one (Edit Advanced Script), paste the SELECT statement into Command, and save.

    Stage Type Sends Also set
    5 cust Customers nothing else
    10 prod Products nothing else
    20 tran Sales transactions Parameter2 and Parameter3, see the next step
    30 cont Contacts nothing else

  5. Set the transaction date

    The transaction stage sends only what is new since the last import. Give it the name of your transaction date column in Parameter2, and choose how your database stores dates in Parameter3. Do not add a date filter to the query yourself, because the service adds it.

    Parameter3 Use when your dates look like
    ACCPAC Plain 8-digit numbers, e.g. 20261001
    SQLDATE ISO dates, e.g. 2026-10-01
    SQLTEXTDATE Dates stored as text, e.g. '2026-10-01'
    DATETIME Microsoft SQL Server date columns
    TIMESTAMP Date and time together, for drivers that need a full timestamp
    Date Anything else. Uses the standard ODBC date format
Leave Parameter1 empty on the transaction stage

Left empty, the service sends transactions one whole day at a time, which is what SWOT needs to replace a day's figures correctly. A number in Parameter1 splits by row count instead, and can split a day in two so that some of its transactions go missing.

Your finished script looks like this:

Stage 1   rest   Connection    Parameter2 = ODBC   Parameter3 = DSN=YourDSNName
Stage 5   cust   Customers     Command = SELECT ... FROM customers
Stage 10  prod   Products      Command = SELECT ... FROM products
Stage 20  tran   Transactions  Command = SELECT ... FROM invoice_lines
                                Parameter2 = InvoiceDate   Parameter3 = SQLDATE
Stage 30  cont   Contacts      Command = SELECT ... FROM contacts

Check it is working

SWOT collects data once a night, in a time slot between 12:00 and 17:00 UTC (late evening to early morning in Australia and New Zealand). Your first data appears the morning after you finish setting up, as long as the machine is switched on overnight.

  • Open Logs in the dashboard. Each stage records how many records it sent, and each night's run ends with an Update Completed entry.
  • Your customers, products and sales then appear in the dashboard and reports.
Changing the script later

Edit a stage on the Connection page at any time. The change takes effect on the next nightly import. You do not need to restart the service or reinstall anything.

Troubleshooting

Nothing appears in Logs the next morning

Check that the machine was on overnight and that SWOTDataTransferService shows Running in services.msc. If it is running, the company token entered during installation may be wrong. Contact Salesmatrix and we will check it with you.

"Data source name not found"

The service cannot see your DSN. Create it as a System DSN in ODBC Data Sources (32-bit), and check that the name in Parameter3 of Stage 1 matches it exactly.

A stage shows an SQL error

The log entry for that stage includes the query and your database's error message. Fix the query in that stage's Command. The other stages keep running in the meantime.

A stage reports 0 records

The query ran successfully but returned nothing. Run the same SELECT in your database's own query tool to check what it returns. A connection problem shows up as an error, never as 0 records.

Every stage fails with a connection error

The script has no connection stage, or the connection stage has a higher stage number than the data stages, so they run before the connection is open. Make sure Stage 1 is the rest stage.

"Unknown query type"

Parameter2 of Stage 1 is not a value the service recognises. For an ODBC data source it must be exactly ODBC.

SWOT Data Transfer Service 2.0.0.0 · Salesmatrix · service SWOTDataTransferService