Implicit context loading - Retrieving schema from table - valued function

[Anonymous]

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

[TRF]

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. 

[Anonymous]

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. 

[Anonymous]

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?

[Anonymous · Accepted solution]

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.

[Anonymous]

Did this answer your question?

[Anonymous]

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

[Anonymous]

Hi rhall_2_0,

Apologies for such a delayed response. Your solution worked beautifully. Thanks a lot !!!

Regards

Rathi