Scheduled Export/Import for Dynamics CRM

17 October 2013

Often when working with Dynamics CRM there are other systems and processes that require data from Dynamics CRM. This is a task that can be easily completed by using DS3 and Ouvvi in three steps.

For this example we wanted to export 3 items to Excel and one to XML at 6 o'clock in the evening.

  1. All 'Open' opportunities have not been modified in the last week.
  2. All new contacts (FirstName, LastName, email address and Telephone) that were created this month.
  3. All new accounts that were created this month (AccountNumber, Name and web address)

For each of these exports it is necessary to complete 3 steps in DS3:

  1. Connect to the Dynamics CRM and the entity for export.
  2. Pick the columns for export and filter the rows being returned.
  3. Connect to the Excel or XML Files.

We want to schedule these exports so the first thing is to create a Project in the Automation Server Ouvvi and then three empty DS3 Steps:

Project

I open the first empty step 'Export Unmodified Opportunities' in DS3 and connect to the Dynamics CRM 2013 Opportunity entity:

Connect

I can then just drag and drop the columns onto the schema window until I am happy:

Schema

Then I can use the 'Tools->Create Excel Sheet' to create the exact source schema in an excel file . DS3 will also automatically set the destination to that Excel sheet. Interestingly, DS3 can create SQL Tables, SharePoint Lists, XML files etc from source schemas.

We can then click, Compare A-> B..Synchronise..Start and all of the Opportunities all be in the Excel sheet.

In reality we want to filter the opportunities to just the ones in the last month. In Dynamics CRM we in an advanced find (or view a current view) we can export the filters by clicking the 'Export to XML' to see the expression.

FetchXML

We can copy the portion into the FilterXmlExpression property in the Dynamics CRM Source connection details in DS3:

Properties

Previewing the Data again will show that we have filtered the opportunities down to this months opportunities:

Preview

Synchronising again will update the Excel file with the filtered rows. We can now just click the save button and the project will be saved inside Ouvvi and safly under version control.

We can run the project from inside Ouvvi to make sure that the server connect to the Dynamics CRM instance and the target Excel file. We do this by Clicking on the 'Start Project' button:

Run

We repeat this for the other exports.

The next step is to schedule the project to run at 6 o'clock every evening. Projects are started by triggers. To create a trigger click on the Trigger menu and then Add Trigger and make it a time trigger:

Trigger

We can then go back to the 'Schedule Exports' project and add the new trigger from the 'Triggers' tab:

Add Trigger

The project will now run, exporting the three files every night (except Sat and Sun) at 18:00. We can use the 'Documentation' feature to create some documentation:

Documentation

| |