Scribe Insight 7.9 – Some Really Nice Features

Imran Popatiya

In the recent release of Scribe Insight, Scribe has added some much needed features to the product. Below are some brief details about the new features. For more information and details about the release, please download the release notes from here.

Global Connection Pool Insight version 7.9.0 converts connections in a DTS and saves connection information in a global pool of connections in the Scribe Internal Database, enabling all DTS files on your Scribe system to use the same connection configuration. Changes to this connection information is available to all DTS files on your system.
For example, if you configure a Dynamics CRM connection and three DTS files need access to a Dynamics CRM system, all three DTS files can use that one connection. Then, if the user name for Dynamics CRM is updated through one of those DTS files, all three DTS files use that updated user…

View original post 234 more words

Advertisements

DBLOOKUP vs FILELOOKUP in Scribe Insight

DBLOOKUP

DBLOOKUP searches a column of a database table for a value and returns the corresponding value from another column in the same row.

Syntax

DBLOOKUP(TextSourceField, “connection“, “table“, “lookup_field“, “substitution_field” )

Parameter

Description

TextSourceField The value for which to search in the lookup_field column.
connection The connection where this lookup table resides.If the connection is Scribe Internal Database, the SCRIBE user must have at least SELECT privileges on the lookup table.
table The name of the lookup table.
lookup_field The name of the column in the lookup table that stores the value to search for.
substitution_field The name of the column in the lookup table that stores the value to return.

Remarks

This function requires special handling when working with bulk operations. See Using Bulk Operations for more information.

Useful for substituting full values for codes or vice versa.

To optimize performance, if DBLOOKUP is called again with the same parameters, it returns the same result (without performing another lookup).

If you are migrating a large number of records into a target, you might want to use DBLOOKUPCached. The working of DBLOOKUPCached is similar to DBLOOKUP just that it caches all the values for the job which can greatly optimize your performance.

Below is actual difference between the two.

  • DBLOOKUP: Caches only one value at a time (e.g. if you ask for the same value; you’ll get the cached value – if you ask for a different value, that new value will be retrieved [and cached; removing the previous value cached]).
  • DBLOOKUPCached: Unlike DBLOOKUP, caches more than one value (so that each value asked for is always cached [can have some memory implications if you do lots of lookups]).

We had a scenario where we imported around 20k records in CRM. When we replaced the DBLOOKUP with DBLOOKUPCached, the time required for the import reduced by almost 50 per cent.

If we have two fields that we need to look on, we also have DBLOOKUP2 which takes two fields for the lookup. Please find more details here.

Examples

The following example uses this table named TITLE_CODE in a target database, which has the connection name of Target.

Code Value
1 Owner
2 President
3 Manager
4 Executive Director
5 Principal

This function returns “Manager”:

DBLOOKUP(“3”, “Target”, “TITLE_CODE”, “Code”, “Value”)

FILELOOKUP

Description

Searches the left column of a cross-reference file for a value and returns the corresponding value from the right column.

Syntax

FILELOOKUP(TextSourceField, “filename“, “section“)

Parameter

Description

TextSourceField The value for which to search in the left column. The search is case sensitive.
filename The name of the cross-reference file. This file is a text file, structured like a Windows *.INI file. If the file is not located in the same directory as the DTS file, you must enter the full path (using the UNC) to the file.
section The section name within the file that stores the cross-reference values. The section name is enclosed in square brackets in the *.INI file.

Remarks

Useful for substituting full values for codes or vice versa.

Examples

The following example uses this cross-reference file named XREF.INI.

[Title_Code]

1=Owner

2=President

3=Manager

4=Executive Director

5=Principal

This function returns Manager:

FILELOOKUP(“3”, “XREF.INI”, “Title_Code”)