When you are building a bi-directional integration between CRM and an external application using SSIS, chances are you will need to send data that has been changed by a user in CRM on a regular basis. There are a couple of key challenges with this approach, given the traditional mechanism to deal with changes by looking at the modified date in the system.

Since we are working with Dynamics CRM, the API tracks all changes to the record by updating the Modified On and Modified By fields with the last user who modified the record. This can be a challenge if you have plugins/workflows/ integrations making changes to the records as an administrative user, as it will change these fields even when a user does not make a change in CRM.

You can exclude the "admin" users and service accounts from your query, however you will still lose visibility into which user actually made the last change we want to send to ERP. This is especially problematic if you have a workflow which runs on update of a record, because it may change the "modifiedon" date and "modifiedby" user before you have a chance to pick up the data with your query. This would cause you to miss those records, losing those changes. Two things you absolutely do not want to do when integrating are lose any changes or send thousands of updates to the other integrated systems when no change has occurred.

If you are using Scribe Insight, you can handle this functionality using the built in adapter Publisher object, which registers some plugins against the objects you wish to track, automatically sending messages to the MSMQ for processing while giving an "ignore" user function to avoid admin user updates. With SSIS and KingswaySoft, however, you don't have that functionality out of the box, and therefore need to build it.

In order to avoid this, we are going to build functionality to only send data when a user changes the record in CRM in 3 (relatively) simple steps:

Build a plugin (or synchronous workflow) to populate two custom fields whenever a change occurs by a user whose changes should be synced to ERP. This process will trigger based on the conditions you specify, and update the "Last Touched" fields with the user who made the change and date the change was made.

Build a table in either a SQL database or a custom entity in CRM to store job execution time details. It doesn't matter which approach you take -- either way, you will create a record for each job, containing a unique ID for the job and a last run date field. Then, in SSIS, your last step in your package will be to update that record, storing the current date and time as the last run date. You will also want to preload this table with a starting date and time for each package that will leverage this functionality.

We will now add a task in SSIS to pick up the "last run date" data from the new table we just created. Pass that information into a user variable in the SSIS package so you can reference it in your FetchXML query. In this case, I will call it "LastRunDateTime" and we will get that value in an Execute SQL Task running the following query:

select
last_run_date
from
crm_integration_packages

where
Package_ID
=
'30005'

Make sure you map the result to a variable, or it will be useless in the following operations:

Next, use the custom CRM fields (we called them "last touched by" and "last touched on") as your source query in your SSIS package to pick up net change from those fields. Here's an example of a FetchXML query for the account entity where we use our custom CRM fields in conjunction with the LastRunDateTime variable:

After building this functionality, you can feel comfortable knowing that you will not be sending loopback data between systems as a result of integration updates or workflow/plugin updates that should be excluded. You also have full flexibility to build conditions into your Workflow or Plugin to specify conditions where data should or should not be sent out of CRM, giving a CRM Administrator more power to filter data out of an integration if the need arises. If you would like more tips on how to optimize and improve your integrations with Microsoft Dynamics CRM, check out some other interesting articles on the PowerObjects blog.

Happy CRM'ing!