The procedure for defining a new SQL import is as follows:
3. In the Name field, type in a suitable name for the import. Leave the Template field blank.
4. Click OK. The Data Import dialogue box, containing a number of different tabs, is displayed.
5. In the SQL Query tab, you must now complete the fields in the Database Connection section to define the connection to the relevant source database. First select the type of connection you want.
6. For a non-ODBC connection type, enter, in the Server Name field, the DNS- resolvable host name or IP address of the computer on which the source
7. In the Database field, either enter the name of the source database or, if the connection type is ODBC, select the name of the relevant System data source.
8. Enter a valid username and password for logging into the source database. For an ODBC connection type, you can use the same credentials as those given in the System DSN definition.
9. Click the Test Connection button to confirm that the connection works. If so, the Data Import Manager will display a message indicating that the test completed successfully. If, on the other hand, you see an error message, you will have to sort out the connection details before continuing any further. 10. With a valid connection defined, you now need to define the SQL query
itself, whether implicitly or explicitly. An implicit query would, in the simplest case, just consist of a table selection. In the Table field, select the table, in the source database, from which the data is to be obtained.
An explicit query, on the other hand, would be needed if you wanted to query more than one table, or if you wanted to be specific about which records to import. In that case, select the Ad-hoc Query option and enter the required SQL statement in full in the adjacent free-text field.
Even with an implicit query, you can optionally enter record criteria in that same text field. In such a case, the WHERE clause concerned must exclude the WHERE word itself.
11. If you have entered an explicit (ad-hoc) query, select (or enter), in the Unique ID field, the name of a column of the queried table (or one of the queried tables) that the import process can use as a unique key for record selection.
12. If required, you can opt to limit the number of records to be returned by the query. This would normally be useful for testing purposes only.
13. With the SQL query defined, you now have to retrieve a sample of data from the source database so as to validate the query and to help you choose exactly which source table columns to use for the import. Click the Query
The record is displayed as a list of column names, together with their respective values. You can display other records in the set by means of the
>> and << buttons at the top right.
14. Once you have decided which columns should be included in the import process, select their associated checkboxes. Only these columns will now be available to you for mapping to the target Supportworks database, as described in the following steps.
16. In the Database field, select the database into which you want the data imported.
17. In the Table field, select the table, within that database, into which you want the data imported. Note that the Unique Key field is now filled by default with the name of the selected table’s primary-key column, and that the Target Columns list is populated with all the columns of that table.
18. If necessary, you can manually select a unique key other than the primary key.
19. By virtue of the default Data Update Operations setting, the import process will both create (insert) and update records in the Supportworks database, as dictated by their absence or presence with respect to the corresponding source records. If, however, you wish to preserve the Supportworks data on this import, you should enable the “Only allow inserts” option. Alternatively, if you wish to prevent further Supportworks records being created on this
20. You are now ready to map your previously selected source table columns to columns of your selected target table. First select the checkboxes associated with your proposed target columns.
21. Highlight one of the selected target columns and click anywhere in the Value Transformation and Assignment field.
22. For a simple mapping, click the Insert Value button and, from the menu that pops up, select the source table column that you want mapped to the target column you have highlighted. Notice that the chosen column appears in the field in the form db.<column>.
For more complex mappings, you would have to manually type the required expression into the Value Transformation and Assignment field. See the section entitled Advanced Import Techniques on page 56 to find out what is possible here using JavaScript.
23. If you want the import process to skip each source record where there is no value assigned for the column (or for any column in the expression), enable the “Non-empty value required” option. This will always be enabled if the target column being mapped is a unique key.
24. If you have entered an expression manually into the Value Transformation and Assignment field, you can confirm correct resolution of the currently highlighted target column’s values by clicking the Check Syntax button. This will display, one at a time in the Preview Value Assignment field, the values for that column as computed by the expression, using the set of sample data you previously queried. You can use the >> and << buttons to show the following and preceding values, respectively.
25. Repeat step 21 to step 24 for each of your proposed target columns. 26. Click Apply.
27. Having completed the mapping, you can now run or schedule the import. First select the Control & Schedule tab.
28. If you wish to run the import immediately, click the Run Import Now button. A separate window is displayed, in which you can see the import’s progress. 29. On completion of the import, you can click View Log in the progress
window to view the details of the import process. Note that this is a
cumulative log, which means that the current import details are appended to any existing details already logged in this session. (The log will be deleted when you exit from the Data Import Manager.) You can also view the log by using the View Log File button located next to the Run Import Now button. 30. Click Close to dismiss the progress window.
31. If you wish to schedule the import, first enable the “Schedule this import” option.
32. Specify when, or how often, the import is to run by selecting the relevant item from the “Run this import when” field’s drop-down list. If you select
In this case, select the day(s) of the week on which the import should run, and choose whether this should keep occurring indefinitely, or a specified number of times.
If you select any frequency other than “Once a day”, the multi-day selection options are replaced in all but one case with a single-day drop-down
selection field that allows you, for example, to choose the first day of the relevant period on which the import should run. The exception is “Once every period”, which retains the multi-day selection options, but replaces the Starting At field with an Every (n) Minutes field, allowing you to specify repeated occurrences of imports during the course of selected days of the week.
In the Starting At field (for those options that have it), specify the date and time at which the import is to run, or at which the import scheduling is to “switch on”. Alternatively, for the “Once every period” option, enter the period (in minutes) required between successive import occurrences. 33. Click OK.