How to Automate a Service Manager Integration

Last modified on 2023/06/05 16:18

WarningSaveDatabase

   Caution: You should always perform a backup of your databases prior to running an integration in Service Manager. The EasyVista team will not resolve any issues related to the update/deletion of data and its impact on your platform.

EndWarningSaveDatabase

Data integration is performed using an integration model. It can be run after a preimport processing that will first prepare the data import, e.g. tables purged, data normalized

Automating a data integration consists of scheduling the sequencing of the preimport processing followed by the integration model. You can use different tools to automate the processing.

Conditions for accessing the tools for automating integration

  • SaaS-based customers:
    • You cannot access the application server. You cannot run the smoBackOfficeClient and smoIntegration tools. You cannot run any batches to run these tools.
    • You can access the \preimport folder via ftps/ftp. This enables you to store data files and access configuration files, SQL scripts, log files (\log subfolder) and temp files for data extraction (\temp subfolder).
    • You can create new subfolders in the \preimport folder based on your requirements.
  • On-premises customers:
    • You can access the smoBackOfficeClient and smoIntegration tools on your application server.
    • You can access the \preimport folder freely and organize it based on your requirements.

Notes

  • You can use an external scheduler such as the Windows Task Scheduler or a third-party application to schedule an integration. This enables you to:
    • Leverage more advanced scheduling options than those available for the preimport and integration model performed respectively by the Service Manager smoBackOffice and smoScheduler schedulers.
    • Centralize all scheduled tasks within a single tool.
    • Automatically sequence the running of an integration model at the end of the preimport processing.
  • The smoBackOfficeClient and smoIntegration tools are available in the <easyvista>\tools folder (Note: <easyvista> identifies the EasyVista folder on the application server. You can also download them from the documentation Web page for each tool.

Caution

  • You should always perform a backup of the Service Manager database prior to running an integration model. This ensures that you can restore the database if there are too many problems or errors.
  • Integration models are unable to perform data integration on an incremental basis. All of the data in the CSV file will be imported into the Service Manager database, regardless of whether or not the data was modified after the last integration was performed.

Best Practice

  • You should first run integration models in the test database before running them in the production database.
  • If there are too many errors at the end of the integration processing, restore the Service Manager database using the backup. Correct the source data and run the integration again.

Procedures

How to automate an integration using the Service Manager schedulers

Step 1: Test the preimport using the smoBackOffice scheduler.

1. Specify the INI configuration files to be run by the smoBackOffice scheduler.

  • Go to the folder called <easyvista>\tools\smobackoffice\QUERIES\SQL\PREIMPORT.
  • Open the PREIMPORT_SQL.sql file.
  • Go to the [PREIMPORT] section.
  • Specify the list of INI configuration files containing the SQL scripts to be run.
    Replace <PreimportFileName.ini> with the names of the INI configuration files to be run by the preimport processing.

Caution: Specify the files in the order in which the smoBackOffice scheduler should run them.

[PREIMPORT]
<PreimportFileName1.ini>=
<PreimportFileName2.ini>=
<PreimportFileName3.ini>=

2. Force the execution of the INI configuration files containing the SQL scripts to be run by the preimport processing. Open url.png See the description.

  • Go to the folder called <easyvista>\tools\smoBackOffice\Preimport\.
    Replace <easyvista> with the easyvista folder on your application server.
  • Open each INI configuration file.
  • Go to the [DEFINITION] section and delete the two keys, NLASTRUN and SLASTRUN that store the last execution date of the file.
    The keys will automatically be regenerated when the file is next run. Deleting them enables you to force the execution.
[DEFINITION]
;    Last execution date in fraction of hours and AAAAMMDDHHMMSS format.
;    It will automatically be updated when the file is next run.
NLASTRUN=
SLASTRUN=

 The preimport will be placed in the smoBackOffice scheduler queue. It will run after the tasks currently being processed.

3. Once the preimport processing is completed, check the contents of the tables loaded by the processing and correct the errors, if any.

Best Practice icon.png  View the log file generated in the C:\<easyvista>\tools\smoBackOffice\Preimport\log folder.

Step 2: Test the integration model using the smoScheduler scheduler.

1. Run the integration model using the smoScheduler scheduler.

  • Select Administration > Integration > Integration Models in the menu.
  • Click Edit icon.png next to the template.
  • Proceed to the last step of the integration model.
  • Click Execute.
     The integration model will be placed in the scheduler queue. It will run after the tasks currently being processed.

2. Once the integration processing is completed, check the data integrated in Service Manager and correct the errors, if any.

Best Practice icon.png  

  • View the integration log.
  •  If there are too many problems or errors at the end of the integration processing, restore the database using the backup. Correct the errors and run the integration again.

Step 3: Schedule the integration using the Service Manager schedulers.

1. Schedule the running of the preimport SQL scripts.

  • Open each INI configuration file.
  • Go to the [DEFINITION] section.
    • Use the ACTIVE key to indicate that the file must be run.
    • Use the FREQUENCY key to define the frequency (in number of days) for running the file.
    • Use the BEGINTIME key to specify the start time (in HH:NN:SS format) for running the file.
    • Delete the two keys, NLASTRUN and SLASTRUN that store the last execution date of the file. This will force the execution of the file.
[DEFINITION]
;    Indicate that the file must be run: ACTIVE parameter (Yes=1)
ACTIVE=1
;    Frequency in number of days for running the file
FREQUENCY=1
;    Start time for running the file
BEGINTIME=06:00:00
;    Last execution date in fraction of hours and AAAAMMDDHHMMSS format.
;    It will automatically be updated when the file is next run.
NLASTRUN=
SLASTRUN=

2. Schedule the execution of the integration model.

  • Select Administration > Integration > Integration Models in the menu.
  • Click Calendar icon.png next to the template.
  • Select the Enabled box in the window for scheduling the task.
  • Specify the scheduling information required for running the integration model. Open url.png See the description.

Caution: To ensure that all preliminary processing is completed before starting the data import in Service Manager tables, you should allow for an adequate interval between the running of the preimport processing and the integration model. The time required for the preimport processing depends on the volume of data.

How to automate an integration using an external scheduler

Step 1: Test the preimport using the smoBackOfficeClient tool.

1. Specify that the INI configuration files should be run only by the smoBackOfficeClient tool. Disable their execution by the Service Manager smoBackOffice scheduler.

  • Go to the folder called <easyvista>\tools\smobackoffice\QUERIES\SQL\PREIMPORT.
  • Open the PREIMPORT_SQL.sql file.
  • Comment out the lines for executing the INI files. 
[PREIMPORT]
;<PreimportFileName1.ini>=
;<PreimportFileName2.ini>=
;<PreimportFileName3.ini>=

2. Force the execution of the INI configuration files containing the SQL scripts to be run by the preimport processing. Open url.png See the description of INI files.

  • Go to the folder called <easyvista>\tools\smobackoffice\QUERIES\SQL\PREIMPORT.
    Replace <easyvista> with the easyvista folder on your application server.
  • Open each INI configuration file.
  • Go to the [DEFINITION] section and delete the two keys, NLASTRUN and SLASTRUN that store the last execution date of the file.
    The keys will automatically be regenerated when the file is next run. Deleting them enables you to force the execution.
[DEFINITION]
;    Last execution date in fraction of hours and AAAAMMDDHHMMSS format.
;    It will automatically be updated when the file is next run.
NLASTRUN=
SLASTRUN=

3. Run the preimport processing using the smoBackOfficeClient tool.

  • Create a batch file.

Best Practice icon.png  Adapt the smoBackOfficeClient.bat file shipped with the tool.

  • Insert a command line for each INI configuration file to be run using the smoBackOfficeClient tool.
    Replace <PreimportFileName.ini> with the name of the INI configuration file run by the preimport processing.
@ECHO OFF
c:
cd\
cd C:\easyvista\tools\smoBackOfficeClient
smoBackOfficeClient.exe "C:\EasyVista\Tools\smoBackOffice\QUERIES\SQL\PREIMPORT\<PreimportFileName1.ini>"
smoBackOfficeClient.exe "C:\EasyVista\Tools\smoBackOffice\QUERIES\SQL\PREIMPORT\<PreimportFileName2.ini>"
smoBackOfficeClient.exe "C:\EasyVista\Tools\smoBackOffice\QUERIES\SQL\PREIMPORT\<PreimportFileName3.ini>"
  • Run the batch file using your external scheduler.

4. Once the preimport processing is completed, check the contents of the tables loaded by the processing and correct the errors, if any.
 

Step 2: Test the integration model using the smoIntegration tool.

1. Specify that the integration model should be run only by the smoIntegration tool. Disable its execution by the smoScheduler scheduler.

  • Select Administration > Integration > Integration Models in the menu.
  • Click Calendar icon.png next to the template.
  • Unselect the Enabled box in the window for scheduling the task

2. Run the integration model using the smoIntegration tool.

  • Create a batch file.

Best Practice icon.png  Adapt the smoIntegration.bat file shipped with the tool.

  • Insert a command line to run the integration model using the smoIntegration tool.
    Replace <IntegrationModel_EnglishName> with the English name of the integration model.

Caution: Remember to add the double quote marks.

@ECHO OFF
cd\
cd "C:\smoIntegration"
@echo Integration is running... Please wait...
smoIntegration.exe -port=25238 -ip=127.0.01 -account=40000 -modelname="<IntegrationModel_EnglishName>"
PAUSE
  • Run the batch file using your external scheduler.

3. Once the integration processing is completed, check the data integrated in Service Manager and correct the errors, if any.

Best Practice icon.png  If there are too many problems or errors at the end of the integration processing, restore the database using the backup. Correct the errors and run the integration again.

Step 3: Schedule the integration model using the external scheduler.

1. Create a batch file.

2. Insert command lines to run the INI configuration files using the smoBackOfficeClient tool.

3. Insert a command line to run the integration model using the smoIntegration tool.

@ECHO OFF
c:
cd\
cd C:\easyvista\tools\smoBackOfficeClient
smoBackOfficeClient.exe "C:\EasyVista\Tools\smoBackOffice\QUERIES\SQL\PREIMPORT\<PreimportFileName1.ini>"
smoBackOfficeClient.exe "C:\EasyVista\Tools\smoBackOffice\QUERIES\SQL\PREIMPORT\<PreimportFileName2.ini>"
smoBackOfficeClient.exe "C:\EasyVista\Tools\smoBackOffice\QUERIES\SQL\PREIMPORT\<PreimportFileName3.ini>"

@ECHO OFF
cd\
cd "C:\smoIntegration"
@echo Integration is running... Please wait...
smoIntegration.exe -port=25238 -ip=127.0.01 -account=40000 -modelname="<IntegrationModel_EnglishName>"
PAUSE

4. Define the parameters for scheduling the batch file using your external scheduler.

Note: You do not need to allow for an adequate interval between the running of the preimport processing and the integration model using the smoBackOfficeClient tool. The smoIntegration tool will only run once the smoBackOfficeClient tool has stopped.

Tags:
Powered by XWiki © EasyVista 2022