Inconsistent Script Task in Process Model

I have an issue that I am at a loss on how to trouble shoot.  I am trying to produce an Excel Spreadsheet that notifies management of 6 different conditions that will occur within 3 months of the report date for the clergy members of our diocese.

  1. Clergy Members that do not have a term end date.  All clergy assignments must have a term end date, and this report shows those that do not have one.
  2. Age Notification - This notifies management of Clergy members that are nearing several age related events. Medicare eligible, early retirement and mandatory retirement.
  3. Birthdays - This notifies management of Clergy Members' birthdays within the next 3 months
  4. Ordination Anniversaries - This notifies management of Clergy Members Ordination Anniversaries within the next three months
  5. Assignment End - This notifies management when Clergy Members are within 3 months of their assignment termination date.
  6. Status Notification - This notifies management when a Clergy Member is within 3 months of the ending of some leave of absence or temporary assignment outside of the diocese.

This report runs the first Monday of every month and I use a process model Run Assignment Notification Report to produce the report.

The process model is pretty straight forward.

  1. The Start Node is set to kick off on the first Monday of the month.
  2. I use a Script Task to pull the data
  3. I save the data from the Script Task into a report table using a Write to Data Store Entity AssignmentNotificationReport
  4. I copy an Excel Template using a plug in Copy Document Service and save it into the Export Folder
  5. I rename the copied template for the final report

     

I then run through a serries of two smart services; Script Task and Export DSE to Excel for each of the 6 tabs.

  1. I pull the configuration I need for the Export DSE to Excel service by executing two expression rules.
    1.  CMS_GetClergyReportQueryConfig the output is saved to the PV!clergyReportQueryConfig
    2. CMS_GetExportCustomPositionsAndValues the output is saved to the PV!exportCustomPositionsAndValues
  2. I used these to execute the Export data from the AssignmentNotificationReport to the Excel Spreadsheet
    1. The PV!clergyReportQueryConfig.selection is used in the export Selection data Input.
    2. the PV!clergyReportQueryConfig.filter is used in the export Filter data input.
    3. the PV!exportCustomPositionsAndValues.startingCell is used in the Export Starting Cell data Input.
    4. the PV!exportCustomPositionsAndValues.positions is used in the Export Custom Cell Positions data Input
    5. the PV!exportCustomPositionsAndValues.values is used in the Export Custom Cell Values data Input

This is my problem. 

The first time this set of two smart services run, it works fine. I can see the process variables in the process model monitor and the data is pulled and saved into the spreadsheet and sorted corrected. (EX: Set Blank Term End Config and Export Blank Term End Tab).

The second and all subsequent pairs of smart services (for example Set Age Notification Config and Export Age Notification Tab) I do not see any data in the two process variables; PV!clergyReportQueryConfig and PV!exportCustomPositionsAndValues (the are null).  The strange thing is that the data is pulled correctly and in the proper tabs, however, it is not sorted correctly.

I tested the two expression rules independently of the process models for all the possible inputs for this report and they work as expected for every condition in this process model.

It is a guess on my part that the reason the data is not sorted correctly, it that the two process variables i need for the Export DSE to work are null.  But if that is the case, then way is the data filtered correctly into the worksheet??

The issue I am trying to solve is to sort the data in the worksheets 2 through 6. 

  Discussion posts and replies are publicly visible