The ARMImporter Tool


THE INFORMATION IN THIS ARTICLE APPLIES TO:

  • EFT Server, version 4 and 5 (does NOT apply to versions 6 or later!)
  • Secure FTP Server, version 3

DISCUSSION

The ARMImporter tool is a command-line utility that groups a batch of SQL files, parses the grouped files, and imports these files into a SQL or Oracle server. Download the ARMImporter Tool here.

While the ARMImporter parses the SQL files, it generates .arm files. These .arm files are saved in a location (drive) specified when you run the ARMImporter. The .arm files can then be imported to the SQL server or Oracle server. Files are grouped in an ascending order based on the timestamp.

Grouping the Files

The user selects the .sql files in the ascending order of their time stamp. For example, if there are files with time stamps of 2007 and 2008, then files with 2007 are grouped first, and then 2008.

Files are grouped as Group1, Group2, and so on. The ARMImporter creates the groups based on the presence of @Identity_1 (temporary Id), which is the key to grouping the .sql files. If two files have the same ID, they are not placed in the same group. This temporary ID is replaced with a specific number at runtime.

Parsing the Grouped Files

All events that occur during the parsing of the grouped files are logged to a file.

The following command at the command prompt parses the files and places those parsed files in a specified location:

ArmImporter –i <path where SQL files are stored> -o <path where arm files will be generated> -p

For example:

ARMImporter -i "H:\SQLFile_Parse\ARMImporter" –o "H:\SQLFile_Parse\ARMImporter –p

Importing the Parsed Files

The parsed files are imported into the SQL or ORACLE server. The file import generates log files that contain any error or warning information that you can use for troubleshooting if the import fails.

SQL server:

ARMImporter_SQL –U ad –P a –d <database name> –S <server-name\instance> -o <path for arm files> -I

For example:

ARMImporter_SQL –U admin –P a –d EFTDB –S 192.168.38.207\GlobalScape –o "H:\SQLFile_Parse\ARMImporter" –I

ORACLE server:

ARMImporter_ORACLE –U admin –P admin –S <server-name\instance> -o <path for arm files> -I

For example:

ARMImporter_ORACLE -U system -P admin -S localhost:1521/XE -i "D:\sql" -o "D:\arm" -I -l

Parsing and Importing files in a Single Statement

Instead of performing the parsing and importing operations individually, you can execute both operations at once using the following commands:

SQL server:

ARMImporter_SQL –U admin –P a –d EFTDB –S 192.168.38.207\GlobalScape –i "H:\SQLFile_Parse\ARMImporter" –o "H:\SQLFile_Parse\ARMImporter" –p –I

Oracle server:

ARMImporter_ORACLE -U system -P admin -S localhost:1521/XE -i "D:\sql" -o "D:\arm" -p -I -l

Advantage of Using this Tool

The main advantage of this tool is that it groups the dispersed .sql files belonging to the same group into one single SQL file. The files are grouped based on the identity keys, which allow us to place a SQL query execution statement after an arbitrary number of lines. This also ensures that the oSQL tool or SQL*Plus tool is not overloaded with a huge amount of simultaneous SQL statements.

Using the ARMImporter Tool in Event Rules

To use the ARMImporter tool in Event Rules, follow the procedures below to create a batch file, create a custom Command that references the batch file, then create an Event Rule to execute the Command at a specified date and time.

(Note: The exact procedure below might be different depending on the version of EFT Server/Secure FTP Server that you are using. Use these steps as a guide, but refer to the application-specific help, if necessary.)

Create a Batch File

Create a DOS batch file that contains the commands that you would normally type at a command line. You can then run the command using a custom Command. You can run and verify this batch file from the command line before creating the custom Command to call this batch file. Use the commands below depending on which Server you are using.

SQL Server batch file example:

ARMImporter_SQL –U admin –P a –d EFTDB–S 192.168.38.207\GlobalScape –I "H:\SQLFile_Parse\ARMImporter" –o "H:\SQLFile_Parse\ARMImporter" –p –I

Oracle batch file example:

ARMImporter_ORACLE_-U system -P admin -S localhost:1521/XE -i "D:\Files" -o "D:\Files" -p -I –l

Create a Custom Command

Create a custom Command to execute the batch file automatically.

  1. In the Administrator, connect to the Server and click the Server tab.

  2. In the left pane, expand the Site node for the Site on which you want to create the Command, then click Commands.

  3. In the right pane, click New. The Command tabs appear.

  4. In the Command name box, provide a descriptive name for the Command. This name will appear under the Commands node of the tree and in Event Rules when you add the Action Execute command in folder.

  5. In the Description box, provide a brief explanation of the rule. (Optional)

  6. In the Executable box, provide the location of the batch file that you created.

  7. Click the Advanced tab.

  8. In the Parameters box, provide any switches that the Server must pass in when the command is executed.

  9. The following switches can be used with the ARMImporter utility:

    Switch

    Description

    -U

    Username for SQL login

    -P

    Password for SQL Login

    -d

    SQL Server Database name

    -S

    SQL or ORACLE server name/IP

    -i

    Source path for the *.sql files.

    -o

    Path where *.arm files will be generated.

    -p

    Parses .sql files.

    -I

    Imports the .arm files into the SQL or ORACLE server. This switch is case sensitive.

    -l (el)

    Enables the generation of log files during the file parsing phase.

    -h

    Provides more information about the tool.

  10. Click the Permissions tab.

  11. Specify in the Permit execution box the users and Groups that are allowed to execute the Command.

  12. Click Apply to save the changes on the Server. The Command appears under the Commands node.

Create an Event Rule

Create a Scheduler (Timer) Event Rule to schedule when the Command will execute the batch file.

  1. Refer to Scheduler (Timer) Event in the help for your version of EFT, if necessary.

  2. In the Actions list, double-click Execute Command in folder.

  3. In the Rule window, click the blue "select" link. The Custom Command dialog box appears.

  4. In the Specify command working folder box, type or browse to the path to the ARMimporter.bat, then click OK.

  5. In the right pane, top-left corner, ensure the Enable this rule check box is selected.

  6. The Rule is defined by default to trigger each hour starting on the date you create the Rule. To change the start date, start time, recurrence pattern, and/or interval, in the Rule pane, click repeat each 01:00:00. The Timer Event dialog box appears.

  7. In the Recurrence pattern area, specify that the Rule is to run no more often than Daily. Weekly should be sufficient.

  8. Specify the Start time and Start date, then click OK. The frequency is updated in the Rule pane.

  9. Click Apply to save the Event Rule on the Server. The Event Rule is now ready to execute the command on a schedule that you specify. You can click Run Now to run the Rule immediately or to test the Rule.

  10. You could also add an email notification to let you know when the Rule has completed.