A MSSQL lookup column takes data from a data source, inserts it into a MSSQL query and returns a value for inclusion in the destination.
To define a MSSQL lookup column:
| 1. | Open Map >> Create, or select the Maps Quick Link and double click on the map to be edited. |
| 2. | If creating a new map enter the map id and description. |
| 3. | If creating a new map enter the data source and key field information. |
| 4. | If creating a new map select the required destination, then double click on the mapping type required. |
| 5. | Select Additional Columns from the toolbar at the top of the mapping window. |
| 6. | Select Columns >> MSSQL Lookup from the toolbar on the columns window, or double click on the column to be edited. |
| 7. | Enter a name for the MSSQL lookup column. Names must be unique within a map. |
| 8. | Select a Connection from the drop down list. If Custom Connection is selected the server, authentication details and database selection will be required. If a default connection is selected the server, authentication details and database selections will be grayed out and defaulted from the selected connection. If a new default connection should be set select the manage default connections button and enter the new details. |
| 9. | Enter the name of the MSSQL Server / Instance to be used for this data source. This may be defaulted by the connection selected in step 8. |
| 10. | Select the required authentication type. Windows authentication will use the details for the current windows user to try to connect to the MSSQL server. Selecting SQL Server Authentication will required the entry of a MSSQL username and password. This setting may be defaulted by the connection selected in step 8. |
| 11. | If required enter the MSSQL login username. This may be defaulted by the connection selected in step 8. |
| 12. | If required enter the MSSQL password. This may be defaulted by the connection selected in step 8. |
| 13. | Select the Refresh button to use the entered details to connect to the MSSQL server. If the connection is successful available databases will be displayed in the database drop-down list. |
| 14. | If required select the MSSQL database from the drop-down list. This selection may be defaulted by the connection selected in step 8. |
| 15. | Select the table that contains the data to be returned to SmartConnect. If the information required by the lookup goes over more than one table the Modify button may be used to open Query Builder and manually build the query. If Query Builder has been used the table name will be changed to User Defined Query. |
| 16. | Select the column to be returned to SmartConnect. The available columns are either all columns in the selected table, or all columns in the query defined through Query Builder. |
| 17. | Select what should happen if the lookup fails to return a value. Valid options are as follows: |
| • | Return error if no match - map processing will stop and throw an error if no match is found in the MSSQL lookup. |
| • | Return source data if no match - if no match is found, the value of the first criteria field passed to the column is returned. |
| • | Return blank if no match - an empty string is returned if no data is found. |
| • | Return default if no match - the default entered in step 18 is returned if no data is found. |
| 18. | If return a default value has been selected in step 17, enter the value to be returned in the Default Value field. |
| 19. | Select  in the Lookup Criteria grid to enter the lookup criteria. |
| 20. | The Source drop-down contains the fields within the data source. The Target drop-down contains the columns within the selected table or query. The operator may be set to Equal To or Not Equal To. Note: multiple criteria rows may be added. Multiple rows are always added with an And keyword. |
| 21. | Select OK to save the record. The lookup field will now be available in the mapping window. |
