SQL Provider (with Views)

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.

Used as a source

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.

Used as a destination

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

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.

Which tables & columns?

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.