Version 22.0.8473

• Overview
• Connector Configuration
  • Settings Tab
    • Database Connection
    • Mappings
  • Automation Tab
    • Automation Settings
    • Performance
  • Advanced Tab
    • Connection
    • Other Settings
    • Miscellaneous
• Establishing a Connection
• Input Mappings
  • Creating an Input Mapping
    • UPSERT
    • INSERT
    • LOOKUP
  • Input Mappings as XML
  • INSERTing into Child Tables
    • REF
    • LOOKUP
    • Child Table XML
• Output Mappings
  • Creating an Output Mapping
    • Output Filters
  • Output Mappings as XML
  • Batching Output
    • Overwriting the SELECT Query
  • Only Process New or Changed Records
    • Using a Last Modified Column
    • Using a Custom Column
  • SELECTing From Multiple Tables
    • Parent-Child Foreign Key Relationships
    • Parent-Child Output Mapping XML
• Input/Output Mappings
  • Creating an Input/Output Mapping
    • Input Parameters
  • Input/Output Mappings as XML
    • Input XML
    • Output XML

Overview

The SQLite Connector can store and retrieve data from SQLite databases.

After establishing a connection to the target database, the connector reads the list of tables/views present in the database. Create an Input Mapping or Output Mapping by selecting an available table and choosing columns from within this table. The connector generates an XML schema structure using the selected table and columns.

After an Input Mapping has been generated, the connector will automatically insert data into the database when it receives XML files that match the XML structure of the Input Mapping. Please see the Input Mappings section for more details.

After an Output Mapping has been generated, the connector will pull data from the database and use the pulled values to populate XML files that have the same XML structure as the output mapping. Please see the Output Mappings section for more details.

Input/Output Mappings are a special kind of mapping used for Stored Procedures that both require input (parameters) and generate output. Please see the Input/Output Mappings section for more details.

Connector Configuration

This section contains all of the configurable connector properties.

Settings Tab

Database Connection

Settings related to establishing the database connection.

• Connector Id The static name of the connector. All connector-specific files are held in a folder by the same name within the Data Directory.
• Connector Description An optional field to provide free-form description of the connector and its role in the flow.
• Settings Format Whether to specify the connection settings as a list of properties or a connection string
• Connection String The database credentials in connection string format. Only applicable when Settings Format is ‘Connection String’, and is used in place of the other connection fields.
• Data Source The path on disk to the SQLite database file.

Mappings

Establish mapping relationships between database tables and XML document structures. Once these mappings are created, XML data can be inserted into the database and data retrieved from the database can be pushed out as XML.

• Input Mappings Input mappings create a relationship between an XML document structure and a database insert (or update). When XML files that match the structure of the input mapping reach the SQLite Connector, the connector makes an insert/update call to the database with the values from the input file. For more information on generating Input mappings, see the Input Mappings section.
• Output Mappings Output mappings create a relationship between an XML document structure and a database select statement. When the connector receives data from the database, it populates an XML file with the same XML structure as the Output mapping. This XML file is then pushed on to the next connector in the Arc flow. For more information on generating Output mappings, see the Output Mappings section.

Automation Tab

Automation Settings

Settings related to the automatic processing of files by the connector.

• Send Whether XML files that match an Input mapping will automatically be inserted into the database.
• Retry Interval The amount of time before a failed insert/update is retried.
• Max Attempts The maximum number of times a failed insert/update will be retried before an error is thrown by the connector.
• Receive Whether data should automatically be pulled from the target database and used to generate XML files that match the configured Output mappings.
• Receive Execution Interval The amount of time between automatic attempts to retrieve data from the database.
• Minutes The number of minutes to wait before downloading. Only applicable when Receive Execution Interval is set to Minute.
• Minutes Past the Hour The minutes offset for an hourly schedule. Only applicable when Receive Execution Interval is set to Hourly. For example, if this value is set to 5, the automation service will pull at 1:05, 2:05, 3:05, etc.
• Time The time within a given day that the download should occur. Only applicable when Receive Execution Interval is set to Daily, or Weekly, or Monthly.
• Day The day on which the pull request should occur. Only applicable when Receive Execution Interval is set to Weekly or Monthly.
• Cron Expression An arbitrary string representing a cron expression that determines when the pull request should occur. Only applicable when Receive Execution Interval is set to Advanced.

