Wednesday, April 22, 2015

How to Execute a CRM Workflow from SSIS - Dynamics CRM and SSIS Integration

In this post we describe Step by step How to Execute a CRM Workflow from SSIS - Dynamics CRM and SSIS Integration  ,  in Sql Server Integration Services.
In this article we create from scratch a small SSIS application which calls - executes a Dynamics CRM workflow (this walkthrough applies to SQLSERVER 2005-2008-2012) . You can schedule this SSIS application to run in the middle of the night , and automatically perform , for example, CRM email sending to customers . For example ,  you could add the small application which we design here , to a bigger one as the following:




How to Execute a CRM Workflow from SSIS - Dynamics CRM and SSIS Integration 



First , open the Toolbox and find the Data Flow Task item:


Execute a CRM Workflow from SSIS - Dynamics CRM and SSIS Integration

Add it to your SSIS application, and click on the "Edit" .
Inside the Data Flow Task item , add a Data Flow Source of your convenience (it can be a raw file, ADO NET, OLE DB , Excel, XML , or even a Dynamics CRM source) .
Connect to it some Data Flow Transformation (such as a Derived Column or a Data Conversion).
We are using here a Dynamics CRM and SSIS Integration tool from KingsWaySoft. 
According to the   KingswaySoft  Help manual which can be found here , starting from the version 3.0 of this tool, there is an option for calling a Dynamics CRM Workflow from SSIS. 
Therefore, look for a Data Flow Destination of the type "Dynamics CRM Destination", and append it to the previous items ,  sequentially connected:


How to Execute a CRM Workflow from SSIS - Dynamics CRM and SSIS Integration 1


Your Data Flow Task should by now look as the following:




How to Execute a CRM Workflow from SSIS


Now we'll edit the Dynamics CRM Destination's properties, to make it able to execute a CRM Workflow.
Open the "Edit" window in the Dynamics CRM Destination , and select the "Execute Workflow" option at "Action":


CRM Workflow from SSIS

The "Destination Entity" should be the ENTITY over which you want the Workflow act.
At the "Execute Workflow Option" you will be able to choose between all the available activated (published) workflows which are set to run over the current Entity.
Select the Workflow that you want to call from SSIS.
Then open the "Columns" tab:


 Dynamics CRM and SSIS Integration


This tab will automatically contain only one column. A very important Entity property will appear here: the ID for the Entity record on which you want the workflow to be run.
This property is of GUID type. However, you send it a string containing the required ID. It doesn't need to be of uniqueidentifier (GUID) type: as you can see at the snapshot above, we send a DT_WSTR SSIS type with length 100 . Also, the length doesn't have to be the length of a string containing a guid , that means 36 (32 digits plus 4) . 
That's all that the Dynamics CRM Destination item needs to execute your Workflow.
Build the SSIS application. Run it in debugger mode (F5) , and your workflow will be triggered!!!
If you receive an error message, just try to run your workflow by hand, right from the CRM entity form, because probably the cause of the error will be there.
Hoping this article was useful to you,

Happy programming.....

      by Carmel Schvartzman


כתב: כרמל שוורצמן