Access Paylocity Data in Anypoint Using SQL

Ready to get started?

Download for a free trial:

Download Now

Learn more:

Paylocity MuleSoft Connector



Create a simple Mule Application that uses HTTP and SQL with the CData Mule Connector for Paylocity to create a JSON endpoint for Paylocity data.

The CData Mule Connector for Paylocity connects Paylocity data to Mule applications enabling read , write, update, and delete functionality with familiar SQL queries. The Connector allows users to easily create Mule Applications to backup, transform, report, and analyze Paylocity data.

This article demonstrates how to use the CData Mule Connector for Paylocity inside of a Mule project to create a Web interface for Paylocity data. The application created allows you to request Paylocity data using an HTTP request and have the results returned as JSON. The exact same procedure outlined below can be used with any CData Mule Connector to create a Web interface for the 200+ available data sources.

  1. Create a new Mule Project in Anypoint Studio.
  2. Add an HTTP Connector to the Message Flow.
  3. Configure the address for the HTTP Connector.
  4. Add a CData Paylocity Connector to the same flow, after the HTTP Connector.
  5. Create a new Connection (or edit an existing one) and configure the properties to connect to Paylocity (see below). Once the connection is configured, click Test Connection to ensure the connectivity to Paylocity.

    Set the following to establish a connection to Paylocity:

    • RSAPublicKey: Set this to the RSA Key associated with your Paylocity, if the RSA Encryption is enabled in the Paylocity account.

      This property is required for executing Insert and Update statements, and it is not required if the feature is disabled.

    • UseSandbox: Set to true if you are using sandbox account.
    • CustomFieldsCategory: Set this to the Customfields category. This is required when IncludeCustomFields is set to true. The default value for this property is PayrollAndHR.
    • Key: The AES symmetric key(base 64 encoded) encrypted with the Paylocity Public Key. It is the key used to encrypt the content.

      Paylocity will decrypt the AES key using RSA decryption.
      It is an optional property if the IV value not provided, The driver will generate a key internally.

    • IV: The AES IV (base 64 encoded) used when encrypting the content. It is an optional property if the Key value not provided, The driver will generate an IV internally.

    Connect Using OAuth Authentication

    You must use OAuth to authenticate with Paylocity. OAuth requires the authenticating user to interact with Paylocity using the browser. For more information, refer to the OAuth section in the Help documentation.

    The Pay Entry API

    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. The Pay Entry API allows you 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, we have elected not to give it a separate schema, but it may be enabled via the UsePayEntryAPI connection property.

    Please be aware that when setting UsePayEntryAPI to true, you may only use the CreatePayEntryImportBatch & MergePayEntryImportBatchgtable stored procedures, the InputTimeEntry table, and the OAuth stored procedures. Attempts to use other features of the product will result in an error. You must also store your OAuthAccessToken separately, which often means setting a different OAuthSettingsLocation when using this connection property.

  6. Configure the CData Paylocity Connector.
    1. Set the Operation to 'Select with Streaming'.
    2. Set the Query type to Dynamic.
    3. Set the SQL query to SELECT * FROM #[message.inboundProperties.'http.query.params'.get('table')] to parse the URL parameter table and use it as the target of the SELECT query. You can customize the query further by referencing other potential URL parameters.
  7. Add a Transform Message Component to the flow.
    1. Map the Payload from the input to the Map in the output.
    2. Set the Output script to the following to convert the payload to JSON:
      %dw 1.0
      %output application/json
      ---
      payload
              
  8. To view your Paylocity data, navigate to the address you configured for the HTTP Connector (localhost:8081 by default) and pass a table name as the table URL parameter: http://localhost:8081?table=Employee
    The Employee data is available as JSON in your Web browser and any other tools capable of consuming JSON endpoints.

At this point, you have a simple Web interface for working with Paylocity data (as JSON data) in custom apps and a wide variety of BI, reporting, and ETL tools. Download a free, 30 day trial of the Mule Connector for Paylocity and see the CData difference in your Mule Applications today.