SQL SERVER – Automated Type Conversion using Expressor Studio

Recently I had an interesting situation during my consultation project. Let me share to you how I solved the problem using Expressor Studio.

Consider a situation in which you need to read a field, such as customer_identifier, from a text file and pass that field into a database table. In the source file’s metadata structure, customer_identifier is described as a string; however, in the target database table, customer_identifier is described as an integer. Legitimately, all the source values for customer_identifier are valid numbers, such as “109380”.

To implement this in an ETL application, you probably would have hard-coded a type conversion function call, such as:


That wasn’t so bad, was it? For this instance, programming this hard-coded type conversion function call was relatively easy.

SQL SERVER - Automated Type Conversion using Expressor Studio Canonical_mapping_500px

However, hard-coding, whether type conversion code or other business rule code, almost always means that the application containing hard-coded fields, function calls, and values is: a) specific to an instance of use; b) is difficult to adapt to new situations; and c) doesn’t contain many reusable sub-parts. Therefore, in the long run, applications with hard-coded type conversion function calls don’t scale well. In addition, they increase the overall level of effort and degree of difficulty to write and maintain the ETL applications.

To get around the trappings of hard-coding type conversion function calls, developers need an access to smarter typing systems. Expressor Studio product offers this feature exactly, by providing developers with a type conversion automation engine based on type abstraction.

SQL SERVER - Automated Type Conversion using Expressor Studio Semantic_types_500px

The theory behind the engine is quite simple. A user specifies abstract data fields in the engine, and then writes applications against the abstractions (whereas in most ETL software, developers develop applications against the physical model). When a Studio-built application is run, Studio’s engine automatically converts the source type to the abstracted data field’s type and converts the abstracted data field’s type to the target type. The engine can do this because it has a couple of built-in rules for type conversions.

So, using the example above, a developer could specify customer_identifier as an abstract data field with a type of integer when using Expressor Studio. Upon reading the string value from the text file, Studio’s type conversion engine automatically converts the source field from the type specified in the source’s metadata structure to the abstract field’s type. At the time of writing the data value to the target database, the engine doesn’t have any work to do because the abstract data type and the target data type are just the same. Had they been different, the engine would have automatically provided the conversion.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Database, SQL Scripts, SSIS
Previous Post
SQL SERVER – DBA or DBD? – Database Administrator or Database Developer
Next Post
SQL SERVER – 3 Simple Puzzles – Need Your Suggestions

Related Posts

5 Comments. Leave new

  • Manoj Kumar Nayak
    November 30, 2010 9:25 am

    Dear Friend,

    Every day I see your article, Its always good article but I want to know how we configure a Database Mirroring between two database server, Kindly publish this type of article, it’s very helpful to me & other people also, I expected you publish soon.

    Thanking you,

  • Nice article. Keep up the excellent work. I read your posts daily.

  • Sadly it requires visio to be installed.

  • This is great, especially when have to deal with a large amount of columns. Is this tool a competitior to SSIS or is it some kind of add-on.

    Thank you

  • Jose Mariano Alvarez
    December 3, 2010 9:14 am

    It looks interesting. But you are assuming that there are many connections between systems that require these transformations (and between different pairs should represent similar information). I do not think that’s what usually happens in ETL, and less if it is dedicated to DW.
    The good thing is that it has a freeware version.


Leave a Reply