Welcome back for part 2 of this blog on how to create and configure an SSIS package to schedule CRM workflows using SQL Server’s job scheduling window in Microsoft SQL Server Management Studio (MSSMS). Part 2 covers the steps needed to run the workflow using the xml generated in Part 1.

Let’s begin!

Steps

  1. Create a new Data Flow task and name it “Run Workflow”.
  2. Inside of the “Run Workflow” Data Flow, add a new Dynamics CRM Source. Configure the source as follows (replacing the source entity “adx_setting” (shown below) with the name of the entity that is associated with your long running workflow):

Using SQL Server Agent to Schedule CRM Workflows via KingswaySoft (Part 2)

  1. The above step will retrieve all records. This is not necessary, as we only need a single record to start our workflow (the workflow itself contains a retrieve multiple that is used to find only relevant records based on a set of criteria, for each of these records we will perform some custom code logic). You have the option of specifying more criteria using FetchXML to restrict the records found to one – this has the benefit of allowing you to see a history of the execution via a single “initiator” record. In this example, we don’t mind what the “initiator” record is, so we will choose one at random. To achieve this, add a Row Sampling widget, and configure it as follows:

Number of rows: 1

Input Columns: Include both the name of the record (in the screenshot adx_name) and the Id of the record (adx_settingid)

row sampling transformation editor row sampling transformation editor 2
  1. Add a new Derived Column transformation to the Data Flow and link it to the “Sampling Selected Output” of the “Row Sampling” widget. Setup the Derived Column transformation as follows:

Derived Column Name: WorkflowToRun

Derived Column: <add as new column>

Expression: @[User::WorkflowToRun]

Data Type: Unicode string [DT_WSTR]

Length: 2000

derived columns transformation editor

  1. We now have a trigger record (achieved in steps 1,2 & 3) and the name of the workflow to run in the data flow (achieved in step 4). The final step is to add a new Dynamics CRM Destination to trigger the workflow. To achieve this, configure it as follows:

Action: ExecuteWorkflow

Destination Entity: <replace “adx_setting” with the name of the entity that is associated with your long running workflow>

Workflow: Workflow Name (Note: this will be overwritten)

execute workflow

 

  1. The Dataflow is now completed. It should look something like this:

dataflow

  1. Save the dataflow and connect it with the control flow we created in Part 1 of this blog. The final control flow should look something like this:

save the dataflow

  1. The last step is to ensure we can set the workflow name via parameter. To achieve this click on the “Run Workflow” control flow, and in the properties window set the following value:

[Dynamics CRM Destination].[Workflow]: <CrmWorkflow>   <WorkflowId>00000000-0000-0000-0000-000000000001</WorkflowId>   <Name>Workflow Name</Name> </CrmWorkflow>

Expressions:

expressions

You can now build and deploy the package to SQL Server and configure the name of the workflow to be run by setting the “Workflow Name” parameter in the package configuration setting of the Job Step properties, i.e.:

workflow_name

I hope the above example gives you more options in regards to scheduling your long running workflows. Best of luck with your future CRM integrations!