Connecting your ODBC or SQL Accounting System to Salesmatrix
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.
- Before you start
- Register your company
- Install the service
- Set up your script
- Check it is working
- 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
-
Open the registration page
-
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.
-
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.
-
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.
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.
-
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.
-
Run it as an administrator
Double-click the file, or right-click it and choose Install. Accept the default installation folder.
-
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.
-
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.
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.
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.
-
Open Connection
Sign in at dashboard.salesmatrix.com with your administration email and open Connection from the menu. Only administrators can see this page.
-
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.
-
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 The connection type: ODBC, SQL, ADO or CSV Parameter3 The connection string, e.g. DSN=YourDSNName;UID=username;PWD=password
Leave out UID and PWD if your DSN stores them.Parameter4 CSV only: YES if the file has a header row, otherwise NO. Leave empty for the other types. Most systems connect through ODBC, as in the example above. If yours is different, see Connection types and Example connection strings below.
-
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 -
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
Connection types
Parameter2 of Stage 1 tells the service how to reach your data, and Parameter3 is the connection string for that type.
| Parameter2 | Use for | Parameter3 | Parameter4 |
|---|---|---|---|
| ODBC | Any system with an ODBC driver. This is the most common choice, e.g. Attaché. | A DSN (DSN=…), or a driver connection string (Driver={…};…) | leave empty |
| SQL | Systems that keep their data in Microsoft SQL Server, e.g. Jiwa, MYOB Exo, SAP Business One, Sybiz and Microsoft Dynamics. No ODBC driver or DSN is needed. | A SQL Server connection string | leave empty |
| ADO | Sources with an OLE DB provider, e.g. a Microsoft Access database | An OLE DB connection string (Provider=…;…) | leave empty |
| CSV | CSV files exported from your system into a folder | The full path of a CSV file in that folder | YES if the first row holds column names, otherwise NO |
The service runs as a 32-bit program, so any ODBC driver or OLE DB provider it uses must be the 32-bit version. If your system offers both, install the 32-bit one, and copy driver names from the Drivers tab of ODBC Data Sources (32-bit). SQL connections need no driver at all.
Example connection strings
Replace the example names, paths and passwords with your own. The service only reads your data, so a database user with read-only access is enough, and safer.
ODBC with a DSN. Use the DSN's exact name from ODBC Data Sources (32-bit).
Parameter2 ODBC Parameter3 DSN=AttacheData or, if the DSN does not store a login: DSN=AttacheData;UID=swot_reader;PWD=YourPassword
ODBC without a DSN. Name the driver directly. For example, Visual FoxPro tables in a folder:
Parameter2 ODBC Parameter3 Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=C:\Data\;Exclusive=No;Collate=Machine;NULL=No;DELETED=Yes;BACKGROUNDFETCH=No
Or a MySQL database (e.g. Fishbowl), using the exact name of the MySQL driver you installed:
Parameter2 ODBC Parameter3 Driver={MySQL ODBC 8.0 Unicode Driver};Server=localhost;Port=3306;Database=fishbowl;Uid=swot_reader;Pwd=YourPassword
SQL Server. For a named instance, add it to the server name, e.g. SQLSERVER01\SQLEXPRESS.
Parameter2 SQL Parameter3 Server=SQLSERVER01;Database=JiwaData;User Id=swot_reader;Password=YourPassword
The service runs under the Windows system account. With Windows authentication (Integrated Security=SSPI), it connects as the computer itself rather than as you, and that only works if your database administrator has given this computer's account access. A SQL Server login, as in the example, avoids this.
ADO (OLE DB). A Microsoft Access database, using the Access Database Engine (32-bit):
Parameter2 ADO Parameter3 Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\Sales.accdb or, for an older .mdb file, with the provider built into Windows: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Sales.mdb
CSV files. Point Parameter3 at one file in the export folder:
Parameter2 CSV Parameter3 C:\Exports\SWOT\customers.csv Parameter4 YES
In each data stage, either leave Command empty to read the file named in Stage 1, or select from any other file in the same folder by its file name, e.g. SELECT * FROM [products.csv].
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.
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. It must be exactly ODBC, SQL, ADO or CSV. See Connection types.
SWOT Data Transfer Service 2.0.0.0 · Salesmatrix · service SWOTDataTransferService