Performance

Settings related to the allocation of resources to the connector.

• Max Workers The maximum number of worker threads that will be consumed from the threadpool to process files on this connector. If set, overrides the default setting from the Profile tab.
• Max Files The maximum number of files that will be processed by the connector each time worker threads are assigned to the connector. If set, overrides the default setting from the Profile tab.

Advanced Tab

Connection

A list of special configuration properties. These should only be used in very specific circumstances.

Other Settings

Settings not included in the previous categories.

• Command Timeout The command execution timeout duration in seconds.
• Flat Mapping Scheme Governs which Input Mapping will be used to process incoming flat files based on the filename of the document. Glob matching is supported, and multiple mappings may be defined by separating each mapping with a semicolon. For example, setting this field to ‘*INVOICE*=Invoice;*ORDER*=Order;’ would cause the connector to use the Invoice Input Mapping to process any files with ‘INVOICE’ in the filename, and any other files with ‘ORDER’ in the name would be processed using the Order Input Mapping. Comparisons are case-insensitive.
• Local File Scheme A filemask for determining local file names as they are downloaded by the connector. The following macros may be used to reference contextual information:
  %ConnectorId%, %Filename%, %FilenameNoExt%, %Ext%, %ShortDate%, %LongDate%, %RegexFilename:%, %DateFormat:%.
  As an example: %FilenameNoExt%_%ShortDate%%Ext%
• Log Subfolder Scheme Instructs the connector to group files in the Logs folder according to the selected interval. For example, the Weekly option instructs the connector to create a new subfolder each week and store all logs for the week in that folder. The blank setting tells the connector to save all logs directly in the Logs folder. For connectors that process many transactions, using subfolders can help keep logs organized and improve performance.
• Log Messages Whether the log entry for a processed file will include a copy of the file itself.
• Save to Sent Folder Whether files processed by the connector should be copied to the Sent folder for the connector.
• Output File Format Whether records pulled from the database should be formatted as XML or CSV.
• Parent Connector The connector from which settings should be inherited, unless explicitly overwritten within the existing connector configuration. Must be set to a connector of the same type as the current connector.
• Send Filter A glob pattern filter to determine which files in the Send folder will be uploaded by the connector (e.g. *.txt). Negative patterns may be used to indicate files that should not be uploaded (e.g. -*.tmp). Multiple patterns may be separated by commas, with later filters taking priority except when an exact match is found.

Miscellaneous

Settings for specific use cases.

• Other Settings Allows configuration of hidden connector settings in a semicolon-separated list, like setting1=value1;setting2=value2. Normal connector use cases and functionality should not require use of these settings.

Establishing a Connection

SQLite connections only require setting the Data Source to a valid SQLite database file on disk.

Input Mappings

Input Mappings represent an INSERT/UPDATE to one or more tables in the destination database. They are created through the visual Mapping Editor panel, and then modeled internally as XML. Files processed by the connector that match the XML structure of an Input Mapping will automatically be converted into INSERT or UPDATE queries.

The process of converting XML to database queries is detailed in the Input Mappings as XML section; conceptually, the connector is simply reading values from XML elements and using them when making INSERT/UPDATE statements.

Creating an Input Mapping

Click the ‘Add mapping’ button (+) to create a new mapping. Select the target table/view from the list of available tables in the database to bring up the Mapping Editor panel. In the Mapping Editor, select the table columns that should be included in the INSERT/UPDATE. For simple INSERTs, this is all that is required to generate a mapping.

After an Input Mapping is created, its XML representation can be viewed by clicking the ‘Code’ button (</>) button next to the mapping name.

UPSERT

