SQL Provider

If you want to import data from a View on an SQL server you can do so with the dedicated SQL Provider with Views. All settings are the same as for the regular SQL Provider but the table dropdown also includes Views. 

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.

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

Figure 2.1 When used as a source provider, the following settings are available

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

SQL Timeout

When reading from an SQL source, you may experience timeouts on the request. To extend the time available to you, you can add a value to /GlobalSettings/DataIntegration/SQLSourceTimeout and overriding SqlCommand.Timeout.

This part of GlobalSettings is not applied by default, so you may need to apply both nodes and value (Figure 2.3). The DataIntegration section should be just below the Globalsettings node.

Figure 2.3 Customizing SQL Timeout settings

When using a SQL Server view as your source, you should make sure that the datatype of your source key column matches the datatype of your destination key column. For instance, if your source key column is of type INT and your destination key column is of type NVARCHAR, there will not be a key match. This will cause for  new row being added, instead of updating an existing row, which you would have been expecting.

If key columns are not of the same type, use a CAST directly in your view to obtain same datatype as in Dynamicweb.

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

Figure 4.1 When used as a destination provider, these are the options available to you

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
  • You can discard duplicates

If you check the remove missing rows after import box, rows that exist in the destination not in the source will be automatically removed after import.

All tables/columns in source/destination database.

Special requirements

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 source key column is of type NVARCHAR, the keys will not be matched and you will experience duplicate rows.

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.