Data integration activities

The Data Integration module is a tool for importing and exporting data to and from a Dynamicweb solution.

Using the module you create integration activities which accomplish a particular task, e.g.:

• Exporting users to XML
• Importing users from CRM
• Exporting orders to Business Central via the OData protocol
• Etc.

An integration activity consists of:

• A source provider matching the data source - either the Dynamicweb solution (export) or a file (import)
• A destination provider matching the data destination - either the Dynamicweb solution (import) or a file (export)

Figure 1.1 The Data Integration module

Dynamicweb ships with a set of built-in providers, all of which can function as both source and destination providers.This gives you a lot of freedom when it comes to integration - and if that's not enough you can create custom providers.

Integration activities are created and managed from the Data Integration node (Figure 2.1) – which can be accessed by navigating to Settings > Integration > Data Integration.

Figure 2.1 The Data Integration area

From the Data Integration area you can:

• Create new activities
• Create new activities from a template
• Open the URL builder
• Delete old logs
• View and edit existing activities – and run them!

To create a new integration activity, simply click New activity in the toolbar (Figure 3.1).

Figure 3.1 Creating a new integration activity

This will open a wizard which will take you through all the steps involved:

• Selecting the source provider
• Selecting the destination provider
• Selecting tables to import or export data from
• Naming the activity
• Checking the column mappings
• Saving and running the activity

Read more about providers, tables and column mappings below.

An integration provider is a piece of software for moving data between Dynamicweb and an external data source, like an XML file, a CSV file or an SQL database.

Integration providers are used in integration activities, which are jobs which accomplish something every time they are run, e.g. 'import products from a file' og 'export users to a file'.

An integration activity consists of:

• A source provider matching the data source - either the Dynamicweb solution (export) or a file (import)
• A destination provider matching the data destination - either the Dynamicweb solution (import) or a file (export)

Here's an overview of the built-in providers supplied by Dynamicweb:

Click the links in the table to read in-depth information about each provider – or read about creating custom providers.

The entire import/export tool is built around the concept of mapping tables to tables, and between those tables, mapping columns to columns. This approach is modeled closely on the approach taken by the Microsoft SQL Server Import and Export Data Tool.

After selecting a source and destination provider you will be asked to select source tables (Figure 6.1) and map them to destination tables.

Figure 6.1 Selecting source tables

In this case, I export data to an XML file, and so I cannot select a target table (as the destination file does not exist yet). However, if your destination is a database you will be able to select a target table here. If your destination table has the same name as the source table, it will be automatically selected in the dropdown.

Once you’re happy with your table selection and mapping click finish – you will be prompted to name the activity (Figure 6.2). Do so.

Automatic mapping on run will be explained in the next section.

Figure 6.2 Naming your activity

After naming the activity, you can create or review the column mappings for each table in your import/export (Figure 7.1).

Figure 7.1 Mapping columns

As with source and destination tables, if the column names in the destination table matches those in the source table the mappings will be created automatically.

From the column mapping overview, you have access to a number of advanced options for handling and modifying your import/export activity.

For instance, you can:

• Remove columns (e.g. AccessUserPassword and other sensitive data) which should not be imported or exported
• Add/edit a conditional filter to a table mapping to e.g. export only products with a stock value below 10
• Customize key settings (see important note on key/id recognition related to products here)
• Use scripting to prepend or append something to column data, or replace the source data with a constant
• Choose if a column should be applied on create only. By default the column is always applied but this can be changed by clicking the icon marked on figure 6.1
• On some providers (Xml, SQL, Ecom & Dynamicweb) you can set destination intructions at the table level:
  • Remove missing rows after import
  • Update only existing records
  • Deactivate missing products
  • Discard duplicate key rows

Read more about scripting and conditionals below.

In some cases, it makes sense to schedule an activity to be run at a predefined interval. This is done using the standard Scheduled task functionality in Dynamicweb.

To set up a schedule for an activity, right click the activity in the Data Integration area view and click Schedule task (Figure 9.1).

Figure 9.1 Scheduling an integration activity

Create a scheduled task by selecting a start date and interval in the standard manner.

Scheduled integration activities are run by accessing an auto-generated activation URL – you can access the URL when editing a scheduled task for an integration activity.

The same type of URL can be generated manually and used to run an activity remotely – read more below.

Import/Export activities may be executed remotely by calling a job runner via an url generated from the URL Builder tool. This can be useful if you need to trigger a particular job from e.g. a remote system like Dynamics NAV or AX. 

The URL is generated with the following parameters:

