Xtract Universal is an SAP connector that enables you extract save data streams from different SAP ERP and BW objects to different target systems. Xtract Universal offers a command tool that can be used for automating and scheduling the SAP extractions and for integration with different ETL Tools.

Powershell also is a powerful and popular task automation and configuration framework from Microsoft that is based on .Net framework and includes a command-line shell and a scripting language.

In this blog, I will show tips how to use PowerShell in combination with Xtract Universal.

How to run an extraction

Let us start with running an Xtract Universal extraction using the command tool xu.exe in  PowerShell.

# execute an Xtract Universal extraction using the command tool xu.exe in a PowerShell script
# 2>&1 redirects standard error (the 2) to the same place as standard output (the 1)
&'C:\Program Files\XtractUniversal\xu.exe' -s "localhost" -p "8065" -n "SAPSalesCube" 1>$null 2>&1

For now, we will throw away the PowerShell output and error. Later we will check the result code.

How to run an extraction with a parameter

Now let us run an extraction with a parameter.

# the extraction has a variable CalendarMonth that needs a value in the format YYYYMM, e.g. 201712
&'C:\Program Files\XtractUniversal\xu.exe' -s "localhost" -p "8065" -n "SAPSalesCube" -o CalendarMonth='200401' 1>$null 2>&1

How to run an extraction with a parameter using PowerShell variables

Now let us run an extraction with a parameter using a PowerShell variable.

# set the path to the installation folder
$XUCmd = 'C:\Program Files\XtractUniversal\xu.exe'
# XU server & port
$XUServer = "localhost"
$XUPort = "8065"
# extraction name
$XUExtraction = "SAPSalesCube"

# Setting Calendar month variable
# the extraction has a variable CalendarMonth that needs a value in the format YYYYMM, e.g. 201712
$myCalendarMonth = (Get-Date -format "yyyyMM")

# run an extraction with one parameter
&$XUCmd -s $XUServer -p $XUPort -n $XUExtraction -o CalendarMonth=$myCalendarMonth 1>$null 2>&1

How to run an extraction with multiple parameters 

Now let us run an extraction with multiple parameters.

# run an extraction with multiple parameters
&$XUCmd -s $XUServer -p $XUPort -n $XUExtraction -o CalendarMonth=$myCalendarMonth -o clearBuffer=true 1>$null 2>&1

How to create a function to run an extraction

Now let us create a function that runs an extraction, checks the exit code and writes an output.

# Function to run an XU extraction
Function XURun($XUCmd, $XUServer, $XUPort, $XUExtraction, $XUParameters)
{
Try {
$parameters = $XUCmd + " " + $XUServer + " " + $XUPort + " " + $XUExtraction + " " + $XUParameters

if([string]::IsNullOrEmpty($XUParameters)){
&$XUCmd -s $XUServer -p $XUPort -n $XUExtraction 1>$null 2>&1
} else {
&$XUCmd -s $XUServer -p $XUPort -n $XUExtraction -o $XUParameters 1>$null 2>&1
}

# check the last exit code
# 0: successful
# else unsuccessful
if($LASTEXITCODE -eq 0) {

write-host -f Green "The last command $parameters has been executed successfully " (Get-Date)

} else {

write-host -f Red "The last execution for $parameters failed with error code $LASTEXITCODE!" (Get-Date)
Write-Host $errorMessage
}
}
Catch {

write-host -f Red "Error running XU extraction!" + (Get-Date) $_.Exception.Message
}
}

# define error message
$errorMessage = @'
If the command completes an operation successfully, it returns an exit code of zero (0).
In case of an error, it will return one of the following (http status) codes:
HTTP Statuscodes (e.g. 404 when the extraction does not exist)
1001 An undefined error occured
1002 Could not find the specified file
1013 Invalid input data
1014 The number of arguments is invalid
1015 The parameter name is unknown
1016 The argument is not valid
1053 Something is wrong with your URL
1087 The parameter is invalid

check the online help for further information

http://help.theobald-software.com/Xtract-Universal-EN/default.aspx?pageid=run-from-a-command-line

'@

# run an extraction with multiple parameters
$XUParameters = "clearBuffer=True -o CalendarMonth=$myCalendarMonth"
$XUResult = XURun -XUCmd $XUCmd -XUServer $XUServer -XUPort $XUPort -XUExtraction $XUExtraction -XUParameters $XUParameters

xurun

How to loop an array with different parameter values

The following script uses a loop and runs an extraction with different parameter values. The parameters values are defined in an array.

