Alternatives to Applying Code in You Integration Flow

In many cases we need to do some manipulation of the source data before committing it to the product database, so we use a TableScript to apply a bit of C# magic to perfect the data. In other schenarios we might use a NotificationSubscriber to fix up the data to perfection after it has been committed to the live database.

This has been a natural way of doing things, because these are the tools the platform provides for us for transforming the data we might get from e.g., Business Central to match a certain use case. But after considering the risks involved with executing my otherwise perfect piece of code at just the time in the synchronization process, this would keep me awake at night. Not only is it a fragile place in the process to start injecting code, but if something should happen (Murphy says it will), debugging is a pain and I would almost need to catch in in the act.

So, I started looking into some good old database tricks as an alternative. One thing that has really been missing in the standard integrations, where we would normally receive perfect data from the ERP, is a staging area for the data, where we could also validate the data before moving it into the production tables. It is not an out-of-the-box feature, but we can easily work the concept into the Data Integration app, so I wanted to share a few tricks for doing the transformation in a less risky place.

Setting up Staging Tables

We start by creating staging tables in the database, which can be done using the SQL Firehose. If you want to use CREATE TABLE, go ahead, but using this small script will create a table matching another table, e.g., EcomProduct, allowing you to import to a similar table before importing to the production table and catching the errors here instead:

SELECT TOP 0 * INTO Stage_EcomProducts from EcomProducts

Now, we can import to Stage_EcomProducts and do troubleshooting on the data before we update the shop table. In my experience, troubleshooting data using SQL is much easier and more powerful than troubleshooting an XML file or multiple batches of XML files.

I also use something similar to back up a single table, if I need to some dangerous work on the data (which off cause I never do on a product environment):

SELECT * INTO EcomProducts_bak from EcomProducts

The difference when backing up compared to making the staging table is to select everything, so I get both the schema (keys not included) and the data, but for making the staging table I select no records and get only the schema.

Views

If you’re using other sources than the ERP Plug-In Unit, e.g. importing from OData, the data structure will probably not be compatible with Dynamicweb. An example, if you are importing contact persons and addresses from ERP, In Dynamicweb addresses are associated to users/contacts, but in BC contacts and addresses are associated to companies, so we need to do some transformation.

Assuming we have this:

If we join these two tables by customer number, we have unique combinations of contacts and addresses, which we can map in to AccessUserAddress.

select Contacts.ContactNo + '_' + Addresses.AddressNumber as AddressExternalId, Contacts.*, Addresses.* from Contacts inner join Addresses on AddressCustomerNumber = ContactCustomerNumber

If we want to work with this in Data Integration, we can create a view with this join:

create view ContactAddresses as select Contacts.ContactNo + '_' + Addresses.AddressNumber as AddressExternalId, Contacts.*, Addresses.* from Contacts inner join Addresses on AddressCustomerNumber = ContactCustomerNumber

And then we have a view which we can use as data source in Data Integration, and we can even debug the data by querying that view.

If we ever want to modify the view, we first select the definition to not have to rewrite it completely:

select OBJECT_DEFINITION(OBJECT_ID('ContactAddresses')) as ViewDef

Then, we drop the view:

drop view ContactAddresses

And then we can create a new view with the same name but a new definition.

For all the queries in this post, you can create them as views on the database and then use that view in a Dynamicweb Source Provider in a Data Integration activity. If you don't use Dynamicweb as the staging area, you can use an SQL source provider, which might as well connect to an Azure hosted database shared between Dynamicweb and the source.

Joins

If you know everything about jois, just skip to the next part. Otherwise, let's have a look at some basic examples

Left Outer Join takes all records from the table specified on the left-hand side of the OUTER JOIN statement and joins them with whatever rows it can find in the table specified on the right-hand side. So here we find all contacts and see that Jill Doe’s company doesn’t have any addresses associated.

select * from Contacts left outer join addresses on Addresses.AddressCustomerNumber = contacts.ContactCustomerNumber

Right outer join does the opposite and takes all rows from the table specified on the right-hand side of OUTER JOIN and joins them with whatever available rows exist in the table specified on the left-hand side. We see that we don’t have any contacts associated with CUST1400 associated address.

select * from Contacts right outer join addresses on Addresses.AddressCustomerNumber = Contacts.ContactCustomerNumber

Full outer join takes everything from both tables and joins whatever rows it can. We see that both rows missing from left and right outer are shown here:

select * from Contacts full outer join addresses on Addresses.AddressCustomerNumber = contacts.ContactCustomerNumber