Building a Flexible Transformation

In Pervasive Map Designer, you create a Transformation based on a source and a target schema.  But as time goes on, the source and target schemas may change.  If your Transformation is in a production setting, you may not be able to change right away.  As long as the changes to the source or target schemas are additive, you may not have to.

Take the following Excel spreadsheet as an example.

Build a Transformation based on this schema in Map Designer:  Sales Associate (First Name, Last Name, Prefix, Suffix, Email, Phone, Street Addr 1, Street Addr 2, City, State, Country, Zip, Region).

In the future, new input columns are added: Secondary Email, Twitter ID, and Facebook ID.

 The extra columns produces a mismatch between the new input spreadsheet (with the added columns) and that of the Transformation.  The default behavior in Map Designer is to treat this as an error.  Attempting to run this will result in an

12/28/2010 15:18:26   1      0 O Global                                                           *** Execution Initialization: [xmldb:ref:///C:/Users/Public/Documents/PervasiveNoLookupTutorial/SalesAssocExcelToAccess.tf.xml] (version 1.8) 
12/28/2010 15:18:26   2      0 I Excel 2007 Source connector schema record layout 'Sheet1' is missing field 'Email' referenced in the map schema
12/28/2010 15:18:26   2      0 I Excel 2007 Source connector schema has an unmatched field 'Main Email' in record 'Sheet1'
12/28/2010 15:18:26   2      0 I Excel 2007 Source connector schema has an unmatched field 'Secondary Email' in record 'Sheet1'
12/28/2010 15:18:26   2      0 I Excel 2007 Source connector schema has an unmatched field 'Twitter ID' in record 'Sheet1'
12/28/2010 15:18:26   2      0 I Excel 2007 Source connector schema has an unmatched field 'Facebook ID' in record 'Sheet1'
12/28/2010 15:18:26   4     15 I Excel 2007 Record layouts in the map schema do not match record layouts in the connector schema
12/28/2010 15:18:26   4      0 E Access 2000 Last Error Code: 25515
12/28/2010 15:18:26   4      0 E Access 2000 Source Record 0:

and the transformation will stop.

For a more flexible Transformation, change the handling of this condition to "Use Connection, Match By Name" instead of "Treat as Error". To do this

1. Open the View > Transformation and Map Properties dialog.
2. Select the Execution Properties tab.
3. In the Source Schema Mismatch property, select "Use Connection, Match By Name".

It's best to keep your Transformations closely in sync with source and target schemas.  However, this may not always be possible in a production environment.  Sources of data may begin sending data with extra information is response to changing business requirements and it may take a while to affect a similar change on the data gathering servers.  "Use Connection, Match By Name" is a key setting to let new data be ignored and handled when the release cycle allows.