Bringing parity to SQL lookup and SQL validation (in in database)

HI,

Currently the lookup value in transformation allows for input be be mapped in format - <parameter1> 
While
The Is  value in database or if value in database in Validation allows for input to be mapped by <value> 

1. I get the logic , since only one value is mapped in validation ,it's easier to do the same in validation 
2. But this is another overload or hidden thing to remember in AETL for user to remember or visit wiki upon , it' might be easier to bring parity by brining the same <parameter> option in validation too (you can keep allowing <value> for older customers)


I am attaching the example from WIKI
   
 

Thank you we will add to the list of things to do.

it is tempting to use "Get value in the database" and "SQL Lookup" because it is easy it must me be avoided.

Imagine that you loading 10 million records of data into the database.
For every record you run "Get value in the database" and "SQL Lookup" functions
That is 20 million SQL statements.
It will take very long time,
overload the server and
make your DBA very unhappy  

 

admin wrote:

Thank you we will add to the list of things to do.

it is tempting to use "Get value in the database" and "SQL Lookup" because it is easy it must me be avoided.

Imagine that you loading 10 million records of data into the database.
For every record you run "Get value in the database" and "SQL Lookup" functions
That is 20 million SQL statements.
It will take very long time,
overload the server and
make your DBA very unhappy  



 

I dont' like it , but hear you are , all high and mighty with proper Logic   . PS I literally just faced this problem and was about to make a post , so thanks for answering 

Question for you - Are lookups from text file subjected to same package rule of Subdirectory ?  

Let's say my package is supposed to run in C:\My awesome\Package and my lookup is in C:\Lookups\Static 

Imagine that you loading 10 million records of data into the database.
For every record you run "Get value in the database" and "SQL Lookup" functions
That is 20 million SQL statements.

1. I always thought , you cache the data somewhere so SQL/DB Server is hit only once for the entire transformation 
2. Your suggestion is then , we run a package/transformation and output everything to a text file in advance?

1. I always thought , you cache the data somewhere so SQL/DB Server is hit only once for the entire transformation.

It is not that simple.

What if the use does not want to cash the data?
What is the cash is to big?

2. Your suggestion is then , we run a package/transformation and output everything to a text file in advance?

I do not really understand what you are trying to achieve here. My suggestion is to use lookup transformation if possible it does support data-base as data source so there is no need to save the data into the text file in advance.  

2. Your suggestion is then , we run a package/transformation and output everything to a text file in advance?

I do not really understand what you are trying to achieve here. My suggestion is to use lookup transformation if possible it does support data-base as data source so there is no need to save the data into the text file in advance.  

I have a list of say 20000 customers, their address are kept in a sql file against a code , in this scenario 
Code | Name | Address

1. For validation , can I use Is in Database without suffering speed by using in in list and SQL as source or you advise first we transfer to txt file
2. Same for transformation