Share via

Dynamics CRM 2011 SSIS Monitoring Pending Emails

  • Article

In this article I'll walk through how to build a simple and fully supported SSIS package to track pending emails in CRM. The idea is to receive an alert email when we reach a threshold suggesting email router is stuck, slow performance or unusual mail activity in CRM.

On this tutorial I use the SSIS CozyRoc component to connect to Dynamics CRM, you can download it here:

http://www.cozyroc.com/products

The package process:

  1. Connect to a CRM organisation using the API
  2. Run FetchXML to retrieve all pending emails
  3. Count how many emails pending and storing the total number in a variable
  4. Use an IF THEN ELSE statement to set the flow of the package to successful or failure based on the total number of emails pending
  5. send an alert email if successful or failure.

In Visual Studio go to: New Project; Integration Services Project

The first step is to create a variable that will hold the total number of emails within the Control Flow tab select view and variables, this will display the variables screen on the left, click new variable and name it: RowCount, the scope should be set to 'Package' this means our variable is a global variable so it can be used either in the 'Data Flow' or 'Control Flow' area.

The next step is to create a connection to CRM to retrieve the data we want.

On the connection Managers diaglog box, right click and select New Connection. From the list choose DYNAMICS-CRM CozyRoc
**

**

The following screen should appear, configure it with your CRM information:

I renamed my connection as SandBox Connection as per below screenshot:

Next Step is to build your Data Flow tasks, click on the Data Flow tab and drag the Dynamics CRM source task on to the Data Flow area. 

Note: the Dynamics CRM Source should be available on the bottom of your Toolbox, if you have installed the CozyRoc Component and you don't see it there is because you didn't complete the post-install steps, you need to add those two tasks to the General section of the Toolbox

http://3.bp.blogspot.com/--ND27o2C_rg/UdFtSUpcuJI/AAAAAAAAAdk/6mG1iUyL4eQ/s1600/SSISVs1.png

 

Also drag the Row counter task and the Data Flow area will look like this:

http://1.bp.blogspot.com/-W16VVDpOVSw/Uc24p4soMMI/AAAAAAAAAc8/ZQV-4z4jrWM/s290/rowcount1.png

 

Note: we haven't linked the Dynamics CRM source with the row counter task we will do this soon.

Double click the Dynamics CRM source task and the following window appears:

 

On the above screenshot the first tab, you need to select the Connection Manager, on this case we select our SandBox Connection

Click on the Second Tab:

 

On the above screenshot we want to make use of the FetchXML feature, so at the bottom under 'Custom Properties' on the 'InputType' change entity to FetchXML and under 'FetchXML' paste the following XML:

http://1.bp.blogspot.com/-cUNrhd2FwOE/Uc24pMv_euI/AAAAAAAAAcI/2yW09S1bPV8/s1600/connectionManager6.png

Click OK all done on the 'Dynamics CRM Source' task. The next step is the 'Row Count' task link the 'Dynamics CRM source' to the 'Row Count' task and double click the 'Row Count' task the following screen appears:

Here we simply set the 'VariableName' to use our global variable, on the variable selection window you should see User::RowCount tick the box and click okay. 

At this stage we run the package it will:

  1. Connect to CRM Organisation SandBox
  2. Run the FetchXML
  3. Pass the data to the Row Count task and store the total on the User::RowCount variable

Great so we the Data Flow process in place, now we need to Send an email if we have more than 100 Emails pending we want our Control Flow area to look like this:

http://2.bp.blogspot.com/-OsFM16-SmH0/Uc24p4YiqjI/AAAAAAAAAcg/Ht6Y3s47HoI/s1600/controlFlow1.png

At the above screenshot, we added a 'Script Task' task to process the data coming from the Data Flow, we then send an email either if the package runs successful or fails.

Double Click the Script Task and on the main screen ReadOnlyVariables select our variable User::RowCount

You should see this:

http://4.bp.blogspot.com/-qYk-e33V7D0/Uc24qeKXPHI/AAAAAAAAAc4/5yOsgPIZ8aQ/s1600/scripttask1.png

On the Script Task what we need to do now is to insert code to check if the variable is bigger than X if yes set to Successful completion or to Failure. On the same window click on The button at the bottom Edit Script and insert the following code under the Main() function:

http://4.bp.blogspot.com/-LiHT-PCy-3A/Uc24q2j2YZI/AAAAAAAAAdE/Spklnhp9u1U/s1600/scripttask2.png

The last step is to configure the 'Send Email' task to send an email in case of Successful or Failure. In this example I've added both routes (successful and Failure, however in my production environment I only have one route for when the email is bigger than X, if you understood the above code you should be able to decide how you want to apply your logic the two routes were added to this article to understand better the process flow of this package and for testing purposes you may want to receive an email saying Yes or No to confirm the package is running okay.

I hope this was useful, please leave your feedback.