Datarails Integrations solution is a paid plan that lets you consolidate all your data sources and seamlessly connect your external apps to the Datarails web app. For more information contact our integrations team here.

Section I: Prerequisites

1. Firewall / Networking*:
   • Access to https://app.datarails.com via 443 port is required
   • Access to https://static.datarails.com via 443 port is required

*If IP whitelisting is required:

• US: 20.231.115.17
• UK: 20.90.237.117
• CA: 20.116.160.220
• WEST US: 52.151.49.67

*Please create a “read only” user in the database.

Section II: Setting up the Data Sync Process

You will first need to log in to Datarails using your Datarails credentials via app.datarails.com (you should be an Administrator in Datarails). Please note that the app link could be prefixed with "CAD" or "UK" depending on your region.

Step 1: Create a New Data Source in Datarails

1. Go to Admin > Data Sources
2. Click Add New Data Source

3. Fill in the following information:
   • Organization: Pick the organization to which you want to assign the connection
   • Type: Select “ODBC/OLE”
   • Name: Name this integration (e.g. “Test1”)
   • Server URL / Connection String: see table below
   • Sync Service: switch to off because this is a cloud based connection
4. Click Save.
5. Click Test Connection; OK Should appear in the “Test Results” field.

Sample connection strings

The connection string may vary between the different SQL Server/ODBC configurations.

6. Once the Data Source is created, you can share it with other Datarails users.
   Note: Only users that are given permission to the Data Source, can use it for querying and syncing data to Datarails Fileboxes.
   

   • Go to the Data Source you have just created, choose “Share”

   • You can add and remove users to this Data Source as well as see which users have access to this connection.
     

Step 2: Querying the Data Source

For the most up-to-date and complete instructions on how to create a new Filebox via the workspace or the Excel add-in, please go here.

After you have created the Filebox, pickup here with the first step.

1. Go to Filebox > Settings tab and scroll to the Data Connection section
2. Choose the relevant Data Connection from the list (e.g Test1)
   • Note: the connection will appear in the list only if it was shared with the user as described in the previous steps.
3. To define a sync schedule set a Sync Interval.
   • For this example, use 0 * * * * to have the sync process run every hour; the syntax used here is a cron syntax
4. In the Sync Query field, type the actual SQL query that will be executed. (e.g. SELECT * FROM sometable)
5. Click Save
6. Click Test; an OK indicates that the query was properly defined
7. Versions will start syncing to the Filebox according to the set schedule
8. Optional: Press Sync Now in order to create a version in the Filebox from the underlying query.

Important! It can take up to two (2) minutes until the synced version will appear in the versions list

Step 3: Using Filebox Date Tags for Queries

In the Filebox you can reference the assigned date_tag dynamically by using the {{date_tag}} directive. This will replace everything within the double curly brackets with the real date tag of the query.

Example:

If the date tag is Dec-2020 (Monthly), the string, The current date tag is {{date_tag}} will be rendered to, The current date tag is 2020-06-01

You can test this by saving the date_tag, and then press on the Show Rendered Query button. In addition, you can use filters on the date-tag, using the pipe (|) sign. A filter is an operation performed on the date tag itself.

The following simple filters can be used:

• year example: The year is {{date_tag|year}} > The year is 2020
• month example: The month is {{date_tag|month}} >The month is 06
• day example: The day is {{date_tag|day}} > The day is 01

In addition, the following date manipulations can be used:

• eomonth (end of month)
• somonth (start of month)

These manipulations can be followed by the additional filter:

• {{date_tag|eomonth}} > 2020-06-30
• {{date_tag|eomonth|day}} > 30

This filter add can be used in order to manipulate numbers (hence the day/month/year):

• {{date_tag|day|add:4}} > 5
• {{date_tag|eomonth|day|add:-5}} > 25

Last, there is a special format filter, that can be used for formatting date in any python valid format examples:

• {{date_tag|format:"%B %d %Y" }} > June 01 2020

Updated April 02, 2025 20:47