Paylocity Connection Details

Introduction

Connector Version

This documentation is based on version 25.0.9368 of the connector.

Get Started

Paylocity Version Support

The connector leverages the Paylocity API to enable bidirectional access to Paylocity.

Establish a Connection

Connect to Paylocity

Paylocity provides two ways to connect: via the Pay Entry API, and via the standard Paylocity API.

Before you connect, set these properties (if applicable):

• UseSandbox: True if you are using a Sandbox account; otherwise, False.
• CustomFieldsCategory: If IncludeCustomFields is True, set CustomFieldsCategory to the appropriate Custom Fields category. (Default: PayrollAndHR.)

For sites that have opted in to encryption:

• Set only one of the following encryption properties:
  • Key: The AES symmetric key (base 64 encoded) is encrypted with the Paylocity Public Key. This key is used to encrypt Paylocity content. Paylocity decrypts the AES key using RSA decryption. Used if the IV value is not provided.
  • IV: The AES IV (base 64 encoded) used when encrypting Paylocity. If no Key value is provided, IV is generated internally.
• If RSA encryption is enabled in your Paylocity account, set RSAPublicKey to the RSA Key associated with your Paylocity. (This property is required for executing Insert and Update statements.) If the feature is disabled, it is not required.

The Pay Entry API

The Pay Entry API is an extremely limited connection that enables users to automatically submit payroll information for individual employees, and little else. Due to the extremely limited nature of what is offered by the Pay Entry API, it does not have a separate schema.

However, it can be enabled via the UsePayEntryAPI connection property.

The Pay Entry API is completely separate from the rest of the Paylocity API. It uses a separate Client ID and Secret, and must be explicitly requested from Paylocity for access to be granted for an account.

Notes:

• When setting UsePayEntryAPI to True, you may only use the following stored procedures:

  • CreatePayEntryImportBatch
  • MergePayEntryImportBatch
  • Input_TimeEntry
  • available OAuth stored procedures

  Attempts to use other features of the product will result in an error.

• the OAuthAccessToken you obtain for use with the Pay Entry API must be stored separately. This often requires you to set a different OAuthSettingsLocation when using this connection property.

Authenticate to Paylocity

Paylocity supports OAuth authentication for all connections to data, from either Pay Entry API or the standard Paylocity API. To enable this authentication from all OAuth flows, you must set AuthScheme to OAuth, and you must obtain the proper credentials from Paylocity, as described in Obtaining OAuth Credentials.

The following subsections describe how to authenticate to Paylocity from the three most common authentication flows.

For a complete list of connection string properties available in Paylocity, see Connection.

Desktop Applications

To authenticate with your OAuth credentials, you must get and refresh the OAuth access token. After you do that, you are ready to connect.

Get and refresh the OAuth access token:

• InitiateOAuth = GETANDREFRESH. Used to automatically get and refresh the OAuthAccessToken.
• OAuthClientId = The client_id you obtained from Paylocity.
• OAuthClientSecret = The client secret that you obtained from Paylocity.
• CallbackURL = The redirect URI that you registered with Paylocity.

When you connect, the connector opens Paylocity's OAuth endpoint in your default browser. Log in and grant permissions to the application.

After you grant permissions to the application, the connector then completes the OAuth process:

1. The connector obtains an access token from Paylocity and uses it to request data.
2. The OAuth values are saved in the path specified in OAuthSettingsLocation. These values persist across connections.

When the access token expires, the connector refreshes it automatically.

Automatic refresh of the OAuth access token:

To have the connector automatically refresh the OAuth access token, do the following:

1. Before connecting to data for the first time, set the following connection parameters:
   • InitiateOAuth = REFRESH.
   • OAuthClientId = The client_id you obtained from Paylocity.
   • OAuthClientSecret = The secret you obtained from Paylocity.
   • OAuthAccessToken = The access token returned by GetOAuthAccessToken.
   • OAuthSettingsLocation = The path where you want the connector to save the OAuth values, which persist across connections.
2. On subsequent data connections, set the following:
   • InitiateOAuth
   • OAuthSettingsLocation

Manual refresh of the OAuth access token:

The only value needed to manually refresh the OAuth access token is the OAuth refresh token.

1. To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by GetOAuthAccessToken) has elapsed, call the RefreshOAuthAccessToken stored procedure.
2. Set the following connection properties:
   • OAuthClientId = The Client ID you obtained from Paylocity.
   • OAuthClientSecret = The Client Secret you obtained from Paylocity.
3. Call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
4. After the new tokens have been retrieved, set the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken. This opens a new connection.

Store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Paylocity connector.

User Defined Views

The connector supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats. For further information, see the SSLServerCert property under "Connection String Options".

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Paylocity and then processes the rest of the query in memory (client-side).

For further information, see Query Processing.

Log

For an overview of configuration settings that can be used to refine logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

User Defined Views

The Paylocity connector supports the use of user defined views: user-defined virtual tables whose contents are decided by a preconfigured query. User defined views are useful in situations where you cannot directly control the query being issued to the driver; for example, when using the driver from Jitterbit.

Use a user defined view to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File

User defined views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This user defined view configuration file is formatted so that each root element defines the name of a view, and includes a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Employee WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

In order to avoid a view's name clashing with an actual entity in the data model, user defined views are exposed in the UserViews schema by default. To change the name of the schema used for UserViews, reset the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a user defined view called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a user defined view that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Client SSL Certificates

The Paylocity connector also supports setting client certificates. Set the following to connect using a client certificate.

