You are using an unsupported browser. Please update your browser to the latest version on or before July 31, 2020.
close
On 6/6/2022, the Hardware.io conference revealed vulnerabilities in LP products, for more information see our ICS Advisory ICSA-22-153-01.
announcement close button
Data Exchange Package - Microsoft SQL Server
print icon

Overview

The Data Exchange Package is an optional feature of Access It! Universal.NET allowing for importing and updating of the Cardholder database. The Data Exchange Package can be ran manually, or set to run at automatic intervals.

When executed within Access It! Universal.NET, the Data Exchange Package allows for specification of user defined criteria of which records will be processed from the source SQL table. The Data Exchange package can be configured to automatically update the selection criteria after the package is executed.

Access It! Universal.NET Licensing

The following licensing option must be enabled within the Access It! Universal dongle and is a system wide license.

  • Data Exchange Packages - Yes

Creating New Data Exchange Package

A pre-created Data Exchange Package can be exported and imported into a new package. See this article for more information.

  1. Within Access It! Universal.NET navigate to Configuration | Data Exchange Packages
  2. Select New from the toolbar
  3. Assign a friendly name for this Data Exchange Package
  4. Select Import – Microsoft SQL Server from the Package Type drop down list

Data Source Tab (required)

  1. In the SQL Server field, select the supported Microsoft SQL Server the Data Exchange Package will import from
  2. In the Authentication field, enter the appropriate credentials to connect to the Microsoft SQL Server Database the Data Exchange Package will import from
  3. In the Database field, select the supported Microsoft SQL Server database the Data Exchange Package will import from
  4. In the Table/Query drop down list, select the source the Data Exchange Package will import from
    If the source records should be deleted after importing, check the box Delete rows after execution.

Field Mappings Tab (required)

  1. Select Add
  2. In the Destination drop down list, select the field in the Access It! Universal.NET database the import data will be placed into
  3. In the Source section, select the field from the Microsoft SQL Server Database that will be mapped to the previously selected destination field. If a static value is to be assigned to the Destination field, select Static Value and enter accordingly
  4. In the Selection Type field, select Full Import to process all records from the source database or select Updates/Deletes Only to only process specific records
  5. When performing only Updates/Deletes Only, select the column where the user defined comparison criteria is located
  6. When performing only Updates/Deletes Only select the appropriate Selection Criteria Type
    1. a.Selection Column Data > Last Processed
      • Will only process records that have a value in the specified column greater than the time stamp of the last time the package was executed
    2. b.Selection Column Data = (Value)
      • Will only process records that have a value that matches the user specified Value. An Update Mode is available to dynamically change the source value after the record has been processed

Advanced Mapping Options (optional)

Advance mapping allows for transforming the import data when the Data Exchange Package is executed.

Input Transform (optional)

Input Transform allows for using a certain portion of the imported field. An example of when this option would be used is when the imported field is a person’s social security number, and only the last 4 digits are required to be imported.

  1. Select Use part of value(Substring)
  2. Select which character you wish the data field to begin at
    If all of the data is needed after the starting point, select All remaining characters.
    If only a certain number of characters are needed, select Character Count and set as needed.

Output Transform (optional)

Output Transform allows for modifying the case format of the imported field. An example of when this option would be used is when source data is formatted in all caps, and should be imported using proper case formatting.

  1. Select None to leave the formatting as is
  2. Select To Upper Case to force the source data to be imported in all upper case
  3. Select To Lower Case to force the source data to be imported in all lower case
  4. Select To Proper Case to force the source data to be imported in all proper case
  5. Select Format value to specify a custom format string that will be used to format the import source

Value Mappings (optional)

Value Mappings allow for mapping a value in the source field to a custom output value which will be imported. An example of when this option would be used is when the imported data is stored as a number value and should be mapped to a friendly name during the import.
If the source mapping is an empty string or NULL, leave the input value mapping empty.

  1. Select Add
  2. In the Input Value field, select the value of the imported field that requires mapping
  3. In the Maps to Output Value, enter the desired output that will be imported when the input value criteria is matched.

Prepend / Append (optional)

Prepend / Append allows for custom data to be placed prior or after the imported source field.

  1. In the Prepend Text field, enter the data that will be placed prior to the import field
  2. In the Append Text field, enter the data that will be placed after the import field

Package Options Tab (required)

  1. In the Exception Logging Mode select whether to log import exceptions to a folder or to a File
  2. Select the path the import exceptions will log to
    If no path is specified, the default path is .\ProgramData\RS2 Technologies, LLC\Access It! Universal.
  3. Within the Key Field drop list, select a unique key field. The Key field is used to compare existing data to the imported data and then used to import a new record, or update the existing field
  4. Set Card Modification Rule accordingly
    The Card Number field must be mapped within the Field Mappings tab in order for the following rules to apply.
    1. Insert / Update Card
      • When the Key field between source and destination match, the existing card record will be updated, or created if not already existing
    2. Replace Existing Card(s)
      • When the Key field between source and destination match, the existing card record will be replaced
    3. Deactivate Existing Card(s)
      • When the Key field between source and destination match, the existing card record will deactivated
  5. Set Access Level Modification Rule accordingly
    One or more Access Levels need to be mapped within the Field Mapping tab in order for the following rules to apply.
    1. Insert / Update Access Levels
      • When the Key field between source and destination match, the existing Access levels assigned are not altered and the new Access Level is inserted to the card.
    2. Replace Access Levels
      • When the Key field between source and destination match, the existing Access levels assigned are removed from the card and then replaced with the new Access Level imported.
    3. Delete Access Levels
      • When the Key field between source and destination match, the existing Access levels assigned are removed from the card.
    4. Insert / Update Cardholder Access Levels
      • When the Key field between source and destination match, the existing Access levels assigned are not altered and the new Access Level is inserted on the Cardholder Level.
    5. Replace Cardholder Access Levels
      • When the Key field between source and destination match, the existing Access levels assigned are removed from the cardholder and then replaced with the new Access Level imported.
    6. Delete Cardholder Access Levels
      • When the Key field between source and destination match, the existing Access levels assigned are removed from the cardholder.
  6. Set the Access Level creation rule accordingly
    Option available beginning in Access It! Universal.NET V5.5
    1. Don't Create
      • If the access level in the imported source does not exist within Access It! Universal.NET it will not create a new access level.
    2. Create in specific sites
      • If the access level in the imported source does not exist within Access It! Universal.NET it will create a new access level for each site specified. To specify a site, click the Specify button.
    3. Create in all sites
      • If the access level in the imported source does not exist within Access It! Universal.NET it will create a new access level within every site.
  7. If the imported data should be created if it does not exist in the destination, check the box Insert record if it does not exist
  8. When the Delete Control Field matches the Delete Control Value select whether the Cardholder or the Card should be deleted
  9. Within the Delete Control Field, select the field from the import source that will be compared against the Delete Control Value. If the values match, the record will be deleted
  10. Within the Delete Control Value, enter a value that will be compared against the Delete Control Field. If the values match, the record will be deleted

Pre/Post-processing steps Mappings Tab (optional)

Pre/Post steps allow for the Data Exchange Package to execute a program, or batch file before or after executing

  1. In the Preprocessing Step field, enter a valid file path or click Add Step and browse for an executable file to be ran  before the Data Exchange package executes
  2. In the Postprocessing Step field, enter a valid file path or click Add Step and browse for an executable file to be ran after the Data Exchange package executes

Schedule Tab (optional)

  1. From the Schedule Type drop list, select the type of schedule determine how often the Data Exchange Package will automatically execute
  2. Assign the Start Date/Time accordingly

Executing a Data Exchange Package

  1. Within Access It! Universal.NET navigate to Configuration | Data Exchange Packages
  2. Select the Data Exchange package to be executed
  3. From the toolbar, select Execute Data Exchange Package

Mobile Credentials

The following articles outline the specifics required when importing mobile credentials for each manufacturers respectfully.


scroll to top icon