Table of Contents

SQL provider

Import and export data to and from Microsoft SQL server databases

The SQL Provider allows you to add or extract data directly to and from Microsoft SQL server databases. The schema is retrieved automatically.

The SQL Provider relies on primary keys being defined on the destination tables, in order to insert/update rows as needed.

When used as a destination, the SQL Provider creates temporary tables during data transfer. These temporary tables are named “****Temp”, where "****” is the name of the table that the data is being imported to. If a table with the temporary name already exists, it will be overwritten.

The SQL provider also supports importing data from a View.

Used as a source

When used as a source provider, the following settings are available for the SQL Provider.

SQL provider as source

To connect to an SQL source, you must:

  • Specify the hostname/IP in the source server field
  • Enter the username and password of the source server in the username and password fields – or select use integrated security to connect to source server, in which case you can ignore the username and password fields
  • Specify a SQL source database to connect to
  • For complex setups, you can enter a full connection string in the SQL source connection string field – this option overrides both the username/password and integrated security fields

Used as a destination

When used as a destination provider, the following settings are available for the SQL Provider.

SQL provider as destination

To export data to an SQL server, you must:

  • Specify the hostname/IP in the source server field
  • Enter the username and password of the source server in the username and password fields – or select use integrated security to connect to source server, in which case you can ignore the username and password fields
  • Specify a SQL source database to connect to
  • For complex setups, you can enter a full connection string in the SQL source connection string field – this option overrides both the username/password and integrated security fields

If you check the Remove missing rows after import box, rows that exist in the destination but not in the source will be automatically removed after import. You can also select to discard duplicate rows.

Which tables and columns?

All tables/columns in source/destination database.

Key columns must be set for destination tables.

Key columns should contain the same data type. If source key column is of type Int, and destination key column is of type NVARCHAR, the keys will not be matched and you will experience duplicate rows.

Automatic table sorting

When a job using the SQL provider as destination is run, the table imports are automatically executed in accordance with the underlying database schema. This means that the SQL provider checks the foreign keys of the established table mappings to determine if one table should be executed after another, in order to maintain proper data relations.

Improve this doc
To top