• SSLClientCert: The name of the certificate store for the client certificate.
• SSLClientCertType: The type of key store containing the TLS/SSL client certificate.
• SSLClientCertPassword: The password for the TLS/SSL client certificate.
• SSLClientCertSubject: The subject of the TLS/SSL client certificate.

Data Model

Overview

This section shows the available API objects and provides more information on executing SQL to Paylocity APIs.

Key Features

• The connector models Paylocity entities like documents, folders, and groups as relational views, allowing you to write SQL to query Paylocity data.
• Stored procedures allow you to execute operations to Paylocity.
• Live connectivity to these objects means any changes to your Paylocity account are immediately reflected when using the connector.

Tables

Tables describes the available tables. Tables are statically defined to model resources such as Earnings, LocalTaxes, and EmployeeDetails.

Views

Views describes the available views. Views are statically defined to model resources such as Employees, CompanyCodes, and CustomFields.

Stored Procedures

Stored Procedures are function-like interfaces to Paylocity. Stored procedures allow you to execute operations to Paylocity, including downloading documents and moving envelopes.

Tables

The connector models the data in Paylocity as a list of tables in a relational database that can be queried using standard SQL statements.

Paylocity Connector Tables

AdditionalRates

Add/update additional rates

Table Specific Information

Select

The connector will use the Paylocity API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• EmployeeId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM AdditionalRates WHERE EmployeeId = '123'

Insert

Following is an example of how to inserting into AdditionalRates table. For example:

INSERT INTO AdditionalRates (employeeid, Shift, Job, CostCenter1, CostCenter2, CostCenter3,  EffectiveDate, ChangeReason, RateCode, Rate, RatePer, RateNotes) VALUES ('123', '1', '0000123', '100', '101', '100', '2021-02-09', 'Cost of Living Increase', 'CN', 500, 'week', 'NewRate')

Update

Following is an example of how to Update a AdditionalRates table:

UPDATE AdditionalRates SET CostCenter1 = '100', ratecode = 'CN', rate = 100, rateper = 'hour' WHERE employeeId = '123'

Columns

Earnings

Get All Earnings. Delete Earning by Earning Code and Start Date. Add/Update Earning. Get Earnings by Earning Code. Get Earning by Earning Code and Start Date

Table Specific Information

Select

The connector will use the Paylocity API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• EmployeeId supports the '=' comparison.
• EarningCode supports the '=' comparison.
• StartDate supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Earnings WHERE EmployeeId = '123'

SELECT * FROM Earnings WHERE EmployeeId = '123' AND EarningCode = '1098'

SELECT * FROM Earnings WHERE EmployeeId = '123' AND EarningCode = '1098' AND StartDate = '2019-04-01'

Insert

Following is an example of how to inserting into Earnings table. For example:

INSERT INTO Earnings (EmployeeId, EarningCode, StartDate) VALUES ('999621027', '1098', '2020-02-10')

Update

Following is an example of how to update a Earnings table:

UPDATE Earnings SET EarningCode = '1098', StartDate = '2020-02-09' WHERE employeeId = '999621027'

Delete

Following is an example of how to delete earnings in the Earnings table:

DELETE FROM Earnings WHERE employeeId = '123' AND EarningCode = '1' AND StartDate = '2021-01-01'

Columns

EmergencyContacts

Add/update emergency contacts

Table Specific Information

Select

The connector will use the Paylocity API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• EmployeeId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM EmergencyContacts WHERE EmployeeId = '123'

Insert

Following is an example of how to inserting into EmergencyContacts table. For example:

INSERT INTO EmergencyContacts (EmployeeId, firstName, lastName, primaryPhone, priority, relationship, MobilePhone, Zip, Notes, Address1, City, County) VALUES ('123', 'firstName', 'lastName', 'M', 'P', 'Brother', 9876543210, 75791, 'Test Notes', 'Address Line1', 'NY', 'USA')

Update

Following is an example of how to Update a EmergencyContacts table:

UPDATE EmergencyContacts SET WorkPhone = '9876543210', firstName = 'firstName', lastName = 'lastName', primaryPhone = 'W', priority = 'S', relationship = 'brother' WHERE employeeId = '999621027'

Columns

EmployeeBenefitSetup

Add/update employee's benefit setup

Table Specific Information

Select

The connector will use the Paylocity API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• EmployeeId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM EmployeeBenefitSetup WHERE EmployeeId = '123'

Insert

Following is an example of how to inserting into EmployeeBenefitSetup table. For example:

INSERT INTO EmployeeBenefitSetup (EmployeeId, BenefitSalary, BenefitClass, BenefitSalaryEffectiveDate, BenefitClassEffectiveDate) VALUES ('123', '250', 'FULL', '2021-02-10', '2021-02-10')

Update

Following is an example of how to Update a EmployeeBenefitSetup table:

UPDATE EmployeeBenefitSetup SET BenefitSalary = '250', BenefitClass = 'FULL', BenefitClassEffectiveDate = '2021-02-10', BenefitSalaryEffectiveDate = '2021-02-10' WHERE EmployeeId = '123'

Columns

EmployeeDetails

Add new employee. Get employee. Update employee

Table Specific Information

Select

The connector will use the Paylocity API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• EmployeeId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Employee WHERE EmployeeId = '123'

Insert

Following is an example of how to inserting into Employee table with aggregate column(Optional). For example:

INSERT INTO AdditionalRates#TEMP (Shift, Job, CostCenter1, CostCenter2, CostCenter3,  EffectiveDate, ChangeReason, RateCode, Rate, RatePer, RateNotes) VALUES ( '1', '0000123', '100', '101', '100', '2021-02-09', 'Cost of Living Increase', 'CN', 500, 'week', 'NewRate')

INSERT INTO Employee (FirstName, LastName, MiddleName, Gender, MaritalStatus, PreferredName, PriorLastName, Salutation, Currency, BirthDate, CompanyFEIN, CompanyName, DepartmentPositionChangeReason, DepartmentPositionCostCenter1, DepartmentPositionCostCenter2, DepartmentPositionCostCenter3, DepartmentPositionEffectiveDate, DepartmentPositionEmployeeType, DepartmentPositionIsMinimumWageExempt, DepartmentPositionIsOvertimeExempt, DepartmentPositionIsSupervisorReviewer, DepartmentPositionIsUnionDuesCollected, DepartmentPositionIsUnionInitiationCollected, DepartmentPositionJobTitle, FederalTaxAmount, FederalTaxExemptions, FederalTaxFilingStatus, FederalTaxPercentage, FederalTaxTaxCalculationCode, FederalTaxW4FormYear, HomeAddressAddress1, HomeAddressAddress2, HomeAddressCity, HomeAddressCountry, HomeAddressEmailAddress, HomeAddressMobilePhone, HomeAddressPhone, HomeAddressPostalCode, HomeAddressState, NonPrimaryStateTaxFilingStatus, NonPrimaryStateTaxHigherRate, NonPrimaryStateTaxOtherIncomeAmount, NonPrimaryStateTaxReciprocityCode, NonPrimaryStateTaxtaxCode, PrimaryPayRateAnnualSalary, PrimaryPayRateBaseRate, PrimaryPayRateChangeReason, PrimaryPayRateDefaultHours, PrimaryPayRateEffectiveDate, PrimaryPayRatePayFrequency, PrimaryPayRatePayType, PrimaryStateTaxAmount, PrimaryStateTaxExemptions, PrimaryStateTaxExemptions2, PrimaryStateTaxPercentage, PrimaryStateTaxFilingStatus, PrimaryStateTaxTaxCalculationCode, PrimaryStateTaxTaxCode, PrimaryStateTaxW4FormYear, StatusAdjustedSeniorityDate, StatusChangeReason, StatusEffectiveDate, StatusEmployeeStatus, StatusHireDate, StatusIsEligibleForRehire, TaxSetupSuiState, TaxSetupTaxForm, WorkAddressAddress1, WorkAddressCity, WorkAddressCountry, WorkAddressMobilePhone, WorkAddressPostalCode, WorkAddressState, suffix, IsSmoker, AdditionalRate) VALUES ('TestFirstName1', 'TestLastName', 'TestMiddleName', 'M', 'S', 'Test1', 'TestPrior', 'Mr', 'USD', '1985-11-01', '00-5554442', 'SusanW  Z2222 Clone', 'New Hire', '100', '101', '100', '2021-02-01', 'RFT', false, true, false, false, false, 'Clinical Psychologist', '1', '0', 'M', '1', 'AFAP', '2021', 'TestAddr1', 'TestAddr2', 'NewJordi', 'USA', 'test@test.com', '(961)062-1234', '(588)148-1234', '75791', 'NY', 'M', 'false', 0, 'Both', 'NY', '1000', '100', 'New Hire', '8', '2021-02-01', 'M', 'Salary', '1', '0', '0', 5, 'S', 'AFAP', 'AZ', '2019', '2021-02-01', 'New Hire', '2021-02-01', 'A', '2021-02-01', true, 'NY', 'W2', '78FraleighStreet', 'RedHook', 'USA', '(276)369-1234', '12571', 'NY', 'Jr.', true, 'AdditionalRates#TEMP')

Update

Following is an example of how to Update a Employee table:

UPDATE Employee SET PriorLastName = 'PriorLastName', [CF_PayrollAndHR_Full Computer Access] = true WHERE EmployeeId = '123'

Columns

Input_TimeEntry

To create the csv content for the CreatePayEntryImportBatch.TimeEntry using this as a TEMP Table. When the connection to Paylocity is closed, all tables names started with Input are cleared.

Columns

LocalTaxes

Get local taxes by tax code. Get all local taxes. Add new local tax. Delete local tax by tax code

Table Specific Information

Select

The connector will use the Paylocity API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• EmployeeId supports the '=' comparison.
• TaxCode supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM LocalTaxes WHERE EmployeeId = '123'

SELECT * FROM LocalTaxes WHERE EmployeeId = '123' AND TaxCode = 'AL-BIR1'

Insert

Following is an example of how to inserting into LocalTaxes table. For example:

INSERT INTO LocalTaxes (EmployeeId, TaxCode, FilingStatus) VALUES ('999621027', 'AL-BIR1', 'N/A')

Delete

Following is an example of how to Delete LocalTaxes from the table:

DELETE FROM LocalTaxes WHERE employeeId = '123' AND TaxCode = 'AL-BIR1'

Columns

NonPrimaryStateTax

Add/update non-primary state tax

Table Specific Information

Select

The connector will use the Paylocity API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• EmployeeId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM NonPrimaryStateTax WHERE EmployeeId = '123'

Insert

Following is an example of how to inserting into Non-PrimaryStateTax table. For example:

INSERT INTO [NonPrimaryStateTax] (Employeeid, TaxCalculationCode, Amount, TaxCode, ReciprocityCode, FilingStatus, W4FormYear, Percentage, Exemptions) VALUES ('123', 'AFAP', 50, 'NY', 'Both', 'M', 2019, 1.5, 5)

