Job Import via SQL

 

The Job Import via SQL will run a predefined script on an external database and add/edit jobs in DMS.  Select Interface | Import via SQL | Job Import via SQL to execute the import. 

 

When importing data to DMS, retain and merge is used for making changes to existing jobs.  Retain and merge specifies which fields may be changed during an import and which fields cannot be changed. 

 

The Job Import via SQL has the ability to add to and change operations that are being imported by comparing the imported operation to the Import Interface Table.  If the “Apply import table” is selected and a new operation is being imported, DMS will look to see if the imported operation has any matching import interface operations.  Note that only new operations that are being added to DMS will have the Import Interface Table applied; existing operations in DMS will not be changed by the Import Interface Table settings.

 

The Job Import via SQL windows will appear as in the example below:

 

 

To import jobs via SQL, perform the following:

1.Job SQL Script Name  Select the SQL script name from the drop-down list.  The SQL script must already exist.

2.Default job state  The default job state will be applied to any job that does not specify the job state. 

3.Add resources if required  Check this prompt if you want to add resources that do not already exist in DMS.  Do not check this prompt if you want DMS to generate an error when adding operations with resources that do not exist in DMS.  If checked, DMS will add any new resources found when importing operations.  The new resources will be set up using a resource profile with the default type of work with 8 hours per shift and 8 hours per operation per shift.  The import log will record new resources added to DMS so that you can adjust these resources as required after the import.

4.Add Pools if required  Check this prompt if you want to add pools that do not already exist in DMS.  Do not check this prompt if you want DMS to generate an error when adding operations with pools that do not exist in DMS.  If checked, DMS will add any new pools found when importing operations.  The new pools will not have any resources defined to them; the user must add the resources to the pools.  The import log will record new pools added to DMS so that you can add the resources to the pools as required after the import.

5.Check for protected jobs  If you are using Job Security Protection to prevent certain jobs for edits and updates, you can chose to ignore the job security protection during the import by not selecting this option.  If you select this option then the area of a job that is marked as protected will not be updated.

6.Set AND actuals to Prime  If selected, then any AND operation's actual time, quantities, and operation status will be set to the prime operation's actual time, quantities, and operation status.  In most cases, you want to have this option selected.

7.Refresh Material Requirements  If selected, the import deletes all existing material requirement items for a given job before adding the job's material requirements.  If not selected, material requirements are not deleted before adding new material.

8.Limit error count (0 for no limit)  This option enables you to limit the number of error and warning messages to log for each type of SQL import.  If 0 is entered, all error and warning messages are written to the log.  Any other number entered will limit the error and warning messages.  For example, if 100 is entered, then no more than 100 error and warning messages will be logged for the Jobs import, no more than 100 error and warning messages will be logged for the Job User Field import, etc.  Limiting error and warning messages can be useful when debugging large dataset imports that have the same error and warning messages being reported.  Limiting error and warning messages speeds up the run and debugging process.  If the number of messages is limited, the Import will still report on the total number of errors and the total number of warnings at the end of the log.

9.Immediately save error messages  This option enables the import to immediately save any error or warning messages to the log.  If not selected, the messages are held in memory and written to the log at the end of each SQL import type, saving processing time.  If selected, error and warning messages are written to the log immediately, thus slowing down the import.

10.Apply import table  If the “Apply import table” is selected and a new operation is being imported, DMS will look to see if the imported operation has any matching import interface operations.  Note that only new operations that are being added to DMS will have the Import Interface Table applied; existing operations in DMS will not be changed by the Import Interface Table settings.  See Import Interface Table for more information.

11.Trace records being read  Check this prompt if you want to display the information currently being imported as well as write the information being imported to the import log. 

12.Set material if Available  If you are importing material you can chose how the Material Available flag is set.  If selected and the material available import is true and the material received date is blank, then the import will set the material date received to the current date.  If this option is not selected, the Material Date Received is not updated during the import. 

13.Add assembly allocations (BOM)  ADD assembly allocations enables a component job in one assembly to feed into a component job in another assembly or to feed an independent job.  If an assembly component with a given order number exists and the same order number is added to a different assembly, the import will automatically set up an allocation (BOM link) between the first assembly component and the second assembly component.  If this option is not selected, the duplicate order number being added to an assembly will be rejected.

14.Backflush operation status  If selected, the import will set all operations from the last completed operation back to the beginning of a job to a status of complete.  If not selected, a backflush does not occur.

15.Check often for messages (slower)  If this option is selected, the import checks for the option to manually abort the import process during the importing of each record.  If not selected, the import checks for the option to abort the import every 200 records if running interactively or every 500 records if run in a batch mode.  If selected, the import runs slower.

16.Use Fetch (select number of rows)  This option enables the use of the SQL Offset and Fetch commands automatically.  To automatically use the Offset/Fetch commands for larger datasets, enter the number of rows to perform an SQL Fetch per iteration (i.e., 5,000, 10,000, etc.).  The DMS Import will then fetch the given number of rows at a time to improve memory performance for large datasets.  If the Fetch is being used, the import will display current Offset and Fetch so that you can see its iteration progress. The display and progress bar will not show the total number of records being imported.  Instead, it displays the total for the current SQL Fetch.  To use the Fetch option, no changes need to be made to the SQL scripts; however, an Order By SQL command must be present in the scripts (a requirement for most DMS import scripts).  To use the Fetch command in a command/batch process, the command UseFetchRows= would be added to the command text file as in:
          
          UseFetchRows=10000
 

17.Add inventory if required  Check this prompt if you want to add inventory to the DMS inventory database that does not already exist in DMS.  Do not check this prompt if you want DMS to generate an error when adding material items that specify inventory that do not exist in DMS.  If checked, DMS will add any new inventory items to the DMS inventory table found when importing material detail. (In most cases, this prompt should be unchecked.  The prompt applies to those users who want to populate the inventory table in DMS.)

18.Click OK to start the import process or Cancel to abort the import.  If OK is selected, DMS will respond with the following confirmation window:

 

Click OK to start the import or click Cancel to cancel the import.  If you click OK, DMS will start the import and display the import progress on the screen.

During the import you can abort the process by clicking the Abort button.  If the Abort button is clicked, DMS will ask you to confirm the abort by displaying the following window.  If you click Yes, the import process will be aborted.  If you click No, the abort will be ignored and the import will continue.

 

Also see Automatic Execution Commands