Run the ETL Process

Use this page to learn more about how to manually setup and start an Agent Job in SQL Server Management Studio (SSMS) that runs the ETL process in IFS Analysis Models
 

Contents

 

How To Run the ETL Process

Set up a SQL Server Agent job to start the Load SSIS IFSDW.cmd file on a schedule.

Do the following:

  1. Make sure that the SQL Server Agent is started.


  2. Create a new job

  3. Create a step and use the Load SSIS IFSDW.cmd file in the command window. Add the path to the file in the command section.

  4. Verify that the command file is correct according to your settings. For example verify the:
    1. Base folder path
    2. DTSPath
    3. Add a change drive instruction to the cmd file if your IFS Analysis Models instance  folder is located on a drive other than the Microsoft SQL Server installation.

  5. Start the job

  6. Debugging

    If the step Execute job <Job Name> stops rather quickly after start then there is something wrong since the execution of the SSIS packages takes a while. Two possible reasons are listed below:

    1. Status = Error
      1. The job can probably not find the cmd file defined in the job step.
      2. Edit the job and check the load step and the path to the cmd file as well as the name of the file and make sure that it is correct.
    2. Status= Success
      1. The cmd file was found but there is something else wrong in the cmd that prevents the job to continue with the execution.
        1. Check the cmd file. If the root directory, e.g. the IFS Analysis Models instance home, is placed on another drive than C:, then make sure that the scripts also defines that that drive is the main drive. See the example above.


    During execution the job execution window looks like this:

     

If IFS Race data is used; if the job ends with an Error, after 15-30 minutes, then there were problems with one or more of the SSIS packages. To get an idea of what is wrong, try one of the following:

  1. Lookup the log files, available in the folder <AM Instance Home>\SSIS\SSIS\Log. The file Load SSIS IFSDW.log provides the most useful information.
  2. In Management Studio, examine the sysssislog system table for the database.

    When the execution is complete the job execution dialog looks like this:


Even if the agent job stop with Success it does not mean that everything is ok! Always perform the following:

  1. Check the log file Load SSIS IFSDW.log as mentioned above and make sure that there are no errors listed.
  2. Open one of the cubes that you know should contain data and perform some simple browsing. This can be done in SSMS (Management Studio) by connecting to Analysis Services, browsing for the cube database and then browse for one of the Analysis Models cubes, e.g.