As we know, there are two types of appointment in Microsoft Dynamics CRM: Appointment and Recurring Appointment.
When it comes to the migration and integration of CRM appointment entity it is quite straightforward when using our software. What you need to do is to simply map those necessary fields from the source to the target in the CRM destination component.
We find it is quite tricky when working with recurring appointment while helping our customers. In this blog post, we will demonstrate how to migrate recurring appointment data. You may find it extremely easy to handle the tricky situation by using our software.
When working with the migration of recurring appointment record, you need to use RecurringAppointmentMaster entity to work with recurring appointment series. Each RecurringAppointmentMaster represents the master appointment of a recurring appointment series. As you create a RecurringAppointMaster record, CRM would automatically create a RecurrenceRule record.
To create a recurring appointment series, you need to provide the following information.
1. Appointment Time
You need to specify the recurring appointment’s starttime and endtime by mapping them to the destination CRM system. CRM should automatically calculate the duration accordingly.
2. Recurrence Pattern
- You would need to set the RecurrencePatternType by defining the possible recurrence pattern values, our software accepts either integer values or string values for Option Set type field in CRM.
The values are as follows:
Daily = 0,
Weekly = 1,
Monthly = 2,
Yearly = 3 - You would also need to specify the frequency of occurrence. Options vary based on what Recurrence Pattern you choose. The following are the fields needed:
- Interval: Occurrence of a given recurrence type. E.g.,“1” means the appointment will repeat once.
- IsWeekdayPattern: Indication of whether the weekly recurrence pattern is a weekday pattern. Valid for weekly recurrence pattern only.
-
DayOfWeekMask: The assigned values are:
1 : Sunday
2 : Monday
4 : Tuesday
8 : Wednesday
16 : Thursday
32 : Friday
64 : SaturdayIf you want to set the appointment on Monday and Wednesday, then just add up the decimal value of Monday and Wednesday. Ex: Monday (2) + Wednesday (8) = Monday & Wednesday (10)
The maximum value for the DayOfWeekMask property is 127, which basically means seven days a week.
- Instance: Specifies the recurring appointment series to trigger on every Nth day of a month. Valid for monthly and yearly recurrence patterns only.
- DayOfMonth: The day of the month on which the recurring appointment occurs.
- MonthOfYear: Indicates the month of the year for the recurrence pattern. And the assigned options are:
0 : Invalid Month Of Year
1 : January
2 : February
3 : March
4 : April
5 : May
6 : June
7 : July
8 : August
9 : September
10 : October
11 : November
12 : December
The following screenshot shows an example of setting weekly recurrence pattern:
3. Range of Recurrence
Specify the range of recurrence by setting the PatternStartDate and the PatternEndType.
Select the type of end date for the recurring appointment by supplying PatternEndType field, the default options are:
- NoEndDate = 1
- Occurrences = 2. If you select to end the recurrence by occurrence, the Occurrences field is required.
- Pattern End Date=3. If the pattern has been set to end by a certain date, the PatternEndDate field is required.
One tricky part here is PatternEndType. You may easily run into “Invalid Recurrence Pattern (Error Code: -2147163904, Detail Message: Invalid Recurrence Pattern)” error if you set a wrong combination of end range.
- When PatternEndType=1, make sure Occurrences and PatternEndDate fields are both NULL.
- When PatternEndType=2, DO NOT enter value in the PatternEndDate field since CRM calculates the value automatically.
- When PatternEndType=3, make sure Occurences field is NULL.
Due to the above described behavior, you would want to split the input into 3 different branches based on the PatternEndType value from the source system. You can either split them using three FetchXML queries, or simply you can read all RecurringAppointmentMaster records, then split them using a SSIS Conditional Split component.
Let’s start from the FetchXML query option, you would be using a FetchXML query such as the following to filter data based on PatternEndType.
<fetch mapping='logical'>
<entity name='recurringappointmentmaster'>
<all-attributes />
<filter>
<condition attribute="PatternEndType" operator="eq" value="1" />
</filter>
</entity>
</fetch>
The data flow task would be as simple as just two CRM components: source and destination. As shown below:
Alternatively you can retrieve all records from CRM or an out-of-the-box source and perform a filtering on the records locally using a SSIS Conditional Split component.
The data flow would be something like this:
Sample SSIS Package
A sample SSIS package that includes both FetchXML task and Conditional Split task has been uploaded. Download here.
The sample package was prepared in a SQL Server 2008 R2 format, and it should automatically upgrade if you are using any newer version of SSIS.
Have a question?
Should you have any questions about the practice discussed in this blog post, please feel free to contact us, we are more than happy to assist you.