Skip to Content

Migration rules in Jitterbit App Builder

Migration Rules are a type of Rule that are executed when a release of App Builder is installed on a server. Migration Rules are configured in the Business SQL area of the Business Logic Layer.

Migration Rules are used to manipulate or clean up data in an existing app and are also used for migrating data after performing a schema change. Migration Rules allow a user to modify a database using the actions Cram, Delete, Insert or Update. Migration rules are defined similarly to a CRUD Rule and run in the development environment. Migration Rules (unlike CRUD Rules) are added to change management steps which are executed in order when pushing an LP to a new environment. App Builder records the rule and runs it during an upgrade.

Migration Rules should be specific to an Execution. If you need to run similar Migration Rules twice, best practice is to create a new Rule for the second Execution. When you Execute a Migration Rule (i.e., click the Execute button for a Migration Rule), App Builder takes a snapshot of the Rule and embeds it in the changeset. Nothing will get added to the changeset until the Migration Rule has been Executed. After Executing the Rule, the Migration Rule can be deleted.

Migration rule tutorial

Important migration rule notes

  • Migration Rules don't support Enum
  • XP CRUD isn't supported for Migration Rules
  • Data cannot be migrated to/from encrypted columns using a Migration Rule. Developers must use CRUD business rules to move data to/from encrypted columns.
  • Currently you cannot modify a CRUD Rule to make it a Migration Rule. If you do it will not fire.

Migration rule actions

The Action selected for a Migration Rule is based on what the Rule is intended to do. Action options are:

  • Cram – inserts distinct/unique records based on the fields marked as Primary Keys
  • Delete – deletes Columns from a Table
  • Insert – creates Columns within a Table
  • Update – updates Columns within a Table

Create a migration rule to cram data

  1. Navigate to the App Workbench > Rules
  2. Confirm the selected App Data Source is where you want to add the Rule
  3. Click + Rule
  4. Assign a Name for the Migration Rule. For example: EventDate (EventDate Migration)
  5. Select Migration as the Purpose
  6. Set the Action to Cram
  7. Set the Target to the Table to be updated against. For example: EventDate
  8. Click Save
  9. Click + Tables and bring in the Target table. For example: EventDate
  10. In the Columns panel add in the Primary Key, the Expression to describe what is being updated, along with the Target which is the destination Column for the Expression
  11. Depending on your Rule you may need additional logic defined
  12. Click Validate and check the information is as expected (no errors)

Create a migration rule to delete data

  1. Navigate to the App Workbench > Rules
  2. Confirm the selected App Data Source is where you want to add the Rule
  3. Click + Rule
  4. Assign a Name for the Migration Rule. For example: Contact (Delete Orphaned Contacts)
  5. Select Migration as the Purpose
  6. Set the Action to Delete
  7. Set the Target to the Table to be updated against. For example: Contact
  8. Click Save
  9. Click + Table and bring in the Target table

  10. In the Columns panel add in the Primary Key, the Expression to describe what is being updated, along with the Target which is the destination Column for the Expression.

    Important

    The Primary Key must be mapped with a Target for Delete Rules to work, or else the Rule will delete everything in the Table.

  11. Click Validate and check the information is as expected (no errors)

Create a migration rule to insert data

Migration Rules with the action type Insert can create new Columns within a Table. All required fields for the Table where a new Column is being created must be mapped from the source Table.

  1. Navigate to the App Workbench > Rules
  2. Confirm the selected App Data Source is where you want to add the Rule
  3. Click + Rule
  4. Assign a Name for the Migration Rule. For example: Session (Insert Session)
  5. Select Migration as the Purpose
  6. Set the Action to Insert
  7. Set the Target to the Table to be updated against. For example: Session
  8. Click Save
  9. Click + Table and bring in the Target table
  10. In the Columns panel input the Expression to describe what is being inserted, along with the Target which is the destination Column for the Expression
  11. Click Validate and check the information is as expected (no errors)

Create a migration rule to update data

  1. Navigate to the App Workbench > Rules
  2. Confirm the selected App Data Source is where you want to add the Rule
  3. Click + Rule
  4. Assign a Name for the Migration Rule. For example: Reach (AuditID Migration)
  5. Select Migration as the Purpose
  6. Set the Action to Update
  7. Set the Target to the Table to be updated against. For example: Reach
  8. Click Save
  9. Click + Table and bring in the Target table
  10. In the Columns panel add in the Primary Key, the Expression to describe what is being updated, along with the Target which is the destination Column for the Expression
  11. Click Validate and check the information is as expected (no errors)