On this page:

• Prerequisites
• GUI Data Transfer tool overview
• Export data from MSSQL
• Connect to MSSQL instance by name
• Use Windows Authentication with MSSQL and WiltonDB
• Import data into WiltonDB
• Command-line interface to Data Transfer tool

1. Install WiltonDB
2. Setup TDS connection from SSMS
3. Changing TDS port from 1433
4. Schema import with SSMS
5. Data import with bcp

bcp utility is a robust tool, but, being a CLI tool with numerous flags, it may be cumbersome to use outside of scripting.

WiltonDB Data Transfer tool is a user-friendly GUI wrapper for bcp. It does not intend to be a comprehensive wrapper, its primary usage is to transfer data from MSSQL to WiltonDB. It effectively covers one narrow use case of bcp functionality, when all records from specified tables are exported in binary (native) format and can be imported into destination tables with the same name.

Data Transfer tool requires Microsoft Visual C++ Redistributable to be installed (direct download link: VC_redist.x64.exe).

Note: this GUI tool requires bcp utility (and the ODBC Driver bcp depends on) to be installed and added to PATH system environment variable.

In this example we will use AdventureWorks sample database that is restored into MSSQL running locally on port 6433.

Install bcp utility (and the correct version of ODBC driver for it), download the latest version of wdb_transfer.exe, run it, enter MSSQL connection parameters and press "Load DB names" button:

After database names are loaded, choose "AdventureWorks2019" in a drop-down list, table names and estimated number of rows in them will be displayed:

We are choosing Sales.SalesOrderDetail and Production.WorkOrder tables that contain 121317 and 72591 rows. Double click corresponding rows to change the "Export" column to YES, choose destination directory and press "Run data export" button:

Export file is a ZIP archive that contains compressed binary records and a format file for each exported table:

Note: the number of rows displayed for each table is an estimated number obtained from DB system catalogs. Getting actual number of rows from many tables is a relatively expensive operation. This estimated number may be wildly different from the actual number. When export is done from WiltonDB, the following PostgreSQL-specific notes are applicable.

MSSQL allows to connect to a DB instance by specifying an instance name (like SQLEXPRESS) instead of a TCP port number. In this case SQLServer Browser service is used to obtain the correct TCP port name before establishing the connection:

To obtain TCP port number wdb_transfer utility requires the SQLServer Browser service to be running and to be accessible over UDP protocol in port 1434. Otherwise the following error is reported:

wdb_transfer also requires the TCP protocol to be enabled in SQL Server Configuration Manager:

Otherwise the following error is reported:

Windows Authentication can be used instead of login/password. To use it enable "Use Windows Authentication" checkbox:

Note, that for Windows Authentication to work it may be necessary to specify the "hostname" not as an IP address, but as a Windows machine name. Otherwise connection check and database names load may pass, but bcp.exe invocation may fail with "Cannot generate SSPI context" error.

-T flag is used when bcp.exe is run with Windows Authentication enabled.

To use Windows Authentication with WiltonDB it is necessary first to create the appropriate DB logins, see detail on Windows Authentication wiki page.

In this example we use WiltonDB running locally on port 1433 with AdventureWorks2019 DB schema already imported (see Schema import with SSMS for details).

Choose "File" -> "DB Connection" to open connection dialog, enter connection parameters and press "Load DB names" button:

When DB names are loaded, open "Import" tab and choose the exported file and the destination DB adventureworks2019:

Double click the tables to change "Import" column to YES and press "Run data import" button:

After import is complete we can browse imported data in SSMS:

Beginning with version 1.4.0 data transfer tool is additionally provided as a command-line utility wdb_transfer_cli.exe that can export all tables from the specified DB or import all table files from the specified ZIP file.

Usage:

> wdb_transfer_cli.exe -h
Data transfer tool for WiltonDB

Usage: wdb_transfer_cli.exe [OPTIONS] --hostname <hostname> --database <database> <command>

Arguments:
  <command>  Specifies the task to perform, either 'export' or 'import'

Options:
  -s, --hostname <hostname>        Specifies the hostname of the DB to which to connect.
  -p, --port <port>                Specifies the TCP port of the DB to which to connect.
  -n, --instance <instance>        Specifies the instance name of SQL Server to which to connect.
  -u, --username <username>        Specifies the login name used to connect to DB.
  -x, --password <password>        Specifies the password for the login ID. If this option isn't used, the password is read from WDBTRANSFERPASSWORD environment variable.
  -w, --windows_auth               Specifies that the tool connects to DB with a trusted connection using integrated security.
  -d, --database <database>        Specifies the database to connect to.
  -c, --check_certificate          Enforce the validation of the DB server certificate.
  -i, --input_file <input_file>    Specifies the path to input data file.
  -o, --output_file <output_file>  Specifies the path to output data file.
  -r, --overwrite_output_file      Overwrite existing output file.
  -h, --help                       Print help
  -V, --version                    Print version

Export data from WiltonDB or MSSQL connecting by username and password:

> wdb_transfer_cli.exe export -s 127.0.0.1 -p 1433 -u wilton -x wilton -d db1 -o db1.zip

Loading tables ...
dbo.tab1 2 rows
dbo.tab2 3 rows
Running export ...
[...]
Zipping destination directory ....
db1/
db1/dbo.tab1.bcp.zstd
db1/dbo.tab1.xml
db1/dbo.tab2.bcp.zstd
db1/dbo.tab2.xml
Export complete

Export data overwriting the output file (-r switch):

wdb_transfer_cli.exe export -s 127.0.0.1 -p 1433 -u wilton -x wilton -d db1 -o db1.zip -r

Export data using DB password from WDBTRANSFERPASSWORD environment variable:

set WDBTRANSFERPASSWORD=wilton
wdb_transfer_cli.exe export -s 127.0.0.1 -p 1433 -u wilton -d db1 -o db1.zip

Export data connecting to WiltonDB or MSSQL using Windows Authentication (-w switch):

wdb_transfer_cli.exe export -s DESKTOP-JS80C9O -p 1433 -d db1 -o db1.zip -w

Export data from MSSQL named instance (-n option):

wdb_transfer_cli.exe export -s 127.0.0.1 -n SQLEXPRESS -u mylogin -x mypwd -d db1 -o db1.zip

Import data into WiltonDB or MSSQL (empty schema must be pre-created in the specified DB):

> wdb_transfer_cli.exe import -s 127.0.0.1 -p 1433 -u wilton -x wilton -d db1 -i db1.zip

Loading tables ...
dbo.tab1 19 B
dbo.tab2 19 B
Running import: db1.zip ...
[...]
Cleaning up work directory ....
Import complete