proc metadata.How to get PHYSYCAL name of source to DI studio ETL tables.

Yura2301 — Tue, 08 Feb 2011 13:53:37 GMT

Hello,
So in general I am working on macro which has one parameter - name of ETL(DI studio Job) , and as result this macro must create table with list of source to this ETL tables.
For all this goals I use proc metadata and I've almost finished, but one issue appears - name of table in General tab of property menu can be different from real table name which can be find in "Phisical storage" tab.

Shortly about how it works- I create XML tamplate with appropritate structure and give it to proc metadata as parameter, and as result creates result XML file which consists of all needed data:

filename in "&workPath\in.xml";
filename out "&workPath\result.xml";

data _null_;
file in;
put '';
put '$METAREPOSITORY';
put 'Job';
put 'SAS';
put '';
put '404';
put '';
put "%bquote()";
put '';
...

proc metadata
header=full
in=in
out=out;
run;

After this I create XMLMap for getting source tables info,It looks like this:

filename xmlMap "&workPath\xmlMap.xml";
data _null_;
file xmlMap;
put '';
put '';
put '';
put '//Job/JobActivities/TransformationActivity/Steps/TransformationStep/Transformations/ClassifierMap/ClassifierSources/PhysicalTable';
put '';
put '//Job/JobActivities/TransformationActivity/Steps/TransformationStep/Transformations/ClassifierMap/ClassifierSources/PhysicalTable/@Id';
put 'character';
put 'string';
put '32';
put '';
put '';
put '//Job/JobActivities/TransformationActivity/Steps/TransformationStep/Transformations/ClassifierMap/ClassifierSources/PhysicalTable/@Name';
put 'character';
put 'string';
put '256';
put '';
put '';
put '//Job/JobActivities/TransformationActivity/Steps/TransformationStep/Transformations/ClassifierMap/ClassifierSources/PhysicalTable/@Desc';
put 'character';
put 'string';
put '256';
put '';
put '

';
put '';
run;

And then I extract source tables:

libname xmllib xml "&workPath\ResultData.xml" xmlmap=xmlMap;

data WORK.JobSourceTables;
set xmllib.JobST1;
run;

But as I describe in begining of this post - name of table in general tab can be different from real physycal table name which can be find in "Phisical storage" tab.

The root of this question can be related with appropriate path to physical table, in my case it is:
//Job/JobActivities/TransformationActivity/Steps/TransformationStep/Transformations/Select/ClassifierSources/PhysicalTable/@Name

But this path gives table name from general tab of property table, but I need name from "Physical storage" tab, and in documentatiuon I couldn't find how to get this.
"PhysicalTable" attribute 3 sub-attributes @id,@name and @desc sub-attributes...So may be I must check somewhere higher in tree...

Thanks in advanced!

Message was edited by: Yura2301

Message was edited by: Yura2301

Message was edited by: Yura2301

Message was edited by: Yura2301

Message was edited by: Yura2301 Message was edited by: Yura2301

Re: proc metadata.How to get PHYSYCAL name of source to DI studio ETL tables.

Tim_SAS — Tue, 08 Feb 2011 14:20:13 GMT

Hi Yura2301.

This forum is for general questions about SAS procedures.

The SAS Enterprise Data Management & Integration Forum is dedicated to the kind of of question you're asking. If you post your question there it will be seen by more knowledgeable people. Good luck!

Re: proc metadata.How to get PHYSYCAL name of source to DI studio ETL tables.

Yura2301 — Tue, 08 Feb 2011 14:24:07 GMT

Ok,
Thanks!

topic Re: proc metadata.How to get PHYSYCAL name of source to DI studio ETL tables. in SAS Procedures

proc metadata.How to get PHYSYCAL name of source to DI studio ETL tables.

Re: proc metadata.How to get PHYSYCAL name of source to DI studio ETL tables.

Re: proc metadata.How to get PHYSYCAL name of source to DI studio ETL tables.