Update

Following is an example of how to Update a Non-PrimaryStateTax table:

UPDATE [NonPrimaryStateTax] SET Percentage = 2.5, Exemptions = 10 WHERE employeeId = '123'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

PrimaryStateTax

Add/update primary state tax

Table Specific Information

Select

The connector will use the Paylocity API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• EmployeeId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM PrimaryStateTax WHERE EmployeeId = '123'

Insert

Following is an example of how to inserting into PrimaryStateTax table. For example:

INSERT INTO PrimaryStateTax (Employeeid, FilingStatus, TaxCalculationCode, Exemptions, Exemptions2, Percentage, W4FormYear, TaxCode, Amount) VALUES ('999621027', 'MS', 'AFAP', 10, 15, 5, 2019, GA, 100)

Update

Following is an example of how to Update a PrimaryStateTax table:

UPDATE PrimaryStateTax SET Exemptions = 20, Amount = 125 WHERE employeeId = '123'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

SensitiveData

Gets employee sensitive data information directly from Web Pay.

Table Specific Information

Select

The connector will use the Paylocity API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• EmployeeId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM SensitiveData WHERE EmployeeId = '1234'

Insert

Following is an example of how to Insert into SensitiveData table.

INSERT INTO SensitiveData (EmployeeId, Disability, DisabilityClassifications, HasDisability, IsVeteran) VALUES ('1234', 'ads', 'dawd, afsc', '1', '0')

Update

Following is an example of how to Update a SensitiveData table:

UPDATE SensitiveData SET Disability = 'ads', DisabilityClassifications = 'dawd,afsc', HasDisability = '1' WHERE EmployeeId = '1234'

Columns

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Paylocity Connector Views

CompanyCodes

Get All Company Codes

Table Specific Information

Select

The connector will use the Paylocity API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• codeResource supports the '=' comparison.

In the case of simple SELECTs, the CodeResource value will be considered as costCenter1. For example, the following queries are processed server side:

SELECT * FROM CompanyCodes WHERE codeResource = 'costCenter1'

Columns

CustomFields

Get All Custom Fields

Table Specific Information

Select

The connector will use the Paylocity API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• category supports the '=' comparison.

In the case of simple SELECTs, the Category value will be considered as PayrollAndHR. For example, the following queries are processed server side:

SELECT * FROM CustomFields WHERE Category = 'PayrollAndHR'

Columns

DirectDeposit

Get All Direct Deposit

Table Specific Information

Select

The connector will use the Paylocity API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• EmployeeId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM DirectDeposit WHERE EmployeeId = '123'

Columns

EmployeePunchDetails

Get employee punch details.

Columns

Name	Type	References	Description
EmployeeId [KEY]	String	Employees.EmployeeId	The ID of the Employee.
SegmentsPunchId [KEY]	String		A unique identifier assigned to each punch or time entry, ensuring that each entry is distinct, traceable, and easily referenced for tracking purposes.
CompanyId	String		The Paylocity Company Id. This is the unique value provided by Paylocity to each specific Payroll Entity.
BadgeNumber	Integer		The Time and Labor badge number.
RelativeStart	Datetime		The relative DateTime for the beginning of the WorkedShift in the format yyyy-MM-ddThhss.
RelativeEnd	Datetime		The relative DateTime for the end of the WorkedShift in the format yyyy-MM-ddThhss.
SegmentsOrigin	String		The associated origin of the time entry.
SegmentsDate	Date		The date the worked shift segment took place. In the case of overnight shifts, this is the date these worked hours apply to in payroll and can play a role in some overtime calculations.
SegmentsPunchType	String		The type of punch recorded, indicating categories such as work or lunch break.
SegmentsRelativeStart	Datetime		The relative DateTime of the beginning of this segment after payroll policy rules are applied. This value is Null if the segment began with a missed punch.
SegmentsRelativeEnd	Datetime		The relative DateTime of the end of this segment after payroll policy rules are applied. This value is Null if the segment ended with a missed punch.
SegmentsRelativeOriginalStart	Datetime		The relative DateTimeOffset of the beginning of this segment before payroll policy rules are applied. This value is Null if the segment began with a missed punch.
SegmentsRelativeOriginalEnd	Datetime		The relative DateTime of the end of this segment before payroll policy rules are applied. This value is Null if the segment ended with a missed punch.
SegmentsDurationHours	Double		The total hours worked by the employee for a particular punch or time entry, reflecting the duration of their work.
SegmentsEarnings	Double		The total earnings accrued by the employee for the specific punch or time entry, including wages, bonuses, or additional compensation.
SegmentsCostCenters	String		A list of cost centers, at most one per cost center level, that this segment of the worked shift is allocated to.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Employees

Get all employee info

Table Specific Information

Select

The Paylocity does not support any column for filtering this table. All filters will be executed client side.

Columns

PayStatementsDetails

Get employee pay statement details data for the specified year.. Get employee pay statement details data for the specified year and check date.

Table Specific Information

Select

The connector will use the Paylocity API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• EmployeeId supports the '=' comparison.
• Year supports the '=' comparison.
• CheckDate supports the '=' comparison.

In the case of simple SELECT the Paylocity will return present and last year records. For example, the following queries are processed server side:

SELECT * FROM PayStatementsDetails WHERE EmployeeId = '24' AND Year = 2019

SELECT * FROM PayStatementsDetails WHERE EmployeeId = '24' AND Year = 2019 AND CheckDate = '2019-02-01'

Columns

PayStatementsSummary

Get employee pay statement summary data for the specified year.. Get employee pay statement summary data for the specified year and check date.

Table Specific Information

Select

The connector will use the Paylocity API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• EmployeeId supports the '=' comparison.
• Year supports the '=' comparison.
• CheckDate supports the '=' comparison.

In the case of simple SELECTs, the Paylocity will return records from the current and previous year.

For example, the following queries are processed server side:

SELECT * FROM PayStatementsSummary WHERE EmployeeId = '123' AND Year = 2019

SELECT * FROM PayStatementsSummary WHERE EmployeeId = '123' AND Year = 2019 AND CheckDate = '2019-02-01'

Columns

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Paylocity.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Paylocity, along with an indication of whether the procedure succeeded or failed.

Paylocity Connector Stored Procedures

CreatePayEntryImportBatch

Create or merge Pay Entry Time Tracking Batch.

Stored Procedures Specific Information

In order to insert into CreatePayEntryImportBatch, you must submit the TimeEntry details and a Pay Period. The TimeEntry details may be submitted via a Temp table by first inserting to the Input_TimeEntry input only table. The Pay Period may be specified by setting the PayPeriodBeginDate, PayPeriodEndDate, and CheckDate. Alternatively, the CalendarId may be specified. For example:

INSERT INTO Input_TimeEntry (EmployeeId, Det, DetCode, Hours, Amount, BeginDate, EndDate) VALUES (999623635, 'E', 'REG', 40.00, 30, '2021-08-08', '2021-08-14')
INSERT INTO Input_TimeEntry (EmployeeId, Det, DetCode, Hours, Amount, BeginDate, EndDate) VALUES (999623636, 'E', 'REG', 40.00, 25, '2021-08-08', '2021-08-14')

EXECUTE CreatePayEntryImportBatch TimeEntry = 'Input_TimeEntry#TEMP', AutoAcknowledge = 'true', BatchName = 'Test', PayPeriodBeginDate = '2021-08-15', PayPeriodEndDate = '2021-08-21', CheckDate = '2021-08-27'

To fetch the processed data information, use the below query.

SELECT * FROM LastResultInfo#TEMP

Input

Result Set Columns

CreateSchema

Creates a schema definition of a table in Paylocity.

CreateSchema

Creates a local schema file (.rsd) from an existing table or view in the data model.

The schema file is created in the directory set in the Location connection property when this procedure is executed. You can edit the file to include or exclude columns, rename columns, or adjust column datatypes.

The connector checks the Location to determine if the names of any .rsd files match a table or view in the data model. If there is a duplicate, the schema file will take precedence over the default instance of this table in the data model. If a schema file is present in Location that does not match an existing table or view, a new table or view entry is added to the data model of the connector.

Input

Result Set Columns

GetOAuthAccessToken

Gets an authentication token from Paylocity.

Result Set Columns

MergePayEntryImportBatch

This is used to create or merge a batch when the Pay Entry Import Status is halted at TimeClockValidationPendingAcknowledgement.

Stored Procedures Specific Information

TimeImportFileTrackingId is required to execute this procedure. The TimeImportFileTrackingId will be generated as the response of CreatePayEntryImportBatch Stored Procedure. For example:

EXECUTE MergePayEntryImportBatch TimeImportFileTrackingId = 'e84b3808-4477-423a-89bf-e32f58c0e685'

Input

Result Set Columns

OnBoardingEmployee

Onboarding API sends employee data into Paylocity Onboarding to help ensure an easy and accurate hiring process for subsequent completion into Web Pay.

Stored Procedures Specific Information

Paylocity allows multiple columns to be used in the Exec query. These columns can typically be used with only = comparison. FirstName and LastName params are required to execute this procedure. For example:

EXECUTE OnBoardingEmployee FirstName = 'EmpFirstName', LastName = 'EmpLastName', BaseRate = 350.0, CostCenter1 = '100', DefaultHours = '9', EmployeeStatus = 'A', MaritalStatus = 'S'

Input

Result Set Columns

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with Paylocity.

Result Set Columns

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Paylocity:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries:

• sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Employee table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Employee'

Columns

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns

sys_procedureparameters

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the CreateSchema stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'CreateSchema' AND Direction = 1 OR Direction = 2

To include result set columns in addition to the parameters, set the IncludeResultColumns pseudo column to True:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'CreateSchema' AND IncludeResultColumns='True'

Columns

Pseudo-Columns

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the Employee table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Employee'

Columns

sys_foreignkeys

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

Columns

sys_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys

Columns

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity

Columns

sys_information

Describes the available system information.

The following query retrieves all columns:

SELECT * FROM sys_information

Columns

Advanced Configurations Properties

The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.

Connection

OAuth

SSL

Schema

Miscellaneous

Connection

This section provides a complete list of connection properties you can configure.

CompanyId

The ID of the company file.

Data Type

string

Default Value

""

Remarks

The ID of the company file.

CustomFieldsCategory

The custom field category in the company.

Data Type

string

Default Value

PayrollAndHR

Remarks

Add comma separated values to set more than one custom fields category. The following is an example of a custom field category:

• PayrollAndHR

UseSandbox

Set to true if you you are using sandbox account.

Data Type

bool

Default Value

false

Remarks

Set to true if you you are using sandbox account.

RSAPublicKey

Set this to the public key shared by Paylocity.

Data Type

string

