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:

output.customer_identifier=stringToInteger(input.customer_identifier)

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

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.

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 (http://blog.SQLAuthority.com)

About these ads

7 thoughts on “SQL SERVER – Automated Type Conversion using Expressor Studio

  1. 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,

  2. 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

  3. 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.

  4. Pingback: Top 10 expressor blogs in 2010 | expressor Data Integration Blog

  5. Pingback: SQL SERVER – 4 Tips for ETL Software IDE Developers | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s