Data Integration
      Back to home
      1. Help Center
      2. Veonics® Portal Users Manual
      3. Data Integration

      How do you set up the Veonics® Credential Database (VCDB) SFTP configuration?

      The Veonics Portal has two integration processes: an SFTP protocol and our API. Here we address the SFTP setup and configuration of the VCDB.

      Table of Contents:

      1. SFTP Veonics Portal Integration Overview
        1. eXpress badging hosted SFTP Import Set Up & Diagram
        2. COMPANY QA Instance Build Review
        3. eXpress badging Hosted Import of SFTP ImportProcess Defined
        4. Customer-Hosted SFTP Option
      2. The SFTP Setup Process
        1. CUSTOMER Private/Public Key Generator Instructions
        2. CUSTOMER Test Record Creation and Submission
        3. COMPANY Crosswalk Database Build
        4. CUSTOMER Provided Test Data for SFTP Import Test
        5. CUSTOMER Provided Test Photo Batch for SFTP Import Test
          1. No Data to Photo Name Relationship
        6. COMPANY Approval of SFTP Import Automation
      3. SFTP Export of data (when needed)
      4. SFTP Export of photos (when needed)
      5. CUSTOMER Managed SFTP and Encryption

      SFTP Veonics Portal Integration Overview


      When enabling the SFTP Veonics Credential Database (SFTP/VCDB), CUSTOMER establishes an SFTP connection with the SFTP/VCDB server using CUSTOMER-provided encrypted private key pair (see below). Once the connection is established, CUSTOMER drops export CSV files using WinSCP, or another internal process, from the CUSTOMER's source database with a specific naming convention into the SFTP/VCDB folders established during setup. 

      SFTP IMPORTS: Ideally, the CUSTOMER export file contains only new and updated records. Otherwise, latency will be an issue. CUSTOMER decides file drop frequency. If CUSTOMER is capturing new photos on their side, they can provide them using a photo export process outlined below. Once the frequency is established, the SFTP/VCDB is updated, tested, and approved by the COMPANY’s testing team. Once confirmed, the test data is deleted, and the live Veonics Portal is ready for integration. 

      SFTP EXPORTS: Bi-directional integration, or export of SFTP/VCDB data, can be enabled for captured RFID numbers and other identified fields to include "Accepted" Photo status only photos that require a reverse of the import process. Syncs can be incoming and outgoing, ideally using a staggard schedule.  RFID number will use the data SFTP/VCDB export process, and the photo will use the photo SFTP/VCDB export process. Exports can be placed on the SFTP/VCDB server for access by the CUSTOMER or the CUSTOMER's server using the provided connection parameters.

      eXpress badging hosted SFTP Set Up & Diagram

      vpqm_sftp_diagram_Q32023c


      COMPANY QA Portal Instance Setup for testing

      1. COMPANY will build a QA Portal Instance to work out all issues before publishing on the live production version of the CUSTOMER's Veonics Portal instance.
      2. Once COMPANY and CUSTOMER approve testing, the QA instance(s) is cloned/mirrored by the COMPANY build team and moved to the live production Veonics Portal instance.

      eXpress badging Hosted SFTP Import Process Defined

      1. The import script is started on the SFTP/VCDB server.

      2. The script logs in to the SFTP/VCDB server

        1. Or CUSTOMER SFTP server if configured.

      3. The script checks for the data.csv file based on a defined schedule.  

        1. When the file is found, it downloads the file to a staging folder on the SFTP/VCDB server.
        2. If the file is not found, the entire import fails.

      4. The script renames the data.csv file on the associated SFTP server for temporary archival use.

      5. The renamed temporary file is decrypted at this point if necessary.

      6. The script processes the temporary file one record at a time.

      7. If photos are included, the photo for the current record is downloaded.

        1. The record is not imported if the associated photo file is not found during standard importing.
        2. If we are using a Batch Record Property import configuration, the records are imported with no photo

      8. The script updates a hidden "heartbeat" file on the associated SFTP server to indicate the time of execution.

      9. The script updates a hidden last_processed file on the SFTP server if rows were imported.

      10. If notifications are enabled, send associated Veonics Portal email notifications of script execution.

      Customer-Hosted SFTP Option

      Customers can set up their SFTP process so that eXpress badging can access folders within their network as the export file destination.  

      The customer supplies:

      1. Select an authentication method below:

        1. Assign a username to eXpress badging and receive a public key from eXpress badging.

          1. The customer assigns usernames to eXpress badging for SFTP folder access; eXpress badging supplies a public key to the customer. This is the most secure and recommended method.
          2. This process reverses key management in the above diagram. This is the most secure and recommended method.
        2. Assign a username and password to eXpress badging
          1. The customer assigns usernames and passwords to eXpress badging for SFTP folder access. The least secure of the two.
      2.  Provides their SFTP server domain name
      3. Provides their full directory path to the data file
      4. Provides the Name of the data file
      5. Provides the relative path to the photo directory

      eXpress badging supplies

      1. The setup of the sftp_import database record and the cron process to run the import script.

      The SFTP Setup Process

      Enabling an SFTP integration requires COMPANY and CUSTOMER to follow the processes defined below.  

      CUSTOMER Private/Public Key Generator

      • CUSTOMER generates 2048-bit key pair for SFTP transmission and sends the public key to COMPANY’s email: support@expressbadging.com

      Download the PuTTY Key Generator Here

      Below is a screenshot of the PuTTY Key Generator. It will produce what is required if it is set on RSA and the number of bits in a generated key is set to 2048.

      • Under "Actions," select "Generate."
        • Ignore "Load an existing private key."
      • Next, select "Save public key."
      • Then select "Save private key."
        • The private key will be used to log in to the Veonics Portal SFTP server.
      • Copy and paste into MS NOTEPAD the data in the ‘Public key for pasting into Open SSH authorized_keys file’ box. 
        • Save the file and use COMPANY Upload Center to provide the public key securely.
        • COMPANY can provide remote technical support to help if needed.

      PuTTY key gen example

      pub key notepad example

       

      COMPANY provides CUSTOMER the SFTP/VCDB file mapping location

      • CUSTOMER uses the mapped location when saving the data.csv file.
      • A separate location will be provided if photos are being imported into the SFTP/VCDB

      CUSTOMER Test Record Creation and Submission 
      • CUSTOMER  will export their source data field headers with one line of data in each field in a CSV comma-delineated format.
        • When Header Name Fields are viewed in Notepad, they are separated using one comma as the separator and end in a carriage return (CRTL).
          • Header Name Field CAN NOT have empty field names (2 commas together), but spaces are OK.
        • When Data Record rows are viewed in Notepad, data is separated using a comma and end with a carriage return (CRLF)
      • The number of Header Name Fields (columns) in the .csv dataset MUST match the number of Data Records (columns).
      • The CUSTOMER can use other separator formats during their export process
        • For example, using "quotes" (") to encapsulate data with unique characters (@, &, - ...) "Safety & Security"  
      • No live data at this point, please.
        • CUSTOMER can place their test record file on the SFTP/VCDB server once set up or can use the COMPANY Upload Center to share the file securely.  Viewed below when opened in Notepad:

        • CUSTOMER Sample SFTP Export Notepad View

          +++

          EmplNum,First,MI,Last,Preferred,Title,Department,Location,Email,CardNum(CRLF)
          1200321,John,T,McSampleton,Johnny,Director,Customer Experience,Cocoa Beach,johnnyd@myco.com,98765432101(CRLF)
          +++

      • CUSTOMER  assigns and communicates the index value field used to COMPANY  regarding the creation of new records and uniquely identifying and updating existing records
        • "EmplNum" in the case of the sample below.
        • No duplicate index values, as the last one writes over the previous record.
      • CUSTOMER  confirms:
        • The SFTP Export data set type is either:
          • ALL records
          • NEW and EDITS only
        • The SFTP Export frequency and, if applicable, the SFTP Import frequency
        • The date format printed on the ID badge: MM-DD-YYYY
        • The numeric fields needing zero left paddings, if applicable
          • Typically a number that is encoded or used to print a barcode
        • The field used to select the correct Data Properties if populating multiple Data Properties

      COMPANY Crosswalk Database Build

      • COMPANY creates a crosswalk mapping from CUSTOMER headers to Veonics Portal alias headers. Sample Crosswalk:
      • Data Cross Walk Sample2
      • CUSTOMER confirms data crosswalk
        • Confirms Unique Critical Identifier; one field must be assigned! 
        • All field headers provided will be created in a Veonics Portal matching data table for import.
        • The header matching file NEVER CHANGES unless COMPANY is notified in advance and the new header is tested and approved.
        • If populating multiple Record Properties, confirm that the related Crosswalk is correct.

      CUSTOMER Provided Test Data for SFTP Import Test

      • CUSTOMER generates a sample export of 20 to 30 records (and photos)
        • One file name will be used for all uploads.
        • ‘data.csv’ is typically used as the upload file name. Any name works but never changes.
        • CUSTOMER loads the test import "data.csv" file on the SFTP server for testing.
          • This tests the end-to-end functionality of the system.
        • The entire database export is not required at this point! 
        • After the file is successfully processed, the file name is updated with a date/time stamp for temporary archival purposes.
          • (data.YYYYMMDDhhmmss.csv)

      CUSTOMER Provided Test Photo Batch for SFTP Import Test

      If CUSTOMER badge photos are being provided with the data import, CUSTOMER must automate transferring photos to the SFTP/VCDB subdirectory folder named “photo” to enable exporting.

      • The photo files must be exported and ready at the same time the data.csv file is imported.
        • The data is imported one record at a time and retrieves the associated named photo file in the photo field.  
      • The saved photo file name must match exactly how it is saved in the related field column of the data import file. For example, if  "EmployeeNumber" is the only field used to name the photo and populated with 87654321, the file name must be 87654321.jpg. We only need the "field name" to match and link the image.  If you have a field called Photo Name, that is straightforward as long as all saved images were saved using that field.  
        • The record and photo are not imported if the two do not exactly match during the integration import process.
          • The records will be imported with no photo during a multiple Record Property batch import process.
      • When using a concatenated naming convention, first_last_emplnum.jpg, CUSTOMER must specify what fields are used
        • Example: Firstn, Lastn, and IDnum (John_Smith_987654.jpg).
        • Again, they MUST match exactly
        • The CUSTOMER is responsible for updating all file names so they can be used during our import/matching process. 
      • CUSTOMER must supply the matching named "photo file" in the correct file type (jpg or png), one or the other for all records, or the record's data import will fail for that record only.
      • CUSTOMER Sample photos import testing:
        • Only 20 to 30 sample photos (matching sample data import file) should be used for testing by uploading your files to our secure Upload Center 
      • CUSTOMER badge photos must be stored on the Veonics Portal SFTP subdirectory “folder” named “photo” to enable exporting.  This also means saving imported photos to the exact location, the "photo" folder, as captured off Portal.

      No Data to Photo Name Relationship

      If the CUSTOMER photo name has no relation to the export data, like a record ID to file name; 123456.jpg named from a disparate access control system or another ID system, CUSTOMER must create a merged import data file combining the exports of source data and the export with the ID recipient's name and associate ID number. 

      • Merging Data:
        • ID Recipient's Disparate System Export Data field header names:
          • Fname, Lname, Department, PhotoName header fields
        • Source System Export Datafield names:
          • Emplnum, Firstn, Lastn, Depart, Photo header fields
          • The more header field names are used to narrow down duplicate names, the better, as duplicate matching is not good; all matches will have the same photo image.
        • Then create a lookup and replace script that relates a single Source-record to one in the Disparate export; Firstn, Lastn, and Depart to Fname, Lname, Department
        • When a match is found, copy the Disparate "PhotoName" field data and paste it into the Source Record export column of "Photo". Run this script for the entire export. The Disparate "PhotoName" is now related to the associate "Photo" record in the Source data export. 
          • Check, recheck, then test thoroughly.
      • Resulting Source Record Data: Employee ID, First, Last, Department, Photo 
      • COMPANY recommends importing the recipient's email address, so the ones that can not be matched can be sent a Veonics CELLfie™ request email with instructions and a capture link to upload a new photo. 
      • COMPANY can provide data merging and CELLfie services as a Professional Service once engaged.

        COMPANY Approval of SFTP Import Automation

        • COMPANY sets up the import process on the back end and tests with the customer-supplied import file.
        • CUSTOMER establishes a test sync frequency (if needed), and COMPANY enables it in the Veonics Portal QA Instance. 
        • Once COMPANY and CUSTOMER approve the import process on the QA portal instance, the SFTP QA instance is cloned and published on the CUSTOMER live Veonics Portal instance.
        • After retesting on the live instance, the Record Property is reset to an empty record state and scheduled per the CUSTOMER-provided schedule.
        • CUSTOMER will be notified of Go-Live so they can enable the SFTP process on their side, pending the approval of the other import and export processes.

        SFTP Export of Data

        Badge record data can be helpful in other disparate systems. Captured RFID numbers and preferred first names are the most common.  However, if allowing data edits via our Veonics CELLfie feature, newly updated fields like mailing address (line one, line two, city, state, zip), last name changes, and even title and department changes may be initialized during this process.

        CUSTOMER Provided Connection Information:

        • Server domain name:
        • Username:
        • Password:
          • OR COMPANY can create an encryption key and supply the public key
        • Full folder path where to place the data

        CUSTOMER Reformat Requests

        • CUSTOMER requests special data reformatting at this time
        • CUSTOMER requests special date reformatting at this time

        COMPANY Processes

        • COMPANY provides CUSTOMER the SFTP/VCDB file mapping location
        • The default photo file name is the current file name used to save the Portal image—
          • Sample "<Employee ID>.<extension>”
          • Otherwise, the CUSTOMER must request the naming convention preferred, which must be an available data field in the Portal.  
        • COMPANY requests CUSTOMER  SFTP connection information if not using COMPANY SFTP server.
        • COMPANY provides CUSTOMER the SFTP/VCDBfile mapping location
        • COMPANY  creates a separate export header mapping as defined by the CUSTOMER. 
          • CUSTOMER requests any data reformatting at this time
          • Otherwise, the default Veonics Portal date format is ‘YYYY-MM-DD’, or the date/time format is ‘YYYY-MM-DD hh:mm:ss’ is provided.  
        • COMPANY sets up the export process, runs the Export Script, and tests pending approval.
        • Once COMPANY has set up the data export process on the QA instance, it is tested and approved.  Once approved by COMPANY and CUSTOMER, it is mirrored/cloned along with the other imports/export setups and published in the CUSTOMER live Veonics Portal Instance, where it is retested and approved one last time.
        • COMPANY informs CUSTOMER when ready for Go-Live

        The export process saves the last export date and exports photos/data for records modified since the last export. Any modification will trigger both photo and data export.

        SFTP Export of Photos

        COMPANY requests CUSTOMER  SFTP connection information if not using COMPANY SFTP server.

        CUSTOMER Provided Connection Information:

        • Server domain name:
        • Username:
        • Password:
          • OR we create an encryption key and supply the public key
        • Full folder path where to place the photos:
        • State export size if resizing: 
          • If photos need to be resized smaller for export, CUSTOMER must provide COMPANY with the desired height or width.
          • Enlarging images will result in poor quality.
          • For any resized photo exported, the file type is converted to JPG if it is a PNG.

        COMPANY Processes

        • COMPANY provides CUSTOMER the SFTP/VCDB file mapping location
        • The default photo file name is the current file name used to save the Portal image—
          • Sample "<Employee ID>.<extension>”
          • Otherwise, the CUSTOMER must request the naming convention preferred, which must be an available data field in the Portal.  
        • CUSTOMER selects either Accepted photos or All photos to be exported
          • It is recommended to export Accepted photos only that are assigned an "A" status.
            • CUSTOMER can request any status for export except New and Missing
            • It is not recommended to export ALL photos. 
        • The default export photo size is based on the Portal Photo Attributes (width and height) used when saved. 
            • If photos need to be resized smaller for export, CUSTOMER must provide COMPANY with the desired height or width that does not exceed the current maximum size. 
            • The aspect ratio will not change; an image saved to 1" x 1.25' standards must use the same ratio of 1:1.25 so images maintain their saved aspect ratio. 
        • Once COMPANY has set up the photo export process on the QA instance, it is tested and approved.  Once approved by COMPANY and CUSTOMER, it is mirrored/cloned along with the other imports/export setups and published in the CUSTOMER live Veonics Portal Instance, where it is retested and approved one last time.
        • COMPANY informs CUSTOMER when ready for Go-Live

        CUSTOMER Managed SFTP

        COMPANY can accommodate CUSTOMER on-premises or self-hosted SFTP to import and export records to and from the Veonics Portal.  Since every situation is unique, CUSTOMER and COMPANY will discuss connection and setup to CUSTOMER system during the setup process.  Please reference What ports need to be opened when using portal.veonics.com? when enabling communication between CUSTOMER system and the Veonics Portal.

        Encryption

        Only PGP encryption is currently supported for imported data, and photo files are decrypted. 

        BACK TO TOP