LookupDataSource

Any Value (except for the name of an attribute) may be specified as a lookup from an external data source. This data source can be one of the following:

• A delimited text file for example, a .CSV file

• A Microsoft Excel spreadsheet file

• A Microsoft Access database file

• A Microsoft SQL Server database

• An Oracle database

You must define the data sources to be used in lookups in the mapping configuration file. The following is an example of the definition of a Microsoft Excel data source:

<MSExcelLookupDataSource
id="Excel Map"
file="C:\Mapping\Data Value Lookup\Mapping.xls"
table="Sheet1"
sourceColumn="[Source]"
targetColumn="[Destination]" />

You can specify any number of data sources in a mapping configuration file. To make use of a lookup, you must reference the Id of the data source within a mapping entry:

<Attribute name="Name" >
<Value value="[Name]" >
<Lookup id="Excel Map" >
<FailAction action="EmptyValue" />
</Lookup>
</Value>
</Attribute>

In the above example, an Excel spreadsheet is used to store a list of lookup values. The value of the Name attribute from the source system is passed to the lookup. This value is compared with the contents of the Source column in the Sheet1 of the Excel file and if a matching value is found, the contents of the Destination column is returned and used as the attribute value in the output.

Sheet1 is constructed as follows:

If no matching values are found in the data source, you can use the values specified in the FailAction element:

• FixedValue – The fall-back value to use if specified, as the Value attribute of FailAction element.

• EmptyValue – Used for the output if no matching value is found.

• DiscardElement - Used to remove the mapped attribute or association on which the lookup is applied.

• DiscardObject - Used to remove the engineering object completely from object model on which the lookup is applied.

  Notes:

  • DiscardElement cannot be applied on the Dataset attributes defined in configuration, such as the dataset's ClassID, for example,

    <Datasets>
    <Dataset id="Dataset1" >
    ...
    <ClassID value="DATASETClass1" />
    </Dataset>
    </Datasets>

  • DiscardObject cannot be applied to objects that have been assigned as Datasets, therefore, Lookup checks on input objects intended to become datasets should be done prior to converting them to datasets.

  • Lookups may return an attribute name (in square brackets) and have this resolved to the attribute value in the output.

The following data sources are supported:

<CsvLookupDataSource
id="CSVLookup"
file="C:\Mapping\Data Value Lookup\Mapping.csv"

separator=","

provider="Microsoft Access Text Driver (*.txt, *.csv)"

sourceColumn="Source"

targetColumn="Destination" />

<MSAccessLookupDataSource
id="MSAccessLookup"
file="C:\Mapping\Data Value Lookup\Mapping1.accdb"

query="SELECT [Source], [Destination] FROM [Table1]"

provider="Microsoft Access Driver (*.mdb, *.accdb)"

sourceColumn="Source"
targetColumn="Destination" />

<MSExcelLookupDataSource

id="ExcelLookup"
file="C:\Mapping\Data Value Lookup\Mapping1.xls"
provider="Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"

extendedProperties="Excel 12.0; HDR=YES"
query="SELECT [Source], [Destination] FROM [$Sheet2]"
sourceColumn="Source"
targetColumn="Destination" />

<MSSqlLookupDataSource

id="MSSqlLookup"

query="SELECT [Key], [Value] FROM [Table1]"

connectionString="Driver={SQL Server}; Server=serverxxx; Database=Databasexxx; UID=userxxx; PWD=pwdxxx;"

sourceColumn="Key"

targetColumn="Value" />

or

<MSSqlLookupDataSource

id="MSSqlLookup" connectionString="WZz2JoEigxXpWGjsUwdTAxjSG7N8rI61d+aHz503TrfeCNNcLPpmOg=="
connectionStringEncrypted="true"
query="SELECT [Key], [Value] FROM [Table1]"
sourceColumn="Key"
targetColumn="Value" />
<OracleLookupDataSource

id="OracleLookup" connectionString="WZz2JoEigxXpWGjsUwdTAxjSG7N8rI61d+aHz503TrfeCNNcLPpmOg=="
connectionStringEncrypted="true"
query="SELECT [Key], [Value] FROM [Table1]"
sourceColumn="Key"
targetColumn="Value" />

or

<OracleLookupDataSource

id="OracleLookup"

connectionStringEncrypted="false"

connectionString="Driver={Oracle in OraClient12Home1}; DBQ=serverAddressxxx; UID=userxxx; PWD=passwordxxx;"

query="SELECT Key, Value FROM Table1"

sourceColumn="Key"

targetColumn="Value" />

Notes:

• You can store the encrypted connection string details for MSSqlLookupDataSource and OracleLookupDataSource as the connecting string may contain sensitive information such as user name and password. The encrypted connection string can be created using the Encrypt tool present in the Tools tab as described in Appendix D: Encrypt Utility. If you encrypt the connectionString value, the connectionStringEncrypted attribute value must be set to true.

• The extendedProperties attribute is optional in the below-mentioned Lookup data sources. If not specified, these defaults to the following values:

  Lookup Data Sources	Value
  MSExcelLookupDataSource	Excel 12.0;HDR=YES

• Only one of the attributes either table or query can be used interchangeably in Lookup data sources mentioned below. The value of attribute sourceColumn and targetColumn has to be a column name from the mentioned table or query value.

  Lookup Data Sources	Value
  CsvLookupDataSource	Not Applicable
  MSExcelLookupDataSource	Yes
  MSAccessLookupDataSource	Yes
  MSSqlLookupDataSource	Yes
  OracleLookupDataSource	Yes

• For security reasons, it is advised that the permission of the DB (database) user account should be restricted to read-only.

• In order to ensure the security of data which is in transit between the Gateway and an Oracle or an SQL Server database, you can configure an SSL-encrypted connection between the two.

• As the encrypted password is associated with the local machine where the Gateway is running, other machines cannot use the configuration file directly. You will have to encrypt the password before using the configuration created in another machine. You can obtain an encrypted password using the encryption utility.