Date migration in Jitterbit App Builder

Overview

App Builder's Date Migration feature provides support for migrating dates from one time zone to another.

Recommendations

Date Migration is recommended for Developers looking to convert DateTime columns from one time zone to another.

The ideal time zone for your Data Source is, perhaps debatable, however the recommended practice is to configure your servers and Data Sources to all use the same time zone. UTC is likely the best choice as any other time zone is prone to change if the server location changes. Also note that Amazon instances are by default set to use UTC.

Another thing to keep in mind is that configuring your database server time zone to be the same as your Data Source time zone will ensure that calls to 'Now()' will return the expected value. Now() returns the current time per the database time zone.

By keeping all your Data Source time zones in sync, you can avoid the headache of converting DateTime data from one time zone to another. Today, this is not done automatically although it may be implemented in a future release.

Limitations and caveats

Although DateTime columns are migrated, there are other aspects of your App Builder application which may require modification, including:

• Hardcoded DateTime values in rules are not adjusted. If your rules include where or select clauses with hardcoded dates, you will need to manually adjust these to the new expected time zone.
• Any columns which use DateAdd or similar functions to adjust time zones manually will also be left as-is. Developers will need to fix these manually.
• MS SQL Server versions earlier than 2016 do not support the AT TIME ZONE function. As a result, Date Migration will be performed using a time zone offset calculated from the source and destination time zones as of the current moment in time. This may cause issues with time zones that make use of daylight saving time.
• Date Migrations run in a single transaction when your app/data source is being upgraded via an LP. The transaction can timeout depending on the amount of data to migrate. If this happens, setting a longer CommandTimeOut in your connection file will help mitigate timing out.

Configure date migration

To perform a Date Migration execute the following steps:

• Navigate to the IDE > Additional Settings > Date Migration
• Click + Migration on the Date Migrations panel
• Select a Data Source
• Select a Source Time Zone. This setting will already be populated for Data Sources that have a non-null Time Zone.
• Select a Destination Time Zone
• Click Save

At this point, the right side panel should populate with all of the DateTime columns in the selected Data Source. From here you can configure the Date Migration settings for individual columns.

If your Data Source does not have a time zone set, you'll notice that Audit DateTime columns will use the App Builder application server's time zone as the Source Time Zone. Audit data is written using the application server's time zone when the Data Source does not have a configured time zone. Also note that App Builder uses the AddedOn and ChangedOn column Usage Types to determine whether a column is considered Audit data.

Once you are done adjusting individual columns, you may proceed with the following steps:

• On the same screen as above click the Migrate button on the Date Migration panel.

This will run a background job to migrate all of the configured columns. Date Migration is run within a single transaction and will lock tables in the process. You should ensure that traffic on your server is minimal to none to avoid lockups.

Change management steps will also be added for each table being migrated.

After dates have been migrated, the status of the Date Migration will change to Complete.

If a failure occurs, the Date Migration transaction will be rolled back and additional logging can be found in the background job history.