UPSERT settings govern how the connector will decide whether to INSERT or UPDATE. The most common case is to set UPSERT by: to the primary key of the table. With this configuration, the connector will read the primary key value from the input XML, query the database to see if this key already exists in the table, update the record if it does exist, and insert a new record if it does not. UPSERT by: can be set to columns other than the primary key, but this may result in multiple records being returned from the database. In this case, the connector updates the first record that is returned.

If Perform this query to select the UPSERT key: is set to a SELECT query, the connector will compare the results of this query to the input XML to determine whether to INSERT or UDPATE. This is useful when the primary key for the target table is not available in the input XML, but can be fetched by referencing another column.

For example, imagine that the primary key CustomerID needs to be used to perform UPSERT logic. The input XML does not have the CustomerID, but it does have a CustomerPhoneNumber that uniquely identifies each customer. The UPSERT query for CustomerID could be set to the following:

SELECT CustomerID FROM Customers WHERE CustomerPhoneNumber = @CustomerPhoneNumber

The ‘@’ syntax in the above query indicates that the value should be read from the CustomerPhoneNumber element in the input XML.

The connector will run this query against the database, returning a CustomerID value if a record already exists with the same CustomerPhoneNumber. Then, this CustomerID will be used to UPDATE a record (otherwise a new record will be INSERTed).

INSERT

If UPSERT is disabled in the Input Mapping, the connector will automatically set the query action to INSERT. The connector will attempt to INSERT new records for each input XML file without querying the database to see if the record already exists.

LOOKUP

Some columns have a LOOKUP option available. The connector has detected that these columns have a foreign key relationship with another table in the database. If LOOKUP is enabled, the connector will query this external table prior to INSERTing, and use the value that is returned in the INSERT. The LOOKUP by: field determines what column will be used to query the external table.

As an example, imagine INSERTing a Purchase Order (PO) into a database where one of the fields in the ‘PO’ table is an internal CustomerID. Imagine that the input XML will have a CustomerName element, but not the customer’s internal ID. However, another table in the database, ‘Customers’ has both the name and ID, so it can be used to lookup the ID value based on the name. The connector will recognize that the CustomerID column of the ‘PO’ table is a foreign key, and enable LOOKUP for that column. Setting LOOKUP by: to CustomerName will instruct the connector to query the ‘Customers’ table with a statement like this:

SELECT CustomerID FROM Customers WHERE CustomerName = @CustomerName

The ‘@’ syntax in the above query indicates that the value should be read from the CustomerName element in the input XML.

With this configuration, the connector will first retrieve the CustomerID from ‘Customers’, then use that value when INSERTing into the ‘PO’ table.

Input Mappings as XML

Input Mappings are represented in XML, with the following structure: a parent element identifies the table to insert into, and each child element corresponds to a column in the target table. The Input Mapping establishes the connection between XML elements and database columns, so that the connector can read values from incoming XML documents and use them in INSERT or UPDATE queries.

The following XML is an example Input Mapping for a ‘Students’ table:

<Items>
    <Students action="insert">
        <StudentID key="true" />
        <Name/>
        <Grade type="int"/>
        <GPA/>
    </Students>
</Items>

In the above example, StudentID, Name, Grade, and GPA are each columns of the ‘Students’ table. When the connector processes an XML file that matches this structure, it will automatically insert the values from that XML into the ‘Students’ table. The following is an example XML input file for the above mapping:

<Items>
    <Students>
        <StudentID>12554</StudentID>
        <Name>Ferris Bueller</Name>
        <Year>11</Year>
        <GPA>1.8</GPA>
    </Students>
    <Students>
        <StudentID>12921</StudentID>
        <Name>Hermione Granger</Name>
        <Year>9</Year>
        <GPA>4.0</GPA>
    </Students>
</Items>

When the connector processes the above input file, it will INSERT or UPDATE two records in the ‘Students’ table, one for each Students element. Note that the primary key of the table does not need to be included in the input XML if the database can automatically generate the primary key via AUTOINCREMENT.

