Dynamic table definition

[pandeesh]

Hi,

Is there any way to fetch the table definition for all the columns in run time and load the data to target.
It will avoid any issues if any of the columns are dropped in future
Let's just consider the direct load alone without any transformations.
a)We can achieve this in connecting database via commandline and redirect to some target file.
I would like to know, whether the same can be achievable in a job?

Thanks

[ray.wurlod]

Yes, at least for some databases. I have done this. A sequence reads the table definition from the system tables and constructs SELECT statement from these, and passes the SELECT statement as a job parameter to a job that makes heavy use of runtime column propagation.

The job that constructs the SQL statement is a server job. It begins by echo as a before-job subroutine, appends each column name and trailing comma to the file, and uses an after-job subroutine to echo the remainder of the SELECT statement (for example 'X' FROM #jpSchemaName#.#jpTableName#)

[BI-RMA]

Hi pandeesh,

Are You sure You want an automated solution to react to dropped columns in a target-database? Be aware that a column dropped in Your taget is effectively the same situation as a missing new value on Your target. If You handle one situation You also handle the other. That way You may load dozens of files successfully, but incomplete, to Your database until You realize You missed something when someone asks for the data.

[eph]

Hi,

I personally use orchdbutil via execute command activity plus a server job to beautify the result before loading data. I guess it's the best way if you want something dynamic.

Regards,
Eric