$Months = @("200401","200402","200403")
foreach($Month in $Months){
XURun -XUCmd $XUCmd -XUServer $XUServer -XUPort $XUPort -XUExtraction $XUExtraction -XUParameters CalendarMonth=$Month
}

How to run multiple extractions in sequence

The following script uses a loop and runs multiple extractions in sequence.  The extraction names are defined in an array.

Function XURun-Multi ($XUCmd, $XUServer, $XUPort, $XUExtractions,$XUParameters){

foreach($XUExtraction in $XUExtractions){

XURun -XUCmd $XUCmd -XUServer $XUServer -XUPort $XUPort -XUExtraction $XUExtraction -XUParameters $XUParameters
}
}

#$XUExtractions = "SAPCustomers", "SAPPlants","PSSAPCustomers", "PSSAPPlants"
$XUResult = XURUN-Multi -XUCmd $XUCmd -XUServer $XUServer -XUPort $XUPort -XUExtractions $XUExtractions

xupowershell-multiple

How to run multiple extractions in parallel 

To run multiple commands in parallel with PowerShell there exist many approaches. One of them is using PowerShell Workflow. Check the following links for that:

https://docs.microsoft.com/en-us/system-center/sma/overview-powershell-workflows
http://www.powershellmagazine.com/2012/11/14/powershell-workflows/

The first workflow uses a ThrottleLimit to limit the number of in parallel running extractions.

# Define Workflow 1
# Run multiple Extractions in parallell using powershell workflow
Workflow XURun-Parallel { param ($XUCmd, $XUServer, $XUPort, $XUExtractions, $XUParameters, $ThrottleLimit)

foreach -parallel -throttlelimit $ThrottleLimit ($XUExtraction in $XUExtractions){

InlineScript{
if([string]::IsNullOrEmpty($XUParameters)){
&$Using:XUCmd -s $Using:XUServer -p $Using:XUPort -n $Using:XUExtraction 1>$null 2>&1
} else {
&$Using:XUCmd -s $Using:XUServer -p $Using:XUPort -n $Using:XUExtraction -o $Using:XUParameters 1>$null 2>&1
}
}

}
}

# 4 parallel extractions
$ThrottleLimit = 4
XURun-Parallel -XUCmd $XUCmd -XUServer $XUServer -XUPort $XUPort -XUExtractions $XUExtractions -XUParameters $XUParamters -ThrottleLimit $ThrottleLimit

# Define Workflow 2

# Run multiple Extractions using PowerShell workflow

Workflow XURun-Parallel2{ param ($XUCmd, $XUServer, $XUPort, $XUExtractions, $XUParameters, $ThrottleLimit)

foreach -parallel -throttlelimit $ThrottleLimit ($XUExtraction in $XUExtractions){

InlineScript{

Try {
$parameters = $Using:XUCmd + " " + $Using:XUServer + " " + $Using:XUPort + " " + $Using:XUExtraction + " " + $Using:XUParameters

if([string]::IsNullOrEmpty($Using:XUParameters)){
&$Using:XUCmd -s $Using:XUServer -p $Using:XUPort -n $Using:XUExtraction 1>$null 2>&1
} else {
&$Using:XUCmd -s $Using:XUServer -p $Using:XUPort -n $Using:XUExtraction -o $Using:XUParameters 1>$null 2>&1
}

# check the last exit code
# 0: successful
# else unsuccessful
if($LASTEXITCODE -eq 0) {

write-host -f Green "The last command $Using:parameters has been executed successfully " (Get-Date)

} else {

write-host -f Red "The last execution for $Using:parameters failed with error code $LASTEXITCODE!" (Get-Date)
Write-Host $errorMessage
}
}
Catch {

write-host -f Red "Error running XU extraction!" + (Get-Date) $_.Exception.Message
}
}

}
}

# 4 parallel extractions
$ThrottleLimit = 4
XURun-Parallel2 -XUCmd $XUCmd -XUServer $XUServer -XUPort $XUPort -XUExtractions $XUExtractions -XUParameters $XUParamters -ThrottleLimit $ThrottleLimit

How to get the list of defined extractions 

Xtract Universal offers also an HTTP API to access the defined extractions and their metadata and log, the server log and further information.

The following function gets the list of extractions from the repository. The output will have the following format for each extraction.

Name : BWCubeFIGL
Type : BWCube
Source : sapbw
Destination : tableau
LastRun : 2018-04-25_12:44:02.422
RowCount : 2733787
LastChange : 2018-02-16_12:18:29.475
Created : 2018-02-14_11:25:47.718