INSERTing into Child Tables

Input Mappings support INSERTing into multiple tables with a single mapping. When creating an Input Mapping with multiple tables, the tables should have a foreign key relationship to each other such that one table is the child of another table. Otherwise, use multiple Input Mappings to insert into tables that are not related through a foreign key.

To INSERT into child tables, first create an Input Mapping that targets the parent table. Then add child tables from within the Input Mapping Editor using the +Add button in the upper-left. Select the appropriate table from the list of tables and the designer will automatically populate with the hierarchical structure of tables. Select the columns of the child table that should be included in the Input Mapping.

REF

Some columns within child tables will be marked with a blue ‘REF’ tag. The connector has detected that these columns correspond to the primary key in the parent table. After making the insert into the parent table, and before making the insert into the child table, the connector will query the parent table for the primary key of the record it just inserted. The connector will then use this value when inserting into the child table.

This is useful when the primary key of the parent table is generated by the database, such as an AUTOINCREMENT key. In this case, this column is not passed in as input XML, so the connector does not know the value of the column until after it has INSERTed the record into the parent table.

In the Input Mapping XML, the ‘ref’ attribute indicates that a column can be referenced later or that the value for a column should be read from another column in the mapping. In order to reference the value of columns that are generated by the database (e.g. AUTOINCREMENT primary keys), the column element should include a ‘ref’ attribute set to the reserved value LAST_INSERT_ID:

<film action="upsert">
    <film_id key="true" upsert="film_id" ref="@@LAST_INSERT_ID" />

This indicates that the film_id value will be retrieved after the insert and can be referenced later in the mapping (usually in a child table). To reference this value later, set the ‘ref’ element to the name of the AUTOINCREMENT column:

<film_id key="true" ref="@film_id" />

In the above example, film_id is the name of the primary key in the parent table, and also the name of a column in the child table. The ‘ref’ syntax instructs the connector to retrieve the film_id from the parent table after inserting, and using that retrieved value as the film_id column in the child table.

LOOKUP

LOOKUP logic for child tables is the same as parent tables. Please see the LOOKUP section in Creating an Input Mapping for more details.

Child Table XML

The Input Mapping XML structure will be multi-layered when inserting into parent and child tables. The following XML is an example Input Mapping for two tables related by a foreign key:

<film action="upsert">
    <film_id key="true" upsert="film_id" ref="@@LAST_INSERT_ID" />
    <description />
    <language_id />
    <rating />
    <release_year type="date" />
    <title />
    <film_actor>
        <actor_id key="true" />
        <film_id key="true" ref="@film_id" />
        <last_update type="datetime" />
    </film_actor>
</film>

In the above example, ‘film’ is the parent table and ‘film_actor’ is the child table. The film_id column is the primary key to the ‘film’ table, and a foreign key in the ‘film_actor’ table. When the SQLite Connector processes an input XML file that matches this structure, it will update both the ‘film’ and ‘film_actor’ table and ensure that the keys linking the two records are the same.

Output Mappings

Output Mappings represent a SELECT query from one or more tables in the destination database. They are created through the visual Mapping Editor panel, and then modeled internally as XML. The results returned from the database are used to populate XML documents that match the structure of the Output Mapping. The process of converting database responses to XML is detailed in the Output Mappings as XML section.

Creating an Output Mapping

Click the ‘Add mapping’ button (+) to create a new mapping. Select the target table/view from the list of available tables in the database to bring up the Mapping Editor panel. In the Mapping Editor, select the table columns that should be returned in the SELECT statement. For simple SELECTs, this is all that is required to generate a mapping.

After an Input Mapping is created, the query it represents can be executed by clicking the ‘Execute’ button (a black play button) next to the mapping name. Additionally, the mapping’s XML representation can be viewed by clicking the ‘Code’ button (</>) button next to the mapping name.

Output Filters

The Mapping Editor includes a ‘Filters’ panel to define rules that restrict the records that are returned by the database. Each rule corresponds to a WHERE statement in the resulting SELECT query, and compares a specified column against a specified value; only records with a matching column value will be pulled.

