ExtractSap Function

ExtractSap function extracts data from an SAP R/3 system and returns it as a DataFlow.

If a column name contains illegal characters for table names, the illegal characters are converted to be underscore characters (e.g. "sap:Owner" -> "sap_Owner"). Columns are extracted as text data. Note that using this command requires installing SAP NetWeaver RFC Library.

Parameters:

• User: SAP username used to connect to SAP. Mandatory. Corresponds to the "USER" constant in SAP.
• Password: Password for the SAP user used to connect to SAP. Mandatory. Corresponds to the "PASSWD" constant in SAP.
• PasswordKey: Secure string key for the stored SAP password. Alternative for the Password property.
• Client: The SAP backend client. Mandatory. Corresponds to the "CLIENT" constant in SAP.
• AppServerHost: Hostname or IP of the specific SAP application server, to which all connections shall be opened. Mandatory if MessageServerHost is not defined. Corresponds to the "ASHOST" constant in SAP.
• MessageServerHost: Hostname or IP of the SAP system’s message server (central instance). Mandatory if AppServerHost is not defined. Corresponds to the "MSHOST" constant in SAP.
• SystemNumber: SAP system’s system number. Mandatory if SystemID is not defined. Corresponds to the "SYSNR" constant in SAP.
• SystemID: SAP system’s three-letter system ID. Mandatory if SystemNumber is not defined. Corresponds to the "SYSID" constant in SAP.
• Language: SAP language used. Default value is "EN". Optional. Corresponds to the "LANG" constant in SAP.
• PoolSize: Maximum number of RFC connections that this destination will keep in its pool. Default value is "5". Optional. Corresponds to the "POOL_SIZE" constant in SAP.
• PeakConnectionsLimit: In order to prevent an unlimited number of connections to be opened, you can use this parameter. Default value is "10". Optional. Corresponds to the "MAX_POOL_SIZE" constant in SAP.
• ConnectionIdleTimeout: If a connection has been idle for more than SapIdleTimeout seconds, it will be closed and removed from the connection pool upon checking for idle connections or pools. Default value is "600". Optional. Corresponds to the "IDLE_TIMEOUT" constant in SAP.
• Router: List of host names and service names or port numbers for the SAPRouter in the following format: /H/hostname/S/portnumber. Optional. Corresponds to the "SAPROUTER" constant in SAP.
• LogonGroup: The logon group from which the message server shall select an application server. Optional. Corresponds to the "GROUP" constant in SAP.
• QueryMode: If this number is set to "1", then the query result will have the SAP Table field names as data table column names and actual data rows as rows. If this is set to "3", the query result will get the field descriptions from the SAP query using NO_DATA parameter, i.e. the returned columns are the following (in this order): Field, Type, Description, Length, Offset. Default value is "1". Optional.
• QueryTable: Name of the SAP table to be extracted. Specifies the value for the parameter QUERY_TABLE in tab: 'Import' or function module 'rfc_read_table' in SAP. Mandatory. Note that if the query doesn't return any data, the target data table or temporary table is not created.
• Rowcount: The maximum amount of rows to fetch. Specifies the value for parameter ROWCOUNT in tab: 'Import' or function module 'rfc_read_table' in SAP. Optional.
• Rowskips: The number of rows to skip. Specifies the value for parameter ROWSKIPS in tab: 'Import' or function module 'rfc_read_table'. in SAP. Optional.
• WhereClause: A comma separated list of WHERE clause elements passed for the QueryTable. Can be used with or without the SapWhereClauseSelect parameter. If used together with the WhereClauseSelect parameter, use the WhereClause parameter first. NOTE: The default maximum length for the Where Clause string is 72 characters in SAP, so the recommended maximum length of the WhereClause value is also 72 characters. In effect, specifies the value for parameter OPTIONS in tab: 'Import' or function module 'rfc_read_table' in SAP. Optional.
• WhereClauseSelect: The SELECT query to be executed in QPR ProcessAnalyzer sandbox. Used with or without the WhereClause parameter to pass WHERE clauses to QueryTable. If used together with the WhereClause parameter, use the WhereClause parameter first. The query is expected to return a table with at least one column, as the contents from the rows in the first column of the table are concatenated together to form the WHERE clause in SAP RFC_ReadTable. Therefore, it's recommended to first create the table with the WHERE clauses into a temporary table. In addition, it's recommended to have an order number column in the table and use that in the SELECT query to make sure the WHERE clause elements are concatenated in the correct order. The default maximum length for Where Clause string is 72 characters in SAP, so the recommended maximum length for the WHERE clause string in each row of the table is also 72. In effect, specifies the value for parameter OPTIONS in tab: 'Import' or function module 'rfc_read_table' in SAP. Optional. The contents up to the first 10 rows in the first column of the SELECT query are shown in the QPR ProcessAnalyzer Script Log.
• FieldNames: A comma separated list of field names for columns to be imported. Default value is empty, resulting in all columns being imported. Specifies the value for parameter FIELDNAME in tab: 'Tables' for table 'FIELDS' for function module 'rfc_read_table' in SAP. Optional.
• Function: If you define a value for this parameter, then the new value specifies the SAP function that is called inside the #ImportSapQuery command. Optional. The default value is RFC_READ_TABLE. Another possible value is BBP_RFC_READ_TABLE.
• ConvertDataTypes: List of SAP data types that are converted into respective data types. Defined by listing the data type identifier characters in any order. Available data type identifying characters are IFPCDTNX. If not defined, all data types are converted. Example: IFP (convert only numeric data types: Integer, Float, Packed number) (more information).

Project is needed for the secure strings.

If there is an error in the function, exception is given.

Examples:

let sapConnection = #{ 
  "AppServerHost": "inf-sap01",
  "SystemNumber": "00",
  "User": "qpr_rfc",
  "Router": "",
  "SystemID": "QPR",
  "Client": "800",
  "Language": "EN",
  "PoolSize": 5,
  "PoolSizeMax": 10,
  "IdleTimeout": 600,
  "PasswordKey": "SapPW41" 
 };
let queryParameters = sapConnection.Extend({
  "FieldNames": "VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK", 
  "QueryTable": "VBAK",
  "Options": ["VBELN BETWEEN '0000017448'" ,"AND '0000017450'"]
});
let queryResults = ProjectById(1).ExtractSap(queryParameters);