Automate SAP SuccessFactors Integration Tasks from PowerShell

The CData Cmdlets for SAP SuccessFactors are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to SAP SuccessFactors.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to SAP SuccessFactors, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete SAP SuccessFactors data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for SAP SuccessFactors. To access SAP SuccessFactors data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for SAP SuccessFactors.

Once you have acquired the necessary connection properties, accessing SAP SuccessFactors data in PowerShell can be enabled in three steps.

You can authenticate to SAP Success Factors using Basic authentication or OAuth with SAML assertion.

Basic Authentication

You must provide values for the following properties to successfully authenticate to SAP Success Factors. Note that the provider will reuse the session opened by SAP Success Factors using cookies. Which means that your credentials will be used only on the first request to open the session. After that, cookies returned from SAP Success Factors will be used for authentication.

• Url: set this to the URL of the server hosting Success Factors. Some of the servers are listed in the SAP support documentation (external link).
• User: set this to the username of your account.
• Password: set this to the password of your account.
• CompanyId: set this to the unique identifier of your company.

OAuth Authentication

You must provide values for the following properties, which will be used to get the access token.

• Url: set this to the URL of the server hosting Success Factors. Some of the servers are listed in the SAP support documentation (external link).
• User: set this to the username of your account.
• CompanyId: set this to the unique identifier of your company.
• OAuthClientId: set this to the API Key that was generated in API Center.
• OAuthClientSecret: the X.509 private key used to sign SAML assertion. The private key can be found in the certificate you downloaded in Registering your OAuth Client Application.
• InitiateOAuth: set this to GETANDREFRESH.

PowerShell

1. Install the module:

   Install-Module SAPSuccessFactorsCmdlets

2. Connect:

   $sapsuccessfactors = Connect-SAPSuccessFactors -User "$User" -Password "$Password" -CompanyId "$CompanyId" -Url "$Url"
   

3. Search for and retrieve data:

   $city = "Springfield"
   $extaddressinfo = Select-SAPSuccessFactors -Connection $sapsuccessfactors -Table "ExtAddressInfo" -Where "city = `'$city`'"
   $extaddressinfo
   

   You can also use the Invoke-SAPSuccessFactors cmdlet to execute SQL commands:

   $extaddressinfo = Invoke-SAPSuccessFactors -Connection $sapsuccessfactors -Query 'SELECT * FROM ExtAddressInfo WHERE city = @city' -Params @{'@city'='Springfield'}
   

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for SAP SuccessFactors\lib\System.Data.CData.SAPSuccessFactors.dll")
    

2. Connect to SAP SuccessFactors:

    
   $conn= New-Object System.Data.CData.SAPSuccessFactors.SAPSuccessFactorsConnection("User=username;Password=password;CompanyId=CompanyId;Url=https://api4.successfactors.com;")
   $conn.Open()
   

3. Instantiate the SAPSuccessFactorsDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT address1, zipCode from ExtAddressInfo"
   
   $da= New-Object System.Data.CData.SAPSuccessFactors.SAPSuccessFactorsDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.address1 $_.zipcode
   }
    

Update SAP SuccessFactors Data

PowerShell

Update-SAPSuccessFactors -Connection $SAPSuccessFactors -Columns @('address1','zipCode') -Values @('Myaddress1', 'MyzipCode') -Table ExtAddressInfo -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.SAPSuccessFactors.SAPSuccessFactorsCommand("UPDATE ExtAddressInfo SET city='Springfield' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SAPSuccessFactors.SAPSuccessFactorsParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert SAP SuccessFactors Data

PowerShell

Add-SAPSuccessFactors -Connection $SAPSuccessFactors -Table ExtAddressInfo -Columns @("address1", "zipCode") -Values @("Myaddress1", "MyzipCode") 

ADO.NET

$cmd = New-Object System.Data.CData.SAPSuccessFactors.SAPSuccessFactorsCommand("INSERT INTO ExtAddressInfo (city) VALUES (@mycity)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SAPSuccessFactors.SAPSuccessFactorsParameter("@mycity","Springfield")))
$cmd.ExecuteNonQuery()

Delete SAP SuccessFactors Data

PowerShell

Remove-SAPSuccessFactors -Connection $SAPSuccessFactors -Table "ExtAddressInfo" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.SAPSuccessFactors.SAPSuccessFactorsCommand("DELETE FROM ExtAddressInfo WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SAPSuccessFactors.SAPSuccessFactorsParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject