Automatically Updating MS SQL Database with Imprivata EAM Data

To keep the MS SQL database up to date with Imprivata EAM managed system, you need to run the data extraction process periodically. Automatically updating an MS SQL database with Imprivata EAM data using an SSIS package for secure API-based data extraction and loading into SQL Server from Imprivata's OneSign system helps you with the following:

  • Creates the required database and their tables.

  • Automates AuthTicket based API authentication.

  • Uses the AuthTicket to securely extract data via a second API.

  • Loads retrieved data into SQL Server.

  • Supports scheduled and repeatable execution through SQL Server Agent.

Prerequisites

Before proceeding with this process, ensure that the following components are available on your system:

  1. SQL Server Integration Services (SSIS)

    SSIS must be installed and enabled on your SQL Server instance. This is necessary for creating and executing SSIS packages.

  2. Supported SQL Server Versions

    This process is compatible with the following SQL Server versions:

    • SQL Server 2017 (v14)

    • SQL Server 2019 (v15)

    • SQL Server 2022 (v16)

  3. SQL Server Management Studio (SSMS)

    SSMS should be installed on the machine where you will be managing the SSIS packages and SQL Server objects.

  4. SQL Server Agent

    The SQL Server Agent service must be enabled on your SQL Server instance. This is required for scheduling and automating the execution of SSIS packages.

  5. Required Access Permissions

    Ensure that you have the following access permissions:

    • Deploy packages to SSISDB (if using SSIS Catalog)

      You must have permission to deploy SSIS packages to the SSISDB catalog if you plan to use the SSIS Catalog deployment method.

    • View and edit SSIS package parameters

      You should have permission to view and modify SSIS package parameters to configure the necessary settings for the data extraction process.

    • Create and manage SQL Server Agent jobs

      You must have permission to create and manage SQL Server Agent jobs, which will be used to schedule and execute the SSIS packages.

    • File access permission for .dtsx package (if using File System method)

      To deploy the SSIS package using the File System method, ensure that you have the necessary file access permissions to read and execute the .dtsx package file.

    • Read/write access to target SQL Server database

      You must have read and write permissions on the target SQL Server database where the extracted data will be loaded.

Downloading MS SQL Configuration File

Refer to the following required versions to download the MS SQL Configuration file:

To Update the MS SQL Database Automatically with Imprivata EAM Data

Follow these steps to update the MS SQL database automatically with Imprivata EAM Data:

  1. Creating a Database for Data Extraction

  2. Running a Scheduled Job for Data Extraction

Creating a Database for Data Extraction

  1. Open the SQL script

    Open the create_imprivate_onsign_database.sql script file. To download the SQL script, refer to Imprivata EAM - Database script.

  2. Update login credentials

    Locate the line:

    CREATE LOGIN <Customer> WITH PASSWORD = '<2Strong2Remember!>';

    1. Replace <Customer> with the desired username.

    2. Replace <2Strong2Remember!> with your chosen password.

  3. Replace all placeholders

    Replace every occurrence of login credentials throughout the script with the details you specified in step 2.a.

  4. Execute the script

    Run the modified SQL script on your Microsoft SQL Server instance that you are going to deploy to SSISDB.

Running a Scheduled Job for Data Extraction

You can use either of the methods to run a scheduled job for data extraction from MS SQL database.

Follow these steps to run the package using SSISDB:

  1. Import the package into SSISDB.

    1. Open SQL Server Management Studio (SSMS).

    2. Connect to your SQL Server instance.

    3. Go to Integration Services Catalogs > SSISDB > [YourFolderName]

    4. Right-click the folder > Import Packages.

    5. Provide the following details:

      • Package location: Enter the File System where SSIS packages are stored as .dtsx file.

      • Package path: Browse to the YourPackage.dtsx file.

      • Package name: Enter a descriptive name for the package.

    6. Select OK to complete import.

  2. Configure runtime variables in SSISDB.

    1. In SSMS, go to the deployed package under SSISDB.

    2. Right-click the package > Configure.

    3. Under the Parameters tab, provide values for:

      Parameters

      Definition

      Type of the Parameters

      AuthTicketUrl

      The URL to get Auth token

      String

      DataExtractionUrl

      The URL to extract the data

      String

      DatabaseUser

      The SQL Server User

      String

      DatabasePassword

      The SQL Server login password

      String

      DatabaseServer

      The SQL Server Instance name or IP

      String

      DatabaseName

      Target SQL database name

      String

      Username

      Imprivata User

      String

      Password

      Imprivata password

      String

      Domain

      The domain for authentication

      String

      ProductId

      The ID of the product

      String

    4. Select Save and Close.

  3. Schedule the package with SQL Server Agent.

    1. Go to SQL Server Agent > Jobs.

    2. Right-click Jobs > New Job.

    3. On the General tab, provide the following details:

      Name: [Provide Name for the Event]

  4. Go to Steps > New, provide the following details:

    • Step Name: Name of the step

    • Type: SQL Server Integration Services Package

    • Package source: SSIS Catalog.

    • Server: Your SQL instance.

    • Package: Select your deployed SSISDB package.

  5. Under Schedules, define frequency (for example, Daily at 2:00:00)

  6. Select Save, and then Enable the job.

This method allows you to schedule .dtsx file without SSISDB or Visual Studio.

Note

  • You do not need to deploy the .dtsx package into SSISDB or import it into a Visual Studio project to schedule it.

  • You can directly schedule and execute the .dtsx file from the File System by referencing its path by following the steps in a SQL Server Agent Job Setup (File System).

To run the package directly from the File System, ensure the following:

  • The SQL Server Agent service account has permission to access the .dtsx file path.

  • All necessary variables and configuration values (for example, API URLs and connection strings) are defined within the package or passed via command-line arguments (if needed).

  • The .dtsx file is stored on a local or network path accessible to SQL Server.

SQL Server Agent Job Setup (File System)

Follow these steps to schedule and execute the .dtsx file from the File System using SQL Server Agent job setup:

  1. In SSMS, go to SQL Server Agent > Jobs > New Job.

  2. Under the Steps tab > New:

    • Type: Enter the SQL Server Integration Services Package

    • Package Source: Enter the File System

    • Package: Browse or type full path to .dtsx file.

    • Configure logging and error handling, if needed

  3. Under Schedules, define your execution schedule.

  4. Select Save to save the job.

Verify Execution of SQL Server Agent Job

Follow these steps to manually test SQL Server Agent job execution:

  1. In SQL Server Agent > Jobs, right-click your job.

  2. Select Start Job at Step.

  3. Monitor progress in real time.

  4. Review history logs after execution for errors or success confirmation.