Creating SQL Statement Lookup Maps

SQL Statement Lookup map is conceptually similar to Database Lookup but it allows you to enter a custom SQL statement to match the incoming values to some columns in the lookup table as controlled by your SQL code. Incoming values are expressed as parameters, using the following syntax: @ParameterName. They should be enclosed in double quotes if the value returned by the parameter is a string.

To add a SQL Statement Lookup map, drag the SQL Statement Lookup object from the Maps group in the Flow toolbox and drop it on the dataflow.

An example of what a SQL Statement Lookup object might look like is shown below:

To configure the properties of a SQL Statement Lookup object after it was added to the dataflow, right-click on it and select Properties from the context menu. The following properties are available:

• SQL Statement Transformation Properties screen: Provide the connection info to the database in which the lookup table resides. To enter the connection info, click the … button, which will open the Database Connection dialog.

Enter your SQL statement in the input area. For example,

select * from Orders where OrderId = @OrderNum

OrderNum appears as an input port on the SQL Statement Lookup object, as shown below:

• Select one of the following options to specify what happens when a value is not found in the lookup list:

  • `Assign Warning and Proceed – a null value is returned for all output fields, and a warning status is assigned to the record

  • Assign Error Message – an error is triggered for the record

• General Options screen: This screen shares the options common to most objects on the dataflow.

Usage

Using SQL Statement Lookup transformation in your dataflow:

An example of a dataflow using SQL Statement Lookup object to look up ARM Margin and Note Rate by Customer ID is shown below: