Copy metadata table between 2 metadata servers with code

[Filipvdr]

Hi all,

I'm working on a custom transformation in DI studio which will load my table from a SQL server library to a VA LASR Library.

I have a working transformation which loads the SQL server table to LASR. But i'm losing my formats and label information.

They are installed on 2 different metadata servers. My VA environment has access to the SQL server library through a libname connection.

A solution would be to connect to the SQL server library with the "META" option in the libname. But then my metadata of the table has to be known there. I don't want to sync or import anything manually, or wait for a sync to be finished.

What i want is that in my custom transformation the metadata of my table is being copied to the VA metadataserver so it can use the "META" option in the libname to get all necessary information.

Is there a way to copy the metadata of a table from one metadataserver to another with sas code?

Thanks!

[ErikLund_Jensen]

Hi @Filipvdr 

You can use the SAS command line tools Exportpackage and ImportPackage and call them from a SAS Program, something like this (export shown):

%let exportpgm = /sas/prod/sashome/SASPlatformObjectFramework/9.4/ExportPackage;
 
%let export_connectopt = %str(-host "sas-meta-p-01.odknet.dk" 
                                -port 8561 
                                -user "domain\userid" 
                                -password "mypassword"
%let typelist = Table;
  
/* SPK package file */
%let package = &pakkesti/package_1.spk;
 
/* Source metadata folder */
%let objects = &MetadataSti; /* Source metadata folder */
  
%let eksportlog = &pakkesti/eksport.log;

%let cmdstr = "&exportpgm" &export_connectopt 
                     &typelist 
                     -package "&package" 
                     -objects "&objects"
                     -subprop -log "&eksportlog" > /dev/null;

%put &cmdstr;
%sysexec &cmdstr;

The -subprop option writes mapping information to a subprop file outside the package, so it can be edited with a program to perform the same as the mapping-dialogue in an interactive import.. 

This code uses the specified metadata folder as a starting point and exports all objects of types specified in typelist. I guess it might be possible to specify a given object only. Note that you might need to change some options in the init file, that contains options similar to the checkboxes in an interactive export/import.

All necessary information can be found in the documentation:

SAS® 9.4 Intelligence Platform: System Administration Guide, Fourth Edition

[Filipvdr]

would this be possible to start from within SAS Eguide?

[ErikLund_Jensen]

Hi @Filipvdr 

I have limited experience with SAS EG, but I can't se why not. Note that It requires your EG workspace server to be configured to allow the use of 'X' commands, and not all administrators like that. 

[Filipvdr]

Hi Erik, thanks for the tips..

In the end I found this solution:

Output a proc contents to a shared location from metadataserver 1.

Register the table on metadataserver 2

With some coding and metadatascripting I applied the labels and formats on the table on metadataserver 2.