Custom Workflow Activity: To Retrieve GUID and Entity Name from a Lookup field

Below is a custom workflow activity that can be used to retrieve the GUID and the Entity Logical Name of a Lookup field, from the record that is calling the workflow. The output of this custom workflow activity can then be used in the workflow.

Inputs:

• Attribute Name: Name of the Lookup attribute. If the input is blank, the ID and Entity Logical Name of the record calling the workflow will be returned as the Output.

Output:

• Entity Name and Record ID of the Lookup field or the record calling the workflow.

Recurring Workflow Scheduler Solution

The Recurring Workflow Scheduler Solution allows users to schedule jobs that will trigger workflows at regular intervals.

Although this solution was built as part of the Simple ETL for CRM Project, it can be used as a generic workflow scheduler to trigger any On-Demand workflows in your Microsft Dynamics CRM 2011 system.

Install the Solution

To Install the solution, download the Managed Solution or theUn-managed Solution from Codeplex here: (links to follow)

Quick Guide:

1. To Create a Recurring Job

To create a recurring job:

1. Go to Scheduled Jobs View (you may need to customize your Sitemap if Scheduled Jobs is not available), or open it from the Advanced Find view. Click the New button to open the New Scheduled Job form
   
   
2. Enter the information on the Scheduled Job form and save it.
   

• Name: The name of the Scheduled Job.
• Status Reason: Draft (Scheduled jobs will not be created) or Running (The scheduled jobs will start running)
• Start On: Optional. If left blank, the Scheduled jobs will start running at the time when the status reason changed to Running.
• End On: Optional. If left blank, the Scehduled Job will continue to run, until the Status Reason is changed back to Draft, or when the Scheduled Job is deactivated/ deleted.
• Scheduling Type: Allows user to select from the following options: Run Once, Start every X hour Xmin, Start everyday at HH:MM, Start every week on Xday at HH:mm, and Start every month on X Days at HH:MM. Additional information is required to be entered, based on the Scheduling Type selected.
• Workflow: The workflow that will be run by the Scheduled Job.
• Target Record ID: The record ID where the workflow will run against.
• Workflow Configuration: A place to store the workflow configuration if the custom workflow requires it.

2. To Start, Stop, Restart a Job

To start a scheduled job,

1. Open the Scheduled Job form.
2. Change the status reason of a Scheduled Job to "Running", and save the Scheduled Job.

To stop a job,

1. Open the Scheduled Job form.
2. Change the status reason of a Scheduled Job to "Draft", and save the Scheduled Job. (Note: this will cancel all existing System Jobs that are waiting to be executed.

Similarly, to restart a job,

1. Open the Scheduled Job form.
2. Change the status reason of a Scheduled Job to "Draft", and save the Scheduled Job 
3. Then, change the status reason of a Scheduled Job to "Running", and save the Scheduled Job

3. Monitor Recurring Jobs 

To view the current list of Active jobs and their status:

1. Go to Active Scheduled Jobs View (you may need to customize your Sitemap if Scheduled Jobs is not available), or open it from the Advanced Find view. 
2. The Active Scheduled Jobs View shows a list of Active Schedule Jobs with information on the  scheduling type, Status Reason (Draft/ Running), Last Executed Time and Next Executed Time. 

 To view the history of System Jobs that have been executed by the Scheduled Job:

1. Open the Scheduled Job form.
2. Click on Workflows on the Left menu of the form. This will show the list of recurring system jobs that was triggered by workflow. Also, key job informations are also displayed on the footer of the form (e.g. Created on, Modifield on, Last Executed On, Next Executed On).
   
3. Alternatively, you can create a custom system view or user view on the System Job entity
   
   

Project: Simple ETL for MS CRM

In many of my projects, we need to create recurring ETL jobs to keep data in MS CRM updated. For example:

• In a large enterprise system, Accounts records are usually retrieved from a separate system and needs to be synchronised frequently
• In multi-tenancy solutions, data like Country, Region, State should be easily synchronized across the different MS CRM organizations frpm a master source.

We can use 3rd party tools, such as Scribe and SSIS, or use Messaging Queues.  But since MS CRM comes with a workflow engine and has ways to schedule recurring jobs, I thought that it would be a natural fit to build an ETL platform using the workflow engine. The results probably may not be as good as commercial products, but it should be good enough for simpler tasks.


So here is what I have set out to do in this project:

• Allow users to create an ETL job that can be run immediately, or at a future date/time, or every x minutes, or every day at HH:mm time, or every week or every month.
• The ETL job will fetch the data from a Local CSV file or from an SQL query
• It should allow developers to easily extend the solution to cater for more complex ETL requirements (e.g. some EAI servers expects acknowledgement files to be create at certain locations after the data is consumed, or if the external system is using other protocols like SOAP for retrieving data.)
• It will allow user to define the field mapping.
• Based on a certain field in the source data, it should check if the data exist. If it does, the record will be updated. If it doesn't existing, a record will be created.
• Configuration and monitoring of the job status is self-contained within MS CRM.
• You do not need a separate server or software to do the ETL

What it doesn't do:

• This solution is heavily based on the CRM workflow engine and CRM Web Service. It is probably not be as fast or robust as commercial ETL or EAI. 
• Also, when many records are imported, it may slow down the processing of other system jobs, compared to dedicated servers for ETL.

This project contains several solutions:

1. Recurring Workflow Scheduler Solution

• This is a stand-alone solution that allows users to creates a recurring jobs that will trigger an On-Demand CRM workflow at regular intervals. 
• Users can use this solution as a stand-alone solution to trigger their own custom workflows for other purposes, or they can use it to trigger the workflows in the ETL Solutions below.
• Details on this solution is found in this post here: http://crmjanitor.blogspot.com/2013/08/recurring-workflow-scheduler-solution.html

2. ETL Solutions for Different Import Types
Each ETL Solution contains

• an On-Demand workflow, which can be triggered by the Recurring Workflow Scheduler Solution above.
• a Configuration table which contains the settings for each ETL job used by the On-Demand ETL workflow. E.g. the file location and separator type for a CSV file, the connection string for a SQL import, etc.

For this project, we will only create the solution for:

• a simple CSV file import: (links coming soon)
• a simple SQL Query import:(links coming soon)