Running an ETL job

You can run an ETL job by:

  • Running the job as a Windows service. This is the default method.
  • Running the job as a batch file using Windows Task Scheduler.
  • Running the job from the command line.

This section describes how to schedule an existing ETL job to run in an unattended and repeated fashion, or by running the ETL job from the command line.

Running the ETL job as a Windows Service

This is the default method and is appropriate for most installations. The ETL Administration Tool provides a built-in way to create a Windows service from the ETL job. The ETL job runs and then waits for a configurable duration before it runs again. You can define the amount of time between each run.

Advantages:

  • The ETL Administration Tool simplifies setting up the service.

  • The ETL service appears in the Windows Services console.

    This is desirable in cases where the administrator is already managing other services for related systems.

Disadvantages:

  • Very few scheduling features are available. The only configurable option in terms of scheduling is the sleep time between executions.

  • The service does not perform a true periodic execution of the job.

    Each single run of the job takes a variable amount of time depending on many factors, such as how much data it needs to process, or how much activity is taking place on the server during the job run. The sleep time is fixed. This means that for each run the start time for the job drifts. This may be undesirable in situations where you want the job to start at a specific time each day.

Running the ETL job as a service may not be optimal when you have many different ETL jobs. The service remains in memory even when the underlying job is sleeping.

Grant database permissions for the ETL job to run as a service

By default, when an ETL job is run as a service it runs under the NT AUTHORITY\SYSTEM Windows user account.

With SQL Server 2012 and later, the NT AUTHORITY user does not have database permissions. If an ETL job is run using the NT AUTHORITY user, the ETL job cannot connect to the Power Monitoring Expert database and the job is not successful.

For the ETL job to succeed, you must first grant database permissions to this user.

To grant database permissions to the NT AUTHORITY user, log in to SQL Server Management Studio as an administrator and run the following script:

USE [ION_Data]
GO
CREATE USER [NT AUTHORITY\SYSTEM] FOR LOGIN [NT AUTHORITY\SYSTEM]
GO
EXEC sp_addrolemember N'db_owner', N’NT AUTHORITY\SYSTEM'
GO
USE [ION_Network]
GO
CREATE USER [NT AUTHORITY\SYSTEM] FOR LOGIN [NT AUTHORITY\SYSTEM]
GO
EXEC sp_addrolemember N'db_owner', N'NT AUTHORITY\SYSTEM'
GO

NOTE: If security concerns limit you from using the default NT AUTHORITY user, create a dedicated Windows user to run the ETL job as a service:

  1. Create a Windows user. Note that if the ETL is installed to its default location, C:\Program Files\..., the Windows user must have Administrator access.
  2. Set the ETL job to run as a service under the new Windows user.
  3. Log in to SQL Server Management Studio as an administrator and run the previous script, substituting NT AUTHORITY\SYSTEM with the new Windows user.

Running the ETL job as a batch file using Windows Task Scheduler

Create a batch file and use Windows Task Scheduler to schedule when the ETL job runs. The batch file contains the command line entry to run the job.

Advantage:

  • The scheduled task performs a true periodic execution of the job. Windows Task Scheduler lets you schedule the job to start at precise times.

Disadvantages:

  • It is more difficult to set up than the services option because you must create and test the batch file before scheduling it. There is currently no built-in feature to create a batch file automatically for the job.
  • You must have a fully configured ETL job that runs successfully. Follow these steps if you want to run the ETL job using the Windows Task Scheduler.

To create the batch file:

  1. Use your favorite text editor and create a command line batch file (.bat) that executes the ETL job once (using the –SingleRun option).

  2. To determine what to put in your batch file:

    • Try running your ETL job from the command line. Open a command prompt, and change directories to your ETL Engine’s bin folder.

    • Optional: View the list of available ETL Engine commands by entering the following:

      ETLEngine.exe -?

    • Run your ETL job once using the following as an example, and substitute your ETL job’s name:

      ETLEngine.exe -SingleRun -job enterjobnamehere

      NOTE: Your job name is listed on the Job tab in the ETL Administration Tool.  If your job name contains spaces, enclose the job name in double quotes on the command line.

  3. After you determine the correct command line arguments to use, create a batch file containing the full command.

Schedule that batch file for repeated execution using Windows Task Scheduler. Refer to the Windows Task Scheduler documentation for details.

Running the ETL job using the command line

The syntax for running an ETL job from a command line is:

ETLEngine.exe [OPTION] -Job JobName

Where OPTION can be one of the following values:

-?, -help

Prints a help message and exits.

-SingleRun

Performs one single run of processing and exits. 

-Service

Registers a specific job as a Windows service.

-UnregService

Unregisters the service associated with a specific job.

-WaitSingleRun

Useful for debugging only.