Populate a Database with Drop File Data – SENTINEL Instructions

Target Audience: Advanced User, IT Manager
Article #: 12629

Summary

This article explains how to populate or a database using drop file data. This drop file can be either text or an XML file. These instructions apply to SENTINEL which serves as the centralized print manager for TEKLYNX CENTRAL.

Preparation

Before you begin, you will need to have the following:

  • Access to an SQL database with administrative credentials
  • Microsoft SQL Management Studio either on the server or on another device that can access the database
  • SENTINEL or TEKLYNX CENTRAL installed and functioning
  • A copy – not the original – of a sample drop file

Instructions

Create the Destination Database Table

  1. Create the destination database table by looking at the sample drop file and separating the variables into:
    a. One-time “header” variables such as order number, operator name, etc.
    b. Repeating variables which will go into the SQL table
Organizing data for the database.
  1. Take note of the field names for your table (1b, above). Log into your SQL database and create the new table.
    HINT: Include “TEST” in the name to make it easier to locate.
SQL statement to create a new table

Create the Database Stored Procedures

  1. Log into SQL Management Studio (for MSSQL)
  2. Open the node for the database in the Object Explorer window, which is typically located on the left-hand side.
  3. Expand the Programmability node to see the Stored Procedures.
  4. Right-click on Stored Procedures and select Stored Procedures…
Creating a Stored Procedure
  1. Use a template from SQL Management Studio to create the stored procedure.

Define Parameters

Defining parameters within the stored procedure.
  1. Enter the SQL statements.
SQL statement in the Stored Procedure.
  1. Press Execute when done.

Create SENTINEL Map

  1. Create a SENTINEL Map. Two examples are shown below.
Example of a SENTINEL map using “Assignment” to set the “>” to “=”.
Example of a SENTINEL map using “File Conversion” to make mass changes.

Create a Task Definition Using Query Manager

  1. Select Query Manager.
  2. Add a datasource such as MS SQL Server.
Adding a datasource to Query Manager.
  1. Establish a Database Connection. Use the Test Connection button to confirm.
Database connection settings.
  1. Define Aliases on the Stored Procedure.
Define aliases on the Stored Procedure.
  1. Add Tasks, such as FillOrder and Stored Procedures.
Task definition of Stored Procedure.

Set Up SENTINEL Watch

  1. Select Create a new sentinel.
  2. In the General tab, define a Name and Map for the SENTINEL Watch.
General tab in SENTINEL properties.
  1. In the Input tab, define the plug-in, such as File Capture, and select Settings to define the file path, naming convention, watch method and digital signature.
File Capture plug-in in SENTINEL properties Input tab.
  1. In the Process tab, select Database and then select the task.
Selecting the Database process plug-in in SENTINEL properties’ Process tab.
Selecting a task in SENTINEL properties’ Process tab.

Test Ability to Use Drop File Data

  1. Start the new Sentinel
  2. Drop in the sample file
  3. Check the database.
SQL query showing that the data was successfully imported

Additional Comments

Tested using TEKLYNX CENTRAL v6

If you need further assistance, please contact us.

Author


Was this article helpful?

Related Articles