URLs are generated using the URL Builder tool (Figure 12.1) in the Data Integration Module toolbar.

Figure 12.1 Generating and URL for remote execution of data integration activities

To use the tool:

• Select one or more tasks (activities) to include
• Click generate URL. The installation checksum will be added to the token parameter.

The generated URL can be used immediately as is. You can also generate a scheduled task for it, which may be useful if you want a single task for executing several activities sequentially. ​

To settings are available:

• Stop on failed jobs causes the execution to stop if an activity is not completed correctly. Already completed activities will not be reversed – i.e. the data from activity 1 is still imported/exported, even if job 2 fails.
• Synchronous execution causes all included activities to be executed simultaneously instead of sequentially. Please note that this may potentially lead to HTTP Request timeouts. If executed successfully in browser you'll get a content of "See log file: yourjob[date]-[timestamp].log.". If a job succeeds you get a HTTP 200 in return, it it fails a HTTP 500 is returned.

By clicking an existing activity in the Data Integration area, you can open (and edit) the activity details and settings.

For that purpose, you have access to a ribbon bar (1) and table/column mappings area (2) (Figure 13.1)

Figure 13.1 The activity details view

Using the ribbon bar you can:

• Save and run and activity
• Add table and column mappings, remove inactive mappings – or activate the create mappings at runtime option
• Add a new destination table and add columns to a destination table
• Edit the source provider and destination provider settings
• Edit or configure email notification settings
• Access the most recent log file – or review old log files

From the table & column mappings view, you can:

• Use conditionals to filter rows during import/export
• Use scripting to append or prepend something to an input value, or to replace input with a constant
• Customize key values

Read more below.

When mapping tables, you can use conditionals to filter which rows should be affected during import/export – e.g. if you want to export all products with a stock value lower than 10.

NOTICE if using CSV as source: The values of columns are converted to the destination data type AFTER conditionals have been applied. 
As a column data type cannot be explicitly determined when having a CSV file, all source columns are initially treated as alphanumerical.
Hence, conditionals are always applied as such. This means that you cannot successfully put a conditional on a column that is of a numeric data type. 
If you need conditional on numeric column, consider using XML or Excel as source instead.

To set conditionals, click the conditional icon next to a table mapping in your activity overview (Figure 14.1).

Please note that conditionals only work as filters when the column is part of the import.

Figure 14.1 Setting conditionals

In addition to comparing simple values, you use @NULL to match on true DbNull (isNull = “true” in the XML) and @NULLOREMPTY to match null and empty strings (e.g. “” or <! [CDATA[]]> in the XML).

During column mapping, you can use scripting to append, prepend, set constants or auto generate GUIDs when the activity is run.

Click the scripting button to the right of a column mapping and define your script (Figure 15.1).

Figure 15.1 Scripting

Simply select append, prepend or set constant as appropriate – and enter the value you want to append/prepend/create.

Set Constant is mainly used if you want to replace all source values with the constant, or (more likely) to create a destination column with no source column available, e.g. if your ERP system has no language context column and you want to import these products into a LANG1 or LANG2 context in Dynamicweb Ecommerce.

If the data table you wish to import lacks a unique primary key, you can use Generate Guid to auto generate one. The GUID (Globally unique identifier) will be in the format shown in the scripting dialoue (Figure 15.2). The GUID will overwrite what's in the source column when inserting it to the destination. Make sure to select a destination column that functions as a primary key in Dynamicweb or to set it as a custom key (see next section).  

When using the SQL provider or any provider that extends the SQL provider (DynamicWeb - Ecom, User, Order providers) as destination, it is possible to append the content of one source column to the content of another source column and insert the combined value in the destination column.

To do this, simply add two or more column mappings with the same destination column. The content is added top-to-bottom as illustrated in the table below:

Destination content after the activity is run:  "ABC123"

Data Integration activities are store as XML files on a solution, and can be accessed through the file manager.

To move an activity to a different solution, simply move the activity from /Integration/Jobs on one solution to /Integration/Jobs on the target solution (Figure 18.1).

Figure 18.1 Moving an activity to another solution

Please note that all paths to source files (CSV or XML) are static in the activity file and must be manually adjusted if they differ on the destination solution.

A global request timeout can be configured to data integration jobs in Settings > System > Global Settings.

To configure the timeout insert a value (of milliseconds) in /GlobalSettings/Modules/DataIntegration/Job/TimeoutInMilliseconds. The default timeout occurs after 20 minutes. 

Please notice that this global setting is currently only supported in the Endpoint Provider.