Establishing a Connection

With the CData Cmdlets users can install a data module, set the connection properties, and start scripting. This section provides examples of using our SAPAribaSource Cmdlets with native PowerShell cmdlets, like the CSV import and export cmdlets.

Installing and Connecting

If you have PSGet, installing the cmdlets can be accomplished from the PowerShell Gallery with the following command. You can also obtain a setup from the CData site.

Install-Module SAPAribaSourceCmdlets

The following line is then added to your profile, loading the cmdlets on the next session:

Import-Module SAPAribaSourceCmdlets;

You can then use the Connect-SAPAribaSource cmdlet to create a connection object that can be passed to other cmdlets:

$conn = Connect-SAPAribaSource -API "SupplierDataAPIWithPagination-V4" -APIKey "wWVLn7WTAXrIRMAzZ6VnuEj7Ekot5jnU" -Environment "SANDBOX" -Realm "testRealm" -AuthScheme "OAuthClient" -InitiateOAuth "GETANDREFRESH" -OAuthClientId "testClient" -OAuthClientSecret "testClientSecret"

Connecting to SAP Ariba

Set the following to connect:

API: Specify which API you would like the cmdlet to retrieve SAP Ariba data from. Select the Supplier, Sourcing Project Management, or Contract API based on your business role.
DataCenter: The data center where your account's data is hosted.
Realm: The name of the site you want to access.
Environment: Indicate whether you are connecting to a test, sandbox, or production environment.

If you are connecting to the Supplier Data API or the Contract API, additionally set the following:

User: Id of the user on whose behalf API calls are invoked.
PasswordAdapter: The password associated with the authenticating User.

Finally, if you're connecting to the Supplier API, set ProjectId to the Id of the sourcing project you want to retrieve data from.

Authenticating to SAP Ariba

Authentication to SAP Ariba APIs is handled via OAuth. SAP Ariba's OAuth flow requires no user interaction.

OAuthClient

Set the AuthScheme to OAuthClient.

You need to register an application with the service to obtain the APIKey, OAuthClientId and OAuthClientSecret.

See Creating a Custom OAuth App for information about creating an application.

InitiateOAuth defaults to GETANDREFRESH for the OAuthClient authentication scheme.

Automatic OAuth

Get and Refresh the OAuth Access Token

After setting the following, you are ready to connect:

APIKey: The Application key in your app settings.
OAuthClientId: The OAuth Client Id in your app settings.
OAuthClientSecret: The OAuth Secret in your app settings.

When you connect, the cmdlet automatically completes the OAuth process:

The cmdlet obtains an access token from SAP Ariba and uses it to request data.
The cmdlet refreshes the access token automatically when it expires.
The OAuth values are saved in memory relative to the location specified in OAuthSettingsLocation.

Manual OAuth

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

APIKey: The Application key in your app settings.
OAuthClientId: The OAuth Client Id in your app settings.
OAuthClientSecret: The OAuth Secret in your app settings.

Then call the GetOAuthAccessToken stored procedure. Set the GrantType input to client_credentials or openapi_2lo (available for legacy users). If not specified, GrantType defaults to client_credentials.

After you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the cmdlet automatically refresh the OAuth access token, set the following on the first data connection.

InitiateOAuth: REFRESH.
APIKey: The Application key in your app settings.
OAuthClientId: The OAuth Client Id in your app settings.
OAuthClientSecret: The OAuth Secret in your app settings.
OAuthAccessToken: The access token returned by GetOAuthAccessToken.
OAuthRefreshToken: The refresh token returned by GetOAuthAccessToken.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token. Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

APIKey: The Application key in your app settings.
OAuthClientId: The OAuth Client Id in your app settings.
OAuthClientSecret: The OAuth Secret in your app settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Retrieving Data

The Select-SAPAribaSource cmdlet provides a native PowerShell interface for retrieving data:

$results = Select-SAPAribaSource -Connection $conn -Table "Vendors" -Columns @("SMVendorID, Category") -Where "SMVendorID='S123456'"

The Invoke-SAPAribaSource cmdlet provides an SQL interface. This cmdlet can be used to execute an SQL query via the Query parameter.

Piping Cmdlet Output

The cmdlets return row objects to the pipeline one row at a time. The following line exports results to a CSV file:

Select-SAPAribaSource -Connection $conn -Table Vendors -Where "SMVendorID = 'S123456'" | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\myVendorsData.csv -NoTypeInformation

You will notice that we piped the results from Select-SAPAribaSource into a Select-Object cmdlet and excluded some properties before piping them into an Export-CSV cmdlet. We do this because the CData Cmdlets append Connection, Table, and Columns information onto each row object in the result set, and we do not necessarily want that information in our CSV file.

However, this makes it easy to pipe the output of one cmdlet to another. The following is an example of converting a result set to JSON:

 
PS C:\> $conn  = Connect-SAPAribaSource -API "SupplierDataAPIWithPagination-V4" -APIKey "wWVLn7WTAXrIRMAzZ6VnuEj7Ekot5jnU" -Environment "SANDBOX" -Realm "testRealm" -AuthScheme "OAuthClient" -InitiateOAuth "GETANDREFRESH" -OAuthClientId "testClient" -OAuthClientSecret "testClientSecret"
PS C:\> $row = Select-SAPAribaSource -Connection $conn -Table "Vendors" -Columns (SMVendorID, Category) -Where "SMVendorID = 'S123456'" | select -first 1
PS C:\> $row | ConvertTo-Json
{
  "Connection":  {

  },
  "Table":  "Vendors",
  "Columns":  [

  ],
  "SMVendorID":  "MySMVendorID",
  "Category":  "MyCategory"
}

Deleting Data

The following line deletes any records that match the criteria:

Select-SAPAribaSource -Connection $conn -Table Vendors -Where "SMVendorID = 'S123456'" | Remove-SAPAribaSource

Modifying Data

The cmdlets make data transformation easy as well as data cleansing. The following example loads data from a CSV file into SAP Ariba, checking first whether a record already exists and needs to be updated instead of inserted.

Import-Csv -Path C:\MyVendorsUpdates.csv | %{
  $record = Select-SAPAribaSource -Connection $conn -Table Vendors -Where ("Id = `'"+$_.Id+"`'")
  if($record){
    Update-SAPAribaSource -Connection $conn -Table Vendors -Columns @("SMVendorID","Category") -Values @($_.SMVendorID, $_.Category) -Where "Id  = `'$_.Id`'"
  }else{
    Add-SAPAribaSource -Connection $conn -Table Vendors -Columns @("SMVendorID","Category") -Values @($_.SMVendorID, $_.Category)
  }
}

Cmdlets for SAP Ariba Source