Rules are combined with AND and OR logical operators, and rules can be grouped together to preserve order-of-operations when many rules are applied.

Output Mappings as XML

Output Mappings are represented in XML, with the following structure: a parent element identifies the table to SELECT from, and each child element corresponds to a column in the target table. The Output Mapping establishes the connection between database columns and XML elements, so that the connector can read values from database results and use them to populate XML documents.

The following XML is an example Output Mapping for a ‘Students’ table:

<Items>
    <Students selectQuery="SELECT * FROM `Students` WHERE `GPA` = 4.0">
        <StudentID key="true" />
        <Name/>
        <Grade type="int"/>
        <GPA/>
    </Students>
</Items>

In the above example, StudentID, Name, Grade, and GPA are each columns of the ‘Students’ table. When the Output Mapping is executed, the connector will run the specified selectQuery and generate an XML document for each record that is returned. The following is an example XML Output file for the above mapping:

<Items>
    <Students>
        <StudentID>12921</StudentID>
        <Name>Hermione Granger</Name>
        <Year>9</Year>
        <GPA>4.0</GPA>
    </Students>
</Items>

Batching Output

When querying for output, the connector may be configured to support batching records together into a single document. The Max Records setting may be used to govern how many records should be included in a single message, and the Batch Size setting may be used to indicate how many Batch Messages should be included in a single Batch Group. More information on Batch Groups and Batch Messages can be found here.

Overwriting the SELECT Query

The Output Mapping XML displays the SELECT query that will be run against the target database. The connector builds this query based on the settings in the Output Mapping, but the query can be manually overwritten if fine-grain control over the query is required.

Simply edit the ‘selectQuery’ attribute of the element that shares a name with the database table and set it to any arbitrary SELECT query.

Only Process New or Changed Records

Output Mappings can be configured to only retrieve records that have been updated or added recently. The connector can use two approaches for detecting which records are new or modified.

The first approach requires a DateTime column that represents the last-modified time for records, and the second requires a column that explicitly stores a value representing whether a record should be processed or not.

Using a Last Modified Column

In the Mapping Editor, expand the Advanced settings at the bottom of the panel. If a DateTime column is detected, the option will be available to Use column for processing new or changed records. Set this to the appropriate DateTime column that represents the last-modified time for records.

Using a Custom Column

Output Mappings can be configured to update a column in the table for records that are successfully pulled from the database. This can be combined with a Filter rule that only SELECTs records with a column value indicating that it has not yet been pulled (or should be pulled again).

To enable column updating after processing, expand the Advanced settings at the bottom of the Mapping Editor. Enable Update column with value when rows are processed successfully, then specify which column should be updated and the value that should be set in this column.

For example, an Output Mapping might be configured to update the Processed column to ‘1’ after pulling a record. Then, a Filter rule could be applied that restricts records to only those where Processed not equal 1.

In this example, when the connector processes the output template, it creates a SELECT statement based on the template. The template would execute “SELECT Id, AccountId, Amount FROM INVOICE”, create an XML file for every record in the result set based on the template, and place those files in the Output folder for this connector.

SELECTing From Multiple Tables

Output Mappings support querying multiple tables with a single mapping. When creating an Output Mapping with multiple tables, the tables should have a foreign key relationship to each other such that one table is the child of another table. Otherwise, use multiple Output Mappings to SELECT from tables that are not related through a foreign key.

To query child tables, first create an Output Mapping that targets the parent table. Then add child tables from within the Output Mapping Editor using the +Add button in the upper-left corner. Select the appropriate table from the list of tables and the designer will automatically populate with the hierarchical structure of tables. Select the columns of the child table that should be included in the Output Mapping.

Next, a WHERE filter should be applied to the child table mapping to reflect the foreign key relationship with the parent table.

Parent-Child Foreign Key Relationships

Typically, When SELECTing from a parent table and child table, records should only be pulled from the child table if a foreign key matches the primary key from the parent table.

As an example, imagine an Output Mapping that pulls purchase order data from a parent table, ‘PurchaseOrders’, and a child table, ‘PurchaseOrderLineItems’. Each record (order) pulled from ‘PurchaseOrders’ should also pull the line items associated with that order. The two tables are related by a shared column, for example a PONumber column that is the primary key of the ‘PurchaseOrders’ table and a foreign key in the ‘PurchaseOrdersLineItems’ table. Line item records should only be pulled from the child table if the line item’s PONumber matches the PONumber of the order being pulled from the parent table.

To establish this relationship in an Arc Output Mapping, add a new Filter to the child table mapping and configure it as follows:

• In the first (left-hand) dropdown, select the column of the child table that is the foreign key to the parent table. In the above example, this would be the PONumber column of the ‘PurchaseOrderLineItems’ table.
• In the middle (operator) dropdown, select ‘equal’
• In the last (right-hand) input box, first use the dropdown arrow to the right to select ‘REF’. Afterwards, the input box should have the $ prefix, indicating that it is referencing the parent table
• Use the last (right-hand) dropdown to select the parent table column that should match the child table’s foreign key. In the above example, this would be the PONumber column of the ‘PurchaseOrders’ table.

Parent-Child Output Mapping XML

The XML representation of an Output Mapping that includes a parent and a child table has a hierarchical structure to reflect the parent-child relationship. Both the query to SELECT from the parent table and the query to SELECT from the child table will be included in the XML:

<PuchaseOrders selectQuery="SELECT * FROM `PurchaseOrders` ">
  <PONumber key="true"/>
  <AccountId/>
  <Amount/>
  <AppliedAmount/>
  <Balance/>
  <BillingCity/>
  <BillingCountry/>
  <BillingLine1/>
  <BillingPostalCode/>
  <BillingState/>
  <InvoiceLineItem selectQuery="SELECT * FROM `PurchaseOrderLineItems` WHERE `PONumber` = ${PONumber}">
    <Id key="true"/>
    <PONumber/>
    <ItemAmount/>
    <ItemName/>
    <ItemQuantity/>
  </InvoiceLineItem>
</PurchaseOrders>

The ‘${PONumber}’ syntax in the child table’s query indicates that the value is referencing a column (the PONumber column) from the parent table.

If multiple records are pulled from the ‘PurchaseOrderLineItems’ table, the InvoiceLineItem element (and all of its children) will appear multiple times in the output XML.

Input/Output Mappings

Some database operations both require input and produce output. Input/Output Mappings provide a way to set an XML schema for both input files and output files for the same database operation.

Creating an Input/Output Mapping

Input/Output Mappings are created using the same approach as both Input Mappings and Output Mappings. Each mapping targets a specific Stored Procedure, and the Mapping Editor wizard allows for viewing the expected parameters for the Stored Procedure.

Input Parameters

The input parameters for a Stored Procedure are defined by the database system, so no configuration is required in the Mapping Editor to select the relevant input parameters. Whatever parameters are advertised by the system are displayed in the Mapping Editor, and these cannot be de-selected or otherwise configured.

Input/Output Mappings as XML

Once an Input/Output Mapping has been created, an XML template is generated to match the required input parameters, and an XML template is generated to handle the output from the Stored Procedure.

Input XML

The XML template for input files follows the same principles as XML templates for Input Mappings; files that match the template XML structure will be interpreted as input to the database operation. Please see the Input Mappings as XML section for more details.

Output XML

Output from a Stored Procedure is not defined in advance by the database system. As a result, it may be necessary to provide a test set of input parameters so that the connector can invoke the procedure and detect the structure of the output.

To provide a set of sample inputs, use the Show Sample Data button in the top right of the Mapping Editor. After providing a test set of inputs in the resulting modal, the connector will display the XML structure of the output as it received it. Copy this XML structure or use the Save Sample Data button to use this XML structure in other aspects of the application, such as an XML Map Connector template.