Five Stars

Implicit context loading - Retrieving schema from table - valued function

Hi,

 

I have a job as below

 

tmssqlinput --> tmap --> tmysqloutput

 

The tmssqlinput has a schema coming from a query such as "select * from etl.loadwfunction(1)". etl.loadwfunction(1) is a table valued function, which has complex query and return many derived columns.

 

So, the schema retrieval was not a problem when I was using the connection details that were hard coded in the context group

 

Now, I am planning to use implicit context loading at the project level. With this in place, and the connection details retrieved only during run time, how will I be able to retrieve the schema of the function in the tmssqlcomponent. The "Guess Schema" would not work as there are no connection details

 

I understand that I can retrieve schemas for all tables and store them as metadata. But, how do I do it when there is a requirement as above?

 

Any ideas on this?

 

Thanks in advance
Rathi

6 REPLIES
Twelve Stars TRF
Twelve Stars

Re: Implicit context loading - Retrieving schema from table - valued function

Hi,

You should retrieve the schema(s) using a specific job for which the connection parameters are issued from context variables or hard coded.

Then you can promote the shema(s) to the repository and reuse it (them) anywhere.

Hope this helps. 


TRF
Twelve Stars

Re: Implicit context loading - Retrieving schema from table - valued function

Export your connection parameters (the actual values) as context groups. You can do this within the connection configuration for your metadata. This will create a context group for that database. The context group will have your values hard coded. However, this does not matter since the implicit context load will overwrite any context variables for which values are returned. 

Rilhia Solutions
Five Stars

Re: Implicit context loading - Retrieving schema from table - valued function

So, is it like I must first configure tmssqlcomponent to take the connection from hardcoded context group, retrieve the schema and then later remove the reference from hard coded context to implicit context connection details?

Twelve Stars

Re: Implicit context loading - Retrieving schema from table - valued function

You shouldn't consider Contexts hardcoded unless you are not supplying a mechanism for them to be changed. Contexts are simply boxes for values. A lot of people do hardcode these values, but they are built to be able to handle being hardcoded and dynamically set. If you supply values to your Context Group contexts at design time, those will be the values that are used when importing schemas, for example. You do not need to remove these if you do not want to if you are using the implicit context load at runtime.

 

If the following contexts have the following set of values at design time....

 

context1 = "Hello"

context2 = "World"

context3 = ":-)"

 

.....if your implicit context load supplies new values for context1 ("Hey") and context2 ("Friend"), at runtime the values will be....

 

context1 = "Hey"

context2 = "Friend"

context3 = ":-)"

 

Context1 and Context2 changed, but Context3 remained with the value set at design time.

 

Rilhia Solutions
Twelve Stars

Re: Implicit context loading - Retrieving schema from table - valued function

Did this answer your question?

Rilhia Solutions
Five Stars

Re: Implicit context loading - Retrieving schema from table - valued function

Hi rhall_2_0,

 

Apologies for the delayed response.

 

I am testing your solution for a job that has many jobs within. So, will take a little more time to test all the scenarios.

 

Will update the results soon as I finish testing all scenarios

 

Thanks

Rathi