For optional advance configurations for your DataSync Agent, contact Perspectium Support if you have any questions.

Prerequisites

First, you will need to install a DataSync Agent configured to your use case.

You will also need to create a ServiceNow dynamic share with an update trigger or create a ServiceNow bulk share.

Finally, make sure to stop running your DataSync Agent before making any Agent configuration changes.

What's on this page?

Agent Column Case Configuration

The DataSync Agent supports forcing of the column name to a configured case. This configuration must be done prior to the Agent creating the table. Two directives are available for inclusion within the agent.xml configuration file. The chosen directive must be placed within the <task> tag.

You would use either of these directives in your agent.xml:

Click here to see the agent.xml example...

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent>
        <subscribe>
            <task>
                <task_name>example_subscribe</task_name>
                <message_connection password="encrypt:XXXX" user="XXXX" queue="..." >amqp://example.perspectium.net</message_connection>
                <instance_connection password="encrypt:XXXX" user="XXXX">http://example.service-now.com</instance_connection>
                <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
                <decryption_key>The cow jumped over the moon</decryption_key>
                <database_type>sqlserver</database_type>
                <database_server>MyAddress</database_server>
                <database_port>1433</database_port>
                <database_user>XXXX</database_user>
                <database_password>XXXX</database_password>
                <database_parms></database_parms>
                <database>psp_repl</database>
 
                <uppercase_columns/>
 
            </task>            
        </subscribe>
        <max_reads_per_connect>2000</max_reads_per_connect>
        <polling_interval>5</polling_interval>    
    </agent>
</config>

The default (none of these directives) simply uses the original case of the column. If you happen to specify both, uppercase will be used.

NOTE: This directive affects column names only. It does not affect table names.

ServiceNow Field Type	Database Field Type
address	NVARCHAR
auto_increment	BIGINT
auto_number	NVARCHAR
boolean	BIT
catalog_preview	NVARCHAR
char	NVARCHAR
choice	NVARCHAR
collection	NVARCHAR
color	NVARCHAR
color_display	NVARCHAR
composite_name	NVARCHAR
compressed	NVARCHAR
condition_string	NVARCHAR
conditions	NVARCHAR
counter	NVARCHAR
currency	FLOAT
date	DATE
datetime	DATETIME2
day_of_week	BIGINT
days_of_week	NVARCHAR
decimal	FLOAT
default	NVARCHAR
document_id	NVARCHAR
documentation_field	NVARCHAR
domain_id	NVARCHAR
domain_number	FLOAT
domain_path	NVARCHAR
double	FLOAT
due_date	DATETIME2
email	NVARCHAR
email_script	NVARCHAR
external_names	NVARCHAR
field_list	NVARCHAR
field_name	NVARCHAR
float	FLOAT
glide_action_list	NVARCHAR
glide_date	DATE
glide_date_time	DATETIME2
glide_duration	DATETIME2
glide_encrypted	NVARCHAR
glide_list	NVARCHAR
glide_percise_time	NVARCHAR
glide_time	DATETIME2
glide_var	NVARCHAR
GUID	NVARCHAR
html	NVARCHAR
html_script	NVARCHAR
id	NVARCHAR
image	NVARCHAR
index_name	NVARCHAR
int	NVARCHAR
integer	BIGINT
integer_date	BIGINT
integer_time	BIGINT
internal_type	NVARCHAR
ip_address	NVARCHAR
journal	NVARCHAR
journal_input	NVARCHAR
journal_list	NVARCHAR
long	NVARCHAR
mask_code	NVARCHAR
metric_absolute	FLOAT
metric_counter	FLOAT
metric_derive	FLOAT
metric_gauge	FLOAT
mid_config	NVARCHAR
month_of_year	BIGINT
multi_small	NVARCHAR
multi_two_lines	NVARCHAR
name_values	NVARCHAR
nl_task_init	BIGINT
number	BIGINT
numeric	BIGINT
order_index	BIGINT
password2	NVARCHAR
password	NVARCHAR
percent_complete	FLOAT
phone	NVARCHAR
ph_number	NVARCHAR
phone_number	NVARCHAR
phone_number_e164	NVARCHAR
picklist	NVARCHAR
price	FLOAT
radio	NVARCHAR

Directive	Attribute(s)	Description
<max_datetime>		Customize your temporal database table's maximum date and time stamp in the psp_valid_to column. <max_datetime>2030-12-31 00:00:00</max_datetime> Replace the date and time stamp with any value you would like to assign for custom configuration. NOTE: If records already exist in your temporal database table and then the <maxdatetime> value is changed, any records that were previously timestamped in the psp_valid_to column will not be updated to reflect the newly entered <max_datetime> value.
<archive_datetime/>		Customize your temporal database table's archive date and time stamp in the psp_valid_to column. <archive_datetime>2050-12-31 00:00:00</archive_datetime> Replace the date and time stamp with any value you would like to assign for custom configuration. The date and time stamp is applied to temporal rows when the agent consumes .archive messages. This marks the row as “archived” and helps with querying for archived records in the database tables.
<disable_archive/>		This will disable <archive_datetime/> functionality and skip processing .archive messages
<column>	type="from" type="to"	Customize the column names for the psp_valid_from and psp_valid_to columns by modifying the values within the <column type="from"> and <column type="to"> directives, respectively. <column type="from">psp_valid_from</column> <column type="to">psp_valid_to</column>
<column>	primary_key=”true”	Customize which temporal columns are primary keys by modifying the values within <column primary_key=”true”>. By default, both temporal columns are primary keys. However, they both cannot be non-primary keys, meaning both columns cannot have <column primary_key=”false”>. This is so that it can still insert temporal entries into the database and the database does not complain about duplicate entries. <column primary_key="false">psp_from</column>
<timestamp_difference>		Customize a time period between temporal entries by adding <timestamp_difference> inside <temporal>. <temporal> <timestamp_difference>1</timestamp_difference> </temporal> The value will be read as seconds, so if you want 1 microsecond, you would use 0.000001. The smallest it can go is nanoseconds. By default, the time period is set to 1 millisecond to keep times between entries in order. WARNING: It is not recommended to input a large number in <timestamp_difference>. Doing so would cause the timestamped entries to not be in order as it relies on when the record is processed.
<skip_duplicates/>		Skip inserting duplicate entries by adding directive. <skip_duplicates/> inside <temporal>. NOTE: This may affect the agent’s performance as it checks with the database and does a comparison every time a message comes in. <temporal> <skip_duplicates/> </temporal>
<skip_duplicates/>	check_all_fields="true"	Check the fields values if the updated time is the same from the previous share. <temporal> <skip_duplicates check_all_fields="true"/> </temporal>
<update_on_bulk/>		Use to change the temporal behavior with .bulk messages. With this attribute set to true, the Agent will do the following when a .bulk message is received: 1) Check if the sys_updated_on value of the message is different than the latest temporal record in the table. 2) If the value is different, insert a new temporal record. 3) If the value is the same, update the latest temporal record. This is useful for when new fields are added to a table with new default values but the record itself hasn't been changed otherwise. This feature only applies to .bulk messages as .insert and .update messages will always insert new temporal records. The default value is false if this attribute is not specified in which case the Agent will have default temporal behavior and always insert new temporal records. NOTE: Executing multiple shares for the same table at the same time resulting in multiple messages for the same record may create duplicates or discrepancies. <temporal> <update_on_bulk/> </temporal>

Directive	Example	Description
<batch_insert/>		Self closing tag that configures your Agent to batch SQL INSERT statements.
<batch_update/>		Self closing tag that configures your Agent to batch SQL UPDATE statements.
<batch_flush_interval>	<batch_flush_interval>20</batch_flush_interval>	Number of seconds that will trigger a batch SQL statement execution. Acceptable values are integers from 1 to 30 (seconds).
<max_batch_size>	<max_batch_size>200</max_batch_size>	Number of SQL statements that will trigger a batch SQL statement execution. A larger suggested value is 200.

Connection directive	Example
Message connection	<message_connection attributes>https://acme.perspectium.net</message_connection>
Reporting connection	<reporting_connection attributes>https://acme.perspectium.net</reporting_connection>
Management connection	<management_connection attributes>https://acme.perspectium.net</management_connection>

Proxy protocol, Authentication	Example
HTTP	<message_connection proxy=“http://proxy.acme.net”>https://acme.service-now.com</message_connection>
HTTPS	<message_connection proxy=“https://proxy.acme.net”>https://acme.service-now.</message_connection>
HTTP, proxy authentication	<message_connection proxy=“http://proxy.acme.net” proxy_user=“proxyUser” proxy_password=“password”>https://acme.service-now.com</message_connection>
HTTPS, proxy authentication	<message_connection proxy=“https://proxy.acme.net” proxy_user=“proxyUser” proxy_password=“password”>https://acme.service-now.com</message_connection>
HTTP, target authentication	<message_connection user=“mark” password=“password” proxy=“http://proxy.acme.net”>https://acme.service-now.com</message_connection>
HTTPS, target authentication	<message_connection user=“mark” password=“password” proxy=“https://proxy.acme.net”>https://acme.service-now.com</message_connection>
HTTP, proxy and target authentication	<message_connection user=“mark” password=“password” proxy=“http://proxy.acme.net” proxy_user=“perspectium” proxy_password=“password”>https://acme.service-now.com</message_connection>
HTTPS, proxy and target authentication	<message_connection user=“mark” password=“password” proxy=“https://proxy.acme.net” proxy_user=“perspectium” proxy_password=“password”>https://acme.service-now.com</message_connection>

Directive	Example	Description	Required
heartbeat_queue	<heartbeat_queue>psp.in.heartbeat</heartbeat_queue>	heartbeat message bus queue	Yes
update_interval	<update_interval>60</update_interval>	heartbeat interval	Yes
amqp_uri	<amqp_uri>amqp://localhost</amqp_uri>	Primary Message Bus Location	No
amqp_user	<amqp_user>georvi</amqp_user>	User for logging into primary bus	No
amqp_password	<amqp_password>greetingsFromGeorvi</amqp_password>	User's password for message bus	No

Content

Space Tools

Advance DataSync Agent Configurations

Agent Column Case Configuration

Change data type

DataSync Agent field type mappings

Enable upsert to a database

Excluding column DataSync Agent configuration

Set up a Highly Available Agent

Set up temporal data

Share Table Schemas to a database

Skip altering of database tables

Specify Working Directories for Agent Files

Truncate string values

Key Set Processing for DataSync Agent

Indexing Database tables

Match Field Lengths

Batch Processing

Truncate Before Share

Dynamic Columns Configuration

Installing Third-party Libraries

Configuring Multiple Agents

Configuring the DataSync Agent to Leverage a Proxy

Set a custom primary key

Heartbeat

Encrypting Fields

Encrypting Fields using DP API