Careful.. using over ride SQL query on your Source Qualifier!!

July 28, 2013

This is something i always used to get myself in confusion and so in trouble. Hell you feel when you try debugging why my mapping works wrong 😦

This time around when i was all again with the same bug getting on top of my head, and good enough to frustrate me. And as usual i was able to make out the cause, and
by EOD i decided ‘this has to go on to the blog’.

The issue was, i was getting a blank space in the target where i was expecting some string data value to flow in, and on debugging the mapping when i got close to the
source qualifier to find that the source value flowing was pulled as ‘0’. I was under an assumption, if i include a SQL over ride in my Source qualifier the the port
order/port name/port data type shall not be considered as in the SQ. I could understand this was happening for only one column where a string value was flowing as an
integer ‘0’ value.

And now i could understand that this was all because the column’s data type was integer whereas the respective column in SQL over ride was of string data type. The
rest of the columns had the data types matching though the Port name might be different will not matter.

For a better understanding, let me take an instance:

My Ports as in source qualifier –

Port1 – Integer
Port2 – Ineteger
Port3 – String

whereas my SQL over ride goes like this –

Select Column_A,Column_B from some_table where Column_B=’some value’

the SQL table structure as below:
Column_A – Integer
Column_B – String

And here when we pull the data from source, our mapping shall pull Column_A values in Port_1 rightly, whereas the values for Column_B shall be fetched wrongly with the
data type not matching between the SQ and the SQL query. A string might be pulled as an integer value with data getting wrong.

Important note: we should also make sure to change the Precision in our SQ (we cant change the data type in a SQ, will invalidate our mapping), which shall give out a
warning while session is run but this can be ignored with the right data loading from the source.

So how get this resolved? All we got to do is change the port order in the SQ as i have done for the above instance,

Port1 – Integer
Port3 – String
Port2 – Integer

So now Column_B will be in Port3, data type matching. Also make sure to change the precision value to match with the SQL column’s precision. And all will be set to go
for the data flowing perfectly good.

Hope this helps, share and let others know. Have a bug free day 🙂

Advertisements

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

%d bloggers like this: