HOW TO: Extract Substitution Parameters

dataservices metadata reports
1

The substitution parameters are repository specific and stored in the repository itself. Here’s a query that can be used to extract the raw source code used by Data Services for storing the substitution parameters:

SELECT AL_LANGTEXT.TEXT_VALUE
FROM AL_LANG
     JOIN AL_LANGTEXT
       ON (AL_LANGTEXT.PARENT_OBJID = AL_LANG.OBJECT_KEY AND
           AL_LANG.NAME = 'subvar_store')
WHERE AL_LANG.VERSION = (SELECT MAX(subq.VERSION) FROM AL_LANG subq WHERE subq.NAME = 'subvar_store');

The result set will contain multiple rows. You will need to concatenate those rows into a single string. If you were to do that and pretty it up here is what it would look like:

CREATE __BODI_SUBVARSTORE  
SET ("SV_Config_Default" = 'Configuration1', 
	  "SV_Config_Values" = '<?xml version="1.0" encoding="UTF-8"?> 
<SVConfigurations> 
	<SVConfiguration name="Configuration1"> 
		<SubVar name="RefFilesAddressCleanse">D:/BODS/DataQuality/reference_data</SubVar> 
		<SubVar name="RefFilesDataCleanse">D:/BODS/DataQuality/datacleanse</SubVar> 
		<SubVar name="RefFilesGeocoder">D:/BODS/DataQuality/reference_data</SubVar> 
		<SubVar name="ReportsAddressCleanse">YES</SubVar> 
		<SubVar name="ReportsMatch">YES</SubVar> 
		<SubVar name="SamplesInstall">D:/BODS</SubVar> 
		<SubVar name="CertificationLogPath">D:/BODS/DataQuality/certifications/CertificationLogs</SubVar> 
		<SubVar name="CompanyName"></SubVar> 
		<SubVar name="CompanyAddress"></SubVar> 
		<SubVar name="CompanyLocality"></SubVar> 
		<SubVar name="CompanyRegion"></SubVar> 
		<SubVar name="CompanyPostcode1"></SubVar> 
		<SubVar name="CompanyPostcode2"></SubVar> 
		<SubVar name="CompanyPhone"></SubVar> 
		<SubVar name="CompanyNAICSCode"></SubVar> 
		<SubVar name="USPSNCOALinkPlatformId"></SubVar> 
		<SubVar name="USPSProviderLevel">NONE</SubVar> 
		<SubVar name="CDC_NAME">SURF_ACTIVITY_TEST</SubVar> 
	</SVConfiguration> 
</SVConfigurations>');

The same data is also stored in AL_LANGXMLTEXT and can be queried this way:

SELECT AL_LANGXMLTEXT.TEXT_VALUE
FROM AL_LANG
     JOIN AL_LANGXMLTEXT
       ON (AL_LANGXMLTEXT.OBJECT_KEY = AL_LANG.OBJECT_KEY AND
           AL_LANG.NAME = 'subvar_store')
WHERE AL_LANG.VERSION = (SELECT MAX(subq.VERSION) FROM AL_LANG subq WHERE subq.NAME = 'subvar_store');

I don’t think the XML version is any easier to work with.

eganjp :us: (BOB member since 2007-09-12)

2

you could use al_engine comand to do that using -XX option
al_engine -U -P -N -S -XX@v@C:\temp\all_sub_var.xml

manoj_d (BOB member since 2009-01-02)

3

DOH!!! :hb: I didn’t even think of that. Good suggestion. :+1:

eganjp :us: (BOB member since 2007-09-12)

4

Old Post but check this out

http://allangxmltextharvest.blogspot.co.uk/

:mrgreen:

phatz (BOB member since 2009-04-21)

5

Anyone tried displaying extracted Substitution Parameters in excel just the matrix format we see in designer?

Chethan.Lingaraju :india: (BOB member since 2015-06-28)

6

Try this may help not sure. Gives all Data Flows and their configurations

Plus link to previous post

phatz (BOB member since 2009-04-21)

7

if any of you trying to export substitution parameters to CSV, here is what we did - http://scn.sap.com/community/data-services/blog/2015/07/10/export-substitution-parameters-to-csv
export-substitution-parameters-to-csv.pdf (235.0 KB)

Chethan.Lingaraju :india: (BOB member since 2015-06-28)