WiltonDB Data Transfer GUI tool

On this page:

• Prerequisites
• GUI Data Transfer tool overview
• Export data from MSSQL
• Use Windows Authentication with MSSQL
• Import data into WiltonDB

Prerequisites

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

GUI Data Transfer tool overview

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.

Export data from MSSQL

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.

Use Windows Authentication with MSSQL

Windows Authentication can be used instead of login/password one when performing export from MSSQL. To use it enable "Use Windows Authentication" checkbox and specify the MSSQL instance name to connect to:

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.

Also, when Windows Authentication is enabled, SQL Server Browser service is used to fetch the actual TCP port to connect to specified MSSQL instance. Make sure that SQL Server Browser service is enabled.

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

Windows Authentication is NOT supported with WiltonDB.

Import data into WiltonDB

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: