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

The Datarails SQL Sync Service must be installed on a server that maintains continuous operation and remains connected to the SQL database. It is not mandatory for this server to be the same as the one hosting the SQL database.

1. Operating system: Windows Server 2012 or higher
2. Hardware:
   • Recommended: 4 Processors, 16 GB RAM, 50GB Free disk space
   • Minimal: 2 Processors, 8 GB RAM, 30GB Free disk space
3. NET Framework 4.8 version is required (a restart may be necessary) - Download from this link
4. The installer requires Admin privileges
5. 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

*If on-prem, needs access to the on premise server.

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

Section II: Sync Agent Installation Instructions

Step 1: Installation

Download the latest version of the SQL Server agent from here and follow the instructions.

After installation, two (2) components will be installed on the machine (C:\Program Files (x86)\Datarails Sync):

1. DRSyncToAllService - a Windows Service that should be in running state
2. Datarails Sync - shortcut to EXE file used as a UI for the service

Step 2: Set up User Credentials

After the installation, a credentials configuration window should appear automatically.

In case the screen did not appear: Locate the Datarails icon in the System Tray, right click > Credentials

1. The credentials (username and password) should have been sent to you by the Datarails Support team.

2. Enter the username and password and click Save & Validate
3. The service will perform a quick test to validate the credentials and access to the Datarails server. If the tests are successful, a green icon should appear next to the test as shown.

4. In case of error, a red icon will appear next to the test.
   • Hover with the mouse over the icon to see the error code, then right click on the icon to copy the information to clipboard.

Windows Account

The service logs on by default as a Local System account. If you want the agent services to run on a Windows account instead of a Local System account please do following:

1. Open Control Panel -> Administrative Tools -> Services DRSyncToAllService
2. Right click -> Properties -> Log On (see image).
3. Type the username and password.
   • Note: This account must be a member of the local Administrators group
   • It must have the read/write access to C:\Program Files (x86)\Datarails Sync directory.
4. Click Apply and restart the service

Step 2.1: Proxy Configuration (Optional)

Datarails SQL Sync service automatically detects the proxy configuration for the specific user that logs on to the service. There are cases that the proxy is not configured to work for a Local System account. In that case, click on Advanced to manually set up the proxy settings as shown below.

1. Select Use proxy option.
2. Enter the proxy address and optional username and password of the proxy.
3. Optional: To bypass the proxy settings in case a proxy is configured in your system, check the Bypass the proxy settings option and be sure that proxy address is the same as configured in the computer.

Step 3: Starting / Stopping the Service (Optional)

1. Locate the Datarails SQL Sync service UI icon in the system tray
2. Right click the icon > SQL Sync > Start Service.

Step 4: Log Files (Optional)

Viewing Logs Files

To view log files, we recommend a log file viewer that supports Tail. In this way, you can see the log file messages in real time.

Please download one of the following:

1. Bartail — Free version
2. LogExpert — Open source

Exporting Log Files

1. Login to the FileSync server.
2. From the System tray, right click on the Datarails icon and choose Zip Logs.
   • In case the agent is not running, go to: C:\Program Files (x86)\Datarails Sync and double click on FileSyncAgent.
3. After File Explorer is opened, use the latest zip file.

Upgrading the Existing Version

1. Download the latest software.
2. There is no need to uninstall the previous version.
3. The previous settings will be saved.
4. Follow the installer instructions.

After the installation, the service will automatically start.

Section III: Configuring ODBC Data Source

1. From the server where we installed the agent, go to Windows main search and type ODBC Data sources.
2. Choose the ODBC 32-bit.

3. Click on System DSN > Add.

4. Select the driver where you want to set up the data source.

5. Fill in the rest of the Data source details.

6. The DSN Name the user created should be pasted on Datarails with the Uid & PASS.

Section IV: 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: On
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

Test Connection Errors

Updated September 10, 2024 06:59