ExtractSap Function: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
No edit summary
 
(24 intermediate revisions by 2 users not shown)
Line 1: Line 1:
ExtractSap function extracts data from an SAP R/3 system and returns it as a DataFlow.
ExtractSap function imports data from an SAP R/3 system using its RFC interface and returns a DataFlow. The function is in the generic context and also in the project context. When using the secure strings, the function need to be called in the project context as the secure strings are project specific. Using the ExtractSap function requires installing [[QPR_ProcessAnalyzer_ScriptLauncher#Installing_SAP_NetWeaver_RFC_Library|SAP NetWeaver RFC Library]]. More information about [[Importing_Data_from_SAP|connecting to SAP]].
 
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 [[Installing_QPR_ProcessAnalyzer_Server#Installing_SAP_NetWeaver_RFC_Library|SAP NetWeaver RFC Library]].


Parameters:
Parameters:
* '''UseGateway''': Boolean value indicating whether data extraction should be performed through the gateway (''true'') or by QPR ProcessAnalyzer Server (''false'', default value). The gateway may be needed to access on-premise systems that are not available in the public network.
* '''User''': SAP username used to connect to SAP. Mandatory. Corresponds to the "USER" constant in SAP.
* '''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.
* '''Password''': Password for the SAP user used to connect to SAP. Mandatory. Corresponds to the "PASSWD" constant in SAP.
* '''PasswordKey''': [[Secure_Strings|Secure string key]] for the stored SAP password. Alternative for the Password property.
* '''PasswordKey''': [[Storing_Secrets_for_Scripts|Secret name]] for the stored SAP password. Alternative for the Password property.
* '''Client''': The SAP backend client. Mandatory. Corresponds to the "CLIENT" constant in SAP.
* '''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.
* '''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.
Line 12: Line 11:
* '''SystemNumber''': SAP system’s system number. Mandatory if SystemID is not defined. Corresponds to the "SYSNR" 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.
* '''SystemID''': SAP system’s three-letter system ID. Mandatory if SystemNumber is not defined. Corresponds to the "SYSID" constant in SAP.
* '''Options''': Array of clause elements. Specifies the value for parameter OPTIONS in tab 'Import' or function module 'rfc_read_table' in SAP. NOTE: maximum length for one string is 72 characters(SAP limit). Optional. Example: ["VBELN BETWEEN '0000017448'" ,"AND '0000017450'"].
* '''OptionString''': String of clause elements. Specifies the value for parameter OPTIONS in tab 'Import' or function module 'rfc_read_table' in SAP. Maximum length for one string is 72 characters(SAP limit). Over 72 character length OptionString is splited by ' AND ' and ' OR ' to several OPTIONS parameter to avoid SAP 72 characters limit. Optional. Example: "VBELN BETWEEN '0000017448' AND '0000017450'".
* '''FieldNames''': Comma separated list of field names for columns to be extracted. Default value is empty, which will extract all columns. Specifies the value for parameter FIELDNAME in tab 'Tables' for table 'FIELDS' for function module 'rfc_read_table' in SAP. Optional.
* '''Function''': SAP function name that is called in SAP. Optional. The default value is RFC_READ_TABLE. Another possible function name is BBP_RFC_READ_TABLE.
* '''Language''': SAP language used. Default value is "EN". Optional. Corresponds to the "LANG" constant in SAP.
* '''Language''': SAP language used. Default value is "EN". Optional. Corresponds to the "LANG" constant in SAP.
* '''Rowcount''': 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''': Number of rows to skip. Specifies the value for parameter ROWSKIPS in tab 'Import' or function module 'rfc_read_table'. in SAP. Optional.
* '''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.
* '''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.
* '''IdleTimeout''': If a connection has been idle for more than the defined time in seconds, it will be closed and removed from the connection pool. Default value is "600". Optional. Corresponds to the "IDLE_TIMEOUT" constant in SAP.
* '''ConnectionIdleTimeout''': If a connection has been idle for more than IdleTimeout 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.
* '''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.
* '''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.
* '''Mode''': 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.
* '''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''': Maximum amount of rows to fetch. Specifies the value for parameter ROWCOUNT in tab: 'Import' or function module 'rfc_read_table' in SAP. Optional.
* '''[[Importing_Data_from_SAP|ConvertDataTypes]]''': List of SAP data types that are converted into respective expression language 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).
* '''Rowskips''': Number of rows to skip. Specifies the value for parameter ROWSKIPS in tab: 'Import' or function module 'rfc_read_table'. in SAP. Optional.
* '''Ping''': If this is set to true, SAP server is pinged before SAP query execution. Purpose is help troubleshooting SAP connection issues. Optional.
* '''Options''': Array of clause elements. Specifies the value for parameter OPTIONS in tab: 'Import' or function module 'rfc_read_table' in SAP. NOTE: maximum length for one string is 72 characters(SAP limit). Optional. Example: ["VBELN BETWEEN '0000017448'" ,"AND '0000017450'"].
* '''TraceLevel''': Sets SAP trace level, which can be 0, 1, 2, 3 or 4. Setting higher trace level helps to troubleshoot SAP connection issues. ''sapnco.dll'' writes log file to the current working folder with for example name'nco_rfc_5484_2.trc'. Optional.
* '''OptionString''': String of clause elements. Specifies the value for parameter OPTIONS in tab: 'Import' or function module 'rfc_read_table' in SAP.
* '''AliasUser'''
2. Maximum length for one string is 72 characters(SAP limit). Over 72 character length OptionString is splited by ' AND ' and ' OR ' to several OPTIONS parameter to avoid SAP 72 charachters limit. Optional. Example: "VBELN BETWEEN '0000017448' AND '0000017450'".
* '''AppServerService'''
* '''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.
* '''CharacterFaultIndicatorToken'''
* '''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.
* '''Codepage'''
* '''FieldNames''': 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.
* '''GatewayHost'''
* '''Function''': The SAP function name that is called in SAP. Optional. The default value is RFC_READ_TABLE. Another possible function name is BBP_RFC_READ_TABLE.
* '''GatewayService'''
* '''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).
* '''IdleCheckTime'''
* '''Ping''': If this is set to true, SAP server is ping before SAP query execution. Purpose is help troubleshooting SAP connection issues. Optional.
* '''LogonCheck'''
* '''TraceLevel''': If this is set to 0, 1, 2, 3 or 4 , SAP tracelevel is set that value. Purpose is help troubleshooting SAP connection issues. ''sapnco.dll'' write log file current working folder with file name example 'nco_rfc_5484_2.trc'.
* '''MaxPoolWaitTime'''
* '''MessageServerService'''
* '''Name'''
* '''NoCompression'''
* '''OnCharacterConversionError'''
* '''PartnerCharSize'''
* '''PasswordChangeEnforced'''
* '''ProgramId'''
* '''R3Name'''
* '''RegistrationCount'''
* '''RepositoryDestination'''
* '''RepositoryPassword'''
* '''RepositorySncMyName'''
* '''RepositoryUser'''
* '''RepositoryX509Certificate'''
* '''SapSso2Ticket'''
* '''SncLibraryPath''': Full path including file name of the SNC shared library to be used.
* '''SncMode''': Determines whether connections will be secured with SNC. Value 0 doesn't use SNC (default) and value 1 uses SNC.
* '''SncMyName''': Token/identifier representing the external RFC program. In most cases this can be omitted. The installed SNC solution usually knows its own SNC name. Only for solutions supporting “multiple identities”, you may Varies depending on the installed SNC solution (Secude, Kerberos, NTLM, etc). Example for Secude: p/secude:CN=ALEREMOT SAP Online Help 09.09.2014 SAP .NET Connector 3.0 41 need to specify the identity to be used for this particular destination/server. E, O=Mustermann-AG, C=DE
* '''SncPartnerName''': The backend's SNCname.
* '''SncPartnerNames'''
* '''SncQop''': Quality of service to be used for SNC communication of this particular destination/server. One of the following values:
** 1: Digital signature
** 2: Digital signature and encryption
** 3: Digital signature, encryption, and user authentication
** 8: Default value defined by back-end system
** 9: Maximum value that the current security product supports
* '''SystemIds'''
* '''UseSapGui'''
* '''X509Certificate'''


Project is needed for the secure strings.
Examples:
 
If there is an error in the function, exception is given.


Examples:
An example of performing a SAP extraction and persisting the extracted data to a table having id 1. Note: Data table must exist already when using this approach.
<pre>
<syntaxhighlight lang="typescript">
let sapConnection = #{  
let sapConnection = #{  
   "AppServerHost": "inf-sap01",
   "AppServerHost": "sap01",
   "SystemNumber": "00",
   "SystemNumber": "00",
   "User": "qpr_rfc",
   "User": "user1",
  "PasswordKey": "SapPW1",
   "Router": "",
   "Router": "",
   "SystemID": "QPR",
   "SystemID": "QPR",
   "Client": "800",
   "Client": "800",
  "Language": "EN",
  "PoolSize": 5,
  "PoolSizeMax": 10,
  "IdleTimeout": 600
};
let queryParameters = sapConnection.Extend(#{
  "FieldNames": "VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK",
  "QueryTable": "VBAK",
  "Options": ["VBELN BETWEEN '0000017448'" ,"AND '0000017450'"]
});
let resultsFlow = ProjectById(1)
  .ExtractSap(queryParameters);
DatatableById(1)
  .Import(resultsFlow);
</syntaxhighlight>
An example of performing a SAP extraction and persisting the extracted data to a table named "SAPData" in project having id 1. If the data table does not yet exist, a new one is created into Snowflake.<syntaxhighlight lang="typescript">
let sapConnection = #{
  "AppServerHost": "sap01",
  "SystemNumber": "10",
  "User": "exampleuser",
  "Password": "examplepassword",
  "Router": "/H/127.0.0.1/A/1234/H/",
  "SystemID": "QPR",
  "Client": "200",
   "Language": "EN",
   "Language": "EN",
   "PoolSize": 5,
   "PoolSize": 5,
   "PoolSizeMax": 10,
   "PoolSizeMax": 10,
   "IdleTimeout": 600,
   "IdleTimeout": 600,
   "PasswordKey": "SapPW41"  
   "AppServerHost": "127.0.0.1",
  "LogonGroup": "GROUPXNAME",
  "Mode": "1"
  };
  };
let queryParameters = sapConnection.Extend({
let queryParameters = sapConnection.Extend(#{
   "FieldNames": "VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK",  
   "FieldNames": "VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK",  
   "QueryTable": "VBAK",
   "QueryTable": "VBAK",
   "Options": ["VBELN BETWEEN '0000017448'" ,"AND '0000017450'"]
   "Options": ["VBELN EQ '0060000039'", "OR VBELN EQ '0060000040'"]
});
});
let queryResults = ProjectById(1).ExtractSap(queryParameters);
ProjectById(1)
</pre>
  .ExtractSap(queryParameters)
  .Persist("SAPData", #{"ProjectId": 1, "Connection": CreateSnowflakeConnection(#{"ProjectId": 1})})
</syntaxhighlight>
 
An example of performing a SAP extract, a simple transformation for extracted data and import to a table named "TransformedSAPData" in project having id 1.
 
Note: The example uses ScriptLauncher as a gateway and works only with ScriptLauncher version 2023.1 or later with "UseLegacyClientSideImport"-setting in appsettings.json set to false (if the setting is available in the used version).<syntaxhighlight lang="typescript">
function ExtractTransformAndLoad(extractFunc, transformFunc, loadFunc)
{
  let rawDataFlow = extractFunc();
  let transformedDataFlow = ToDataFlow();
 
  _system.Parallel.Run([
    () => Catch({
      let df;
      while (!IsNullTop(df = rawDataFlow.Collect(#{"CollectChunk": true}))) {
        transformedDataFlow.Append(transformFunc(df));
        WriteLog(`A chunk having ${df.NRows} rows has been transformed.`);
      }
      if (rawDataFlow.HasError) {
        transformedDataFlow.Fail("Error occurred during data extraction.");
      }
      else {
        transformedDataFlow.Complete();
      }
    }, {
      transformedDataFlow.Fail("Error occurred during transformation calculation.");
    }),
    () => {
      loadFunc(transformedDataFlow);
    }
  ]);
}
 
let sapConnection = #{
  "AppServerHost": "sap01",
  "SystemNumber": "10",
  "User": "exampleuser",
  "Password": "examplepassword",
  "Router": "/H/127.0.0.1/A/1234/H/",
  "SystemID": "QPR",
  "Client": "200",
  "Language": "EN",
  "PoolSize": 5,
  "PoolSizeMax": 10,
  "IdleTimeout": 600,
  "AppServerHost": "127.0.0.1",
  "LogonGroup": "GROUPXNAME",
  "Mode": "1"
};
 
ExtractTransformAndLoad(
  () => ExtractSap(
    sapConnection.Extend([
      "FieldNames": "VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK",
      "QueryTable": "VBAK",
      "Options": ["VBELN BETWEEN '0000017448'" ,"AND '0060000042'"],
      "UseGateway": true
    ])
  ),
  df => df.SetColumns(["Test": () => `${Column("NETWR")} ${Column("WAERK")}`]),
  dataFlow => {
    dataFlow.Persist("TransformedSAPData", ["ProjectName": "TestData", "Append": 0]);
  }
);
 
</syntaxhighlight>

Latest revision as of 07:16, 30 April 2024

ExtractSap function imports data from an SAP R/3 system using its RFC interface and returns a DataFlow. The function is in the generic context and also in the project context. When using the secure strings, the function need to be called in the project context as the secure strings are project specific. Using the ExtractSap function requires installing SAP NetWeaver RFC Library. More information about connecting to SAP.

Parameters:

  • UseGateway: Boolean value indicating whether data extraction should be performed through the gateway (true) or by QPR ProcessAnalyzer Server (false, default value). The gateway may be needed to access on-premise systems that are not available in the public network.
  • 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: Secret name 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.
  • Options: Array of clause elements. Specifies the value for parameter OPTIONS in tab 'Import' or function module 'rfc_read_table' in SAP. NOTE: maximum length for one string is 72 characters(SAP limit). Optional. Example: ["VBELN BETWEEN '0000017448'" ,"AND '0000017450'"].
  • OptionString: String of clause elements. Specifies the value for parameter OPTIONS in tab 'Import' or function module 'rfc_read_table' in SAP. Maximum length for one string is 72 characters(SAP limit). Over 72 character length OptionString is splited by ' AND ' and ' OR ' to several OPTIONS parameter to avoid SAP 72 characters limit. Optional. Example: "VBELN BETWEEN '0000017448' AND '0000017450'".
  • FieldNames: Comma separated list of field names for columns to be extracted. Default value is empty, which will extract all columns. Specifies the value for parameter FIELDNAME in tab 'Tables' for table 'FIELDS' for function module 'rfc_read_table' in SAP. Optional.
  • Function: SAP function name that is called in SAP. Optional. The default value is RFC_READ_TABLE. Another possible function name is BBP_RFC_READ_TABLE.
  • Language: SAP language used. Default value is "EN". Optional. Corresponds to the "LANG" constant in SAP.
  • Rowcount: 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: Number of rows to skip. Specifies the value for parameter ROWSKIPS in tab 'Import' or function module 'rfc_read_table'. in SAP. Optional.
  • 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.
  • IdleTimeout: If a connection has been idle for more than the defined time in seconds, it will be closed and removed from the connection pool. 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.
  • Mode: 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.
  • ConvertDataTypes: List of SAP data types that are converted into respective expression language 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).
  • Ping: If this is set to true, SAP server is pinged before SAP query execution. Purpose is help troubleshooting SAP connection issues. Optional.
  • TraceLevel: Sets SAP trace level, which can be 0, 1, 2, 3 or 4. Setting higher trace level helps to troubleshoot SAP connection issues. sapnco.dll writes log file to the current working folder with for example name'nco_rfc_5484_2.trc'. Optional.
  • AliasUser
  • AppServerService
  • CharacterFaultIndicatorToken
  • Codepage
  • GatewayHost
  • GatewayService
  • IdleCheckTime
  • LogonCheck
  • MaxPoolWaitTime
  • MessageServerService
  • Name
  • NoCompression
  • OnCharacterConversionError
  • PartnerCharSize
  • PasswordChangeEnforced
  • ProgramId
  • R3Name
  • RegistrationCount
  • RepositoryDestination
  • RepositoryPassword
  • RepositorySncMyName
  • RepositoryUser
  • RepositoryX509Certificate
  • SapSso2Ticket
  • SncLibraryPath: Full path including file name of the SNC shared library to be used.
  • SncMode: Determines whether connections will be secured with SNC. Value 0 doesn't use SNC (default) and value 1 uses SNC.
  • SncMyName: Token/identifier representing the external RFC program. In most cases this can be omitted. The installed SNC solution usually knows its own SNC name. Only for solutions supporting “multiple identities”, you may Varies depending on the installed SNC solution (Secude, Kerberos, NTLM, etc). Example for Secude: p/secude:CN=ALEREMOT SAP Online Help 09.09.2014 SAP .NET Connector 3.0 41 need to specify the identity to be used for this particular destination/server. E, O=Mustermann-AG, C=DE
  • SncPartnerName: The backend's SNCname.
  • SncPartnerNames
  • SncQop: Quality of service to be used for SNC communication of this particular destination/server. One of the following values:
    • 1: Digital signature
    • 2: Digital signature and encryption
    • 3: Digital signature, encryption, and user authentication
    • 8: Default value defined by back-end system
    • 9: Maximum value that the current security product supports
  • SystemIds
  • UseSapGui
  • X509Certificate

Examples:

An example of performing a SAP extraction and persisting the extracted data to a table having id 1. Note: Data table must exist already when using this approach.

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

An example of performing a SAP extraction and persisting the extracted data to a table named "SAPData" in project having id 1. If the data table does not yet exist, a new one is created into Snowflake.

let sapConnection = #{ 
  "AppServerHost": "sap01",
  "SystemNumber": "10",
  "User": "exampleuser",
  "Password": "examplepassword",
  "Router": "/H/127.0.0.1/A/1234/H/",
  "SystemID": "QPR",
  "Client": "200",
  "Language": "EN",
  "PoolSize": 5,
  "PoolSizeMax": 10,
  "IdleTimeout": 600,
  "AppServerHost": "127.0.0.1",
  "LogonGroup": "GROUPXNAME",
  "Mode": "1"
 };
let queryParameters = sapConnection.Extend(#{
  "FieldNames": "VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK", 
  "QueryTable": "VBAK",
  "Options": ["VBELN EQ '0060000039'", "OR VBELN EQ '0060000040'"]
});
ProjectById(1)
  .ExtractSap(queryParameters)
  .Persist("SAPData", #{"ProjectId": 1, "Connection": CreateSnowflakeConnection(#{"ProjectId": 1})})

An example of performing a SAP extract, a simple transformation for extracted data and import to a table named "TransformedSAPData" in project having id 1.

Note: The example uses ScriptLauncher as a gateway and works only with ScriptLauncher version 2023.1 or later with "UseLegacyClientSideImport"-setting in appsettings.json set to false (if the setting is available in the used version).

function ExtractTransformAndLoad(extractFunc, transformFunc, loadFunc)
{
  let rawDataFlow = extractFunc();
  let transformedDataFlow = ToDataFlow();

  _system.Parallel.Run([
    () => Catch({
      let df;
      while (!IsNullTop(df = rawDataFlow.Collect(#{"CollectChunk": true}))) {
        transformedDataFlow.Append(transformFunc(df));
        WriteLog(`A chunk having ${df.NRows} rows has been transformed.`);
      }
      if (rawDataFlow.HasError) {
        transformedDataFlow.Fail("Error occurred during data extraction.");
      }
      else {
        transformedDataFlow.Complete();
      }
    }, {
      transformedDataFlow.Fail("Error occurred during transformation calculation.");
    }),
    () => {
      loadFunc(transformedDataFlow);
    }
  ]);
}

let sapConnection = #{ 
  "AppServerHost": "sap01",
  "SystemNumber": "10",
  "User": "exampleuser",
  "Password": "examplepassword",
  "Router": "/H/127.0.0.1/A/1234/H/",
  "SystemID": "QPR",
  "Client": "200",
  "Language": "EN",
  "PoolSize": 5,
  "PoolSizeMax": 10,
  "IdleTimeout": 600,
  "AppServerHost": "127.0.0.1",
  "LogonGroup": "GROUPXNAME",
  "Mode": "1"
};

ExtractTransformAndLoad(
  () => ExtractSap(
    sapConnection.Extend([
      "FieldNames": "VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK", 
      "QueryTable": "VBAK",
      "Options": ["VBELN BETWEEN '0000017448'" ,"AND '0060000042'"],
      "UseGateway": true
    ])
  ),
  df => df.SetColumns(["Test": () => `${Column("NETWR")} ${Column("WAERK")}`]),
  dataFlow => {
    dataFlow.Persist("TransformedSAPData", ["ProjectName": "TestData", "Append": 0]);
  }
);