<pre>Function XUGet-Extractions($XUServer, $XUPort){
$XUExtractions= (Invoke-WebRequest "http://$XUServer`:$XUPort").Content | ConvertFrom-CSV
return $XUExtractions
}
$XUExtractions = XUGet-Extractions $XUServer $XUPort
1
The following functions gets the list of extraction names from repository. This list can be then used e.g. to run the extraction or to check their logs.
1
Function XUGet-ExtractionNames($XUServer, $XUPort){
$XUExtractions = XUGet-Extractions $XUServer $XUPort
$XUExtractionNames = $XUExtractions | foreach { $_.Name } #| where{$_ -like "*PSSAP*"}
return $XUExtractionNames
}
$XUExtractionNames = XUGet-ExtractionNames $XUServer $XUPort

# run all the extractions in the list
XURun-Parallel2 -XUCmd $XUCmd -XUServer $XUServer -XUPort $XUPort -XUExtractions $XUExtractionNames

How to get the latest log of the extractions 

The following script gets the latest log of the extractions and writes a colorful output depending on the log status.

Function XUGet-Log($XUServer, $XUPort){
$XUExtractionNames = XUGet-ExtractionNames $XUServer $XUPort
$XULog = @{}
foreach ($XUExtractName in $XUExtractionNames) {
# concatenate URL
$XUURL = "http://$XUServer`:$XUPort/log/?req_type=extraction&name=$XUExtractName"
# get log, convert it to csv, sort by timestamp and select the newest log
$newestLog = (Invoke-WebRequest $XUURL).Content | ConvertFrom-CSV | Sort-Object Timestamp | Select-Object -Last 1
# chech log status
Switch ($newestLog.StateDescr) {
"FinishedNoErrors"{ write-host -f Green $XUExtractName $newestLog}
"FinishedErrors" {write-host -f Red $XUExtractName $newestLog}
"Running" {write-host -f Yellow $XUExtractName $newestLog}
"NotAvailable"{write-host -f Blue $XUExtractName $newestLog}
}
$XULog.Add($XUExtractName, $newestLog)
}
return $XULog
}
$XULog = XUGet-Log $XUServer $XUPort

xupowershell-log

How the get the metadata of the extractions 

This function gets the metadata of the extractions, including field names, data types etc.

The output will have the following format for each extraction.

POSITION,NAME,DESC,TYPE,LENGTH,DECIMALS
0,WERKS,Plant,C,4,0
1,NAME1,Name,C,30,0
2,KUNNR,Customer number of plant,C,10,0
3,NAME2,Name 2,C,30,0

# Get Metadata
# http://[host]:[port]/metadata/?name=[extractionName]
Function XUGet-Metadata($XUServer, $XUPort){
$XUExtractionNames = XUGet-ExtractionNames $XUServer $XUPort
$XUMetadata = @{}
foreach ($XUExtractName in $XUExtractionNames) {
# concatenate URL
$XUURL = "http://$XUServer`:$XUPort/metadata/?name=$XUExtractName"
# get log, convert it to csv, sort by timestamp and select the newest log
$tmpmeta = (Invoke-WebRequest $XUURL).Content | ConvertFrom-CSV
$XUMetadata.Add($XUExtractName, $tmpmeta)
}
return $XUMetadata
}
$XUMetadata = XUGet-Metadata $XUServer $XUPort

I hope you find this information helpful. Should you have any suggestions or questions, I would be pleased to hear from you.

Check the script on GitHub.

Check the following useful links for further information

https://theobald-software.com/en/xtract-universal-productinfo.html

http://help.theobald-software.com/Xtract-Universal-EN/default.aspx?pageid=run-from-a-command-line

http://help.theobald-software.com/Xtract-Universal-EN/default.aspx?pageid=metadata-access-via-http

http://help.theobald-software.com/Xtract-Universal-EN/default.aspx?pageid=logging-access-via-http

https://docs.microsoft.com/en-us/powershell/

Written by Khoder Elzein

Khoder is responsible for presales and always has an attentive ear for our customers, prospectives and partners. When travelling in Germany or abroad, he provides customer support at PoCs, workshops and on training courses. He also looks after the further development of our software solutions. Khoder has been working in IT since the turn of the millennium; he has been a member of team Theobald since 2009. When it comes to private interests, family, nature and reading feature at the top of his list – along with innovative fusion cuisine, as you may infer from his favourite dish of Swabian cheese noodles with tabouleh.