Default Value

""

Remarks

Either enter the key value or complete path of the file. This is required for executing Insert and Update statements.

Key

The secret key for the symmetric algorithm.

Data Type

string

Default Value

""

Remarks

The AES symmetric key(base 64 encoded) encrypted with the Paylocity Public Key. Paylocity will decrypt the AES key using RSA decryption and use it to decrypt the content. It is an optional property if the IV value is not provided. In this case, the driver will generate a key internally.

IV

The initialization vector (IV).

Data Type

string

Default Value

""

Remarks

The AES IV (base 64 encoded) used when encrypting the content. If you have set the Key, this property is optional because the driver generates an IV internally.

UsePayEntryAPI

Set to true if you want to access the Pay Entry API. When this property is set to true, you should specify different location for OAuthSettingsFile.

Data Type

bool

Default Value

false

Remarks

Set to true if you want to access the Pay Entry API. When this property is set to true, you should specify different location for OAuthSettingsFile.

OAuth

This section provides a complete list of OAuth properties you can configure.

InitiateOAuth

Specifies the process for obtaining or refreshing the OAuth access token, which maintains user access while an authenticated, authorized user is working.

Possible Values

OFF, REFRESH, GETANDREFRESH

Data Type

string

Default Value

OFF

Remarks

OAuth is an authorization framework that enables applications to obtain limited access to user accounts on an HTTP service. The OAuth flow defines the method to be used for logging in users, exchanging their credentials for an OAuth access token to be used for authentication, and providing limited access to applications.

Paylocity supports the following options for initiating OAuth access:

1. OFF: No automatic OAuth flow initiation. The OAuth flow is handled entirely by the user, who will take action to obtain their OAuthAccessToken. Note that with this setting the user must refresh the token manually and reconnect with an updated OAuthAccessToken property when the current token expires.
2. GETANDREFRESH: The OAuth flow is handled entirely by the connector. If a token already exists, it is refreshed when necessary. If no token currently exists, it will be obtained by prompting the user to login.
3. REFRESH: The user handles obtaining the OAuth Access Token and sets up the sequence for refreshing the OAuth Access Token. (The user is never prompted to log in to authenticate. After the user logs in, the connector handles the refresh of the OAuth Access Token.

OAuthClientId

Specifies the client ID (also known as the consumer key) assigned to your custom OAuth application. This ID is required to identify the application to the OAuth authorization server during authentication.

Data Type

string

Default Value

""

Remarks

This property is required when using a custom OAuth application, such as in web-based authentication flows, service-based authentication, or certificate-based flows that require application registration. It is also required if an embedded OAuth application is not available for the driver. When an embedded OAuth application is available, this value may already be provided by the connector and not require manual entry.

This value is generally used alongside other OAuth-related properties such as OAuthClientSecret and OAuthSettingsLocation when configuring an authenticated connection.

OAuthClientId is one of the key connection parameters that need to be set before users can authenticate via OAuth. You can typically find this value in your identity provider’s application registration settings. Look for a field labeled Client ID, Application ID, or Consumer Key.

While the client ID is not considered a confidential value like a client secret, it is still part of your application's identity and should be handled carefully. Avoid exposing it in public repositories or shared configuration files.

OAuthClientSecret

Specifies the client secret assigned to your custom OAuth application. This confidential value is used to authenticate the application to the OAuth authorization server.

Data Type

string

Default Value

""

Remarks

This property is required when using a custom OAuth application in any flow that requires secure client authentication, such as web-based OAuth, service-based connections, or certificate-based authorization flows. It is not required when using an embedded OAuth application.

The client secret is used during the token exchange step of the OAuth flow, when the driver requests an access token from the authorization server. If this value is missing or incorrect, authentication will fail, and the server may return an invalid_client or unauthorized_client error.

OAuthClientSecret is one of the key connection parameters that need to be set before users can authenticate via OAuth. You can obtain this value from your identity provider when registering the OAuth application. It may be referred to as the client secret, application secret, or consumer secret.

This value should be stored securely and never exposed in public repositories, scripts, or unsecured environments. Client secrets may also expire after a set period. Be sure to monitor expiration dates and rotate secrets as needed to maintain uninterrupted access.

OAuthAccessToken

Specifies the OAuth access token used to authenticate requests to the data source. This token is issued by the authorization server after a successful OAuth exchange.

Data Type

string

Default Value

""

Remarks

The OAuthAccessToken is a temporary credential that authorizes access to protected resources. It is typically returned by the identity provider after the user or client application completes an OAuth authentication flow. This property is most commonly used in automated workflows or custom OAuth implementations where you want to manage token handling outside of the driver.

The OAuth access token has a server-dependent timeout, limiting user access. This is set using the OAuthExpiresIn property. However, it can be reissued between requests to keep access alive as long as the user keeps working.

If InitiateOAuth is set to REFRESH, we recommend that you also set both OAuthExpiresIn and OAuthTokenTimestamp. The connector uses these properties to determine when the token expires so it can refresh most efficiently. If OAuthExpiresIn and OAuthTokenTimestamp are not specified, the connector refreshes the token immediately.

Access tokens should be treated as sensitive credentials and stored securely. Avoid exposing them in logs, scripts, or configuration files that are not access-controlled.

OAuthSettingsLocation

Specifies the location of the settings file where OAuth values are saved. Storing OAuth settings in a central location avoids the need for users to enter OAuth connection properties manually each time they log in. It also enables credentials to be shared across connections or processes.

Data Type

string

Default Value

%APPDATA%\Paylocity Data Provider\OAuthSettings.txt

Remarks

You can store OAuth values in a central file for shared access to those values, in either of the following ways:

• Set InitiateOAuth to either GETANDREFRESH or REFRESH and specify a filepath to the OAuth settings file.
• Use memory storage to load the credentials into static memory.

The following sections provide more detail on each of these methods.

Specifying the OAuthSettingsLocation Filepath

The default OAuth setting location is %APPDATA%\Paylocity Data Provider\OAuthSettings.txt, with %APPDATA% set to the user's configuration directory.

Default values vary, depending on the user's operating system.

• Windows (ODBC and Power BI): registry://%DSN%
• Windows: %APPDATA%Paylocity Data Provider\OAuthSettings.txt
• Mac: %APPDATA%//Paylocity Data Provider/OAuthSettings.txt
• Linux: %APPDATA%//Paylocity Data Provider/OAuthSettings.txt

Loading Credentials Via Memory Storage

Memory locations are specified by using a value starting with memory://, followed by a unique identifier for that set of credentials (for example, memory://user1). The identifier can be anything you choose, but it should be unique to the user.

Unlike file-based storage, where credentials persist across connections, memory storage loads the credentials into static memory and the credentials are shared between connections using the same identifier for the life of the process. To persist credentials outside the current process, you must manually store the credentials prior to closing the connection. This enables you to set them in the connection when the process is started again.

To retrieve OAuth property values, query the sys_connection_props system table. If there are multiple connections using the same credentials, the properties are read from the previously closed connection.

Supported Storage Types

• memory://: Stores OAuth tokens in-memory (unique identifier, shared within same process, etc.)
• registry://: Only supported in the Windows ODBC and Power BI editions. Stores OAuth tokens in the registry under the DSN settings. Must end in a DSN name like registry://Paylocity connector Data Source, or registry://%DSN%.
• %DSN%: The name of the DSN you are connecting with.
• Default (no prefix): Stores OAuth tokens within files. The value can be either an absolute path, or a path starting with %APPDATA% or %PROGRAMFILES%.

OAuthRefreshToken

Specifies the OAuth refresh token used to request a new access token after the original has expired.

Data Type

string

Default Value

""

Remarks

The refresh token is used to obtain a new access token when the current one expires. It enables seamless authentication for long-running or automated workflows without requiring the user to log in again. This property is especially important in headless, CI/CD, or server-based environments where interactive authentication is not possible.

The refresh token is typically obtained during the initial OAuth exchange by calling the GetOAuthAccessToken stored procedure. After that, it can be set using this property to enable automatic token refresh, or passed to the RefreshOAuthAccessToken stored procedure if you prefer to manage the refresh manually.

When InitiateOAuth is set to REFRESH, the driver uses this token to retrieve a new access token automatically. After the first refresh, the driver saves updated tokens in the location defined by OAuthSettingsLocation, and uses those values for subsequent connections.

The OAuthRefreshToken should be handled securely and stored in a trusted location. Like access tokens, refresh tokens can expire or be revoked depending on the identity provider’s policies.

OAuthExpiresIn

Specifies the duration in seconds, of an OAuth Access Token's lifetime. The token can be reissued to keep access alive as long as the user keeps working.

Data Type

string

Default Value

""

Remarks

The OAuth Access Token is assigned to an authenticated user, granting that user access to the network for a specified period of time. The access token is used in place of the user's login ID and password, which stay on the server.

An access token created by the server is only valid for a limited time. OAuthExpiresIn is the number of seconds the token is valid from when it was created. For example, a token generated at 2024-01-29 20:00:00 UTC that expires at 2024-01-29 21:00:00 UTC (an hour later) would have an OAuthExpiresIn value of 3600, no matter what the current time is.

To determine how long the user has before the Access Token will expire, use OAuthTokenTimestamp.

OAuthTokenTimestamp

Displays a Unix epoch timestamp in milliseconds that shows how long ago the current Access Token was created.

Data Type

string

Default Value

""

Remarks

The OAuth Access Token is assigned to an authenticated user, granting that user access to the network for a specified period of time. The access token is used in place of the user's login ID and password, which stay on the server.

An access token created by the server is only valid for a limited time. OAuthTokenTimestamp is the Unix timestamp when the server created the token. For example, OAuthTokenTimestamp=1706558400 indicates the OAuthAccessToken was generated by the server at 2024-01-29 20:00:00 UTC.

SSL

This section provides a complete list of SSL properties you can configure.

SSLClientCert

Specifies the TLS/SSL client certificate store for SSL Client Authentication (2-way SSL). This property works in conjunction with other SSL-related properties to establish a secure connection.

Data Type

string

Default Value

""

Remarks

This property specifies the client certificate store for SSL Client Authentication. Use this property alongside SSLClientCertType, which defines the type of the certificate store, and SSLClientCertPassword, which specifies the password for password-protected stores. When SSLClientCert is set and SSLClientCertSubject is configured, the driver searches for a certificate matching the specified subject.

Certificate store designations vary by platform. On Windows, certificate stores are identified by names such as MY (personal certificates), while in Java, the certificate store is typically a file containing certificates and optional private keys.

The following are designations of the most common User and Machine certificate stores in Windows:

For PFXFile types, set this property to the filename. For PFXBlob types, set this property to the binary contents of the file in PKCS12 format.

SSLClientCertType

Specifies the type of key store containing the TLS/SSL client certificate for SSL Client Authentication. Choose from a variety of key store formats depending on your platform and certificate source.

Possible Values

USER, MACHINE, PFXFILE, PFXBLOB, JKSFILE, JKSBLOB, PEMKEY_FILE, PEMKEY_BLOB, PUBLIC_KEY_FILE, PUBLIC_KEY_BLOB, SSHPUBLIC_KEY_FILE, SSHPUBLIC_KEY_BLOB, P7BFILE, PPKFILE, XMLFILE, XMLBLOB, BCFKSFILE, BCFKSBLOB

Data Type

string

Default Value

USER

Remarks

This property determines the format and location of the key store used to provide the client certificate. Supported values include platform-specific and universal key store formats. The available values and their usage are:

SSLClientCertPassword

Specifes the password required to access the TLS/SSL client certificate store. Use this property if the selected certificate store type requires a password for access.

Data Type

string

Default Value

""

Remarks

This property provides the password needed to open a password-protected certificate store. This property is necessary when using certificate stores that require a password for decryption, as is often recommended for PFX or JKS type stores.

If the certificate store type does not require a password, for example USER or MACHINE on Windows, this property can be left blank. Ensure that the password matches the one associated with the specified certificate store to avoid authentication errors.

SSLClientCertSubject

Specifes the subject of the TLS/SSL client certificate to locate it in the certificate store. Use a comma-separated list of distinguished name fields, such as CN=www.server.com, C=US. The wildcard * selects the first certificate in the store.

Data Type

string

Default Value

*

Remarks

This property determines which client certificate to load based on its subject. The connector searches for a certificate that exactly matches the specified subject. If no exact match is found, the connector looks for certificates containing the value of the subject. If no match is found, no certificate is selected.

The subject should follow the standard format of a comma-separated list of distinguished name fields and values. For example, CN=www.server.com, OU=Test, C=US. Common fields include the following:

SSLServerCert

Specifies the certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

If not specified, any certificate trusted by the machine is accepted.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Schema

This section provides a complete list of schema properties you can configure.

Location

Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.

Data Type

string

Default Value

%APPDATA%\Paylocity Data Provider\Schema

Remarks

The Location property is only needed if you want to either customize definitions (for example, change a column name, ignore a column, etc.) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is %APPDATA%\Paylocity Data Provider\Schema, where %APPDATA% is set to the user's configuration directory:

BrowsableSchemas

Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing all available database schemas can take extra time, thus degrading performance. Providing a list of schemas in the connection string saves time and improves performance.

Tables

Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing all available tables from some databases can take extra time, thus degrading performance. Providing a list of tables in the connection string saves time and improves performance.

If there are lots of tables available and you already know which ones you want to work with, you can use this property to restrict your viewing to only those tables. To do this, specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Views

Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing all available views from some databases can take extra time, thus degrading performance. Providing a list of views in the connection string saves time and improves performance.

If there are lots of views available and you already know which ones you want to work with, you can use this property to restrict your viewing to only those views. To do this, specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.

MaxRows

Specifies the maximum rows returned for queries without aggregation or GROUP BY.

Data Type

int

Default Value

-1

Remarks

This property sets an upper limit on the number of rows the connector returns for queries that do not include aggregation or GROUP BY clauses. This limit ensures that queries do not return excessively large result sets by default.

When a query includes a LIMIT clause, the value specified in the query takes precedence over the MaxRows setting. If MaxRows is set to "-1", no row limit is enforced unless a LIMIT clause is explicitly included in the query.

This property is useful for optimizing performance and preventing excessive resource consumption when executing queries that could otherwise return very large datasets.

Other

Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.

Data Type

string

Default Value

""

Remarks

This property allows advanced users to configure hidden properties for specialized scenarios. These settings are not required for normal use cases but can address unique requirements or provide additional functionality. Multiple properties can be defined in a semicolon-separated list.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting

PseudoColumns

Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.

Data Type

string

Default Value

""

Remarks

This property allows you to define which pseudocolumns the connector exposes as table columns.

To specify individual pseudocolumns, use the following format: "Table1=Column1;Table1=Column2;Table2=Column3"

To include all pseudocolumns for all tables use: "*=*"

Timeout

Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.

Data Type

int

Default Value

60

Remarks

This property controls the maximum time, in seconds, that the connector waits for an operation to complete before canceling it. If the timeout period expires before the operation finishes, the connector cancels the operation and throws an exception.

The timeout applies to each individual communication with the server rather than the entire query or operation. For example, a query could continue running beyond the timeout value if each paging call completes within the timeout limit.

Setting this property to 0 disables the timeout, allowing operations to run indefinitely until they succeed or fail due to other conditions such as server-side timeouts, network interruptions, or resource limits on the server. Use this property cautiously to avoid long-running operations that could degrade performance or result in unresponsive behavior.

UserDefinedViews

Specifies a filepath to a JSON configuration file defining custom views. The provider automatically detects and uses the views specified in this file.

Data Type

string

Default Value

""

Remarks

This property allows you to define and manage custom views through a JSON-formatted configuration file called UserDefinedViews.json. These views are automatically recognized by the connector and enable you to execute custom SQL queries as if they were standard database views. The JSON file defines each view as a root element with a child element called "query", which contains the SQL query for the view. For example:

{
    "MyView": {
        "query": "SELECT * FROM Employee WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }

}

You can define multiple views in a single file and specify the filepath using this property. For example: UserDefinedViews=C:\Path\To\UserDefinedViews.json. When you use this property, only the specified views are seen by the connector.

Refer to User Defined Views for more information.