Automate Salesforce Marketing Integration Tasks from PowerShell

The CData Cmdlets for Salesforce Marketing 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 Salesforce Marketing.

PowerShell Cmdlets or ADO.NET Provider?

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

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

Authenticating to the Salesforce Marketing Cloud APIs

Set the and to your login credentials, or to the credentials for a sandbox user if you are connecting to a sandbox account.

Connecting to the Salesforce Marketing Cloud APIs

By default, the data provider connects to production environments. Set to true to use a Salesforce Marketing Cloud sandbox account.

The default Instance is s7 of the Web Services API; however, if you use a different instance, you can set .

PowerShell

1. Install the module:

   Install-Module SFMarketingCloudCmdlets

2. Connect:

   $sfmarketingcloud = Connect-SFMarketingCloud -User "$User" -Password "$Password" -InitiateOAuth "$InitiateOAuth"
   

3. Search for and retrieve data:

   $emailaddress = "[email protected]"
   $subscriber = Select-SFMarketingCloud -Connection $sfmarketingcloud -Table "Subscriber" -Where "EmailAddress = `'$EmailAddress`'"
   $subscriber
   

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

   $subscriber = Invoke-SFMarketingCloud -Connection $sfmarketingcloud -Query 'SELECT * FROM Subscriber WHERE EmailAddress = @EmailAddress' -Params @{'@EmailAddress'='[email protected]'}
   

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Salesforce Marketing Cloud\lib\System.Data.CData.SFMarketingCloud.dll")
    

2. Connect to Salesforce Marketing:

    
   $conn= New-Object System.Data.CData.SFMarketingCloud.SFMarketingCloudConnection("User=myUser;Password=myPassword;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

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

   $sql="SELECT Id, Status from Subscriber"
   
   $da= New-Object System.Data.CData.SFMarketingCloud.SFMarketingCloudDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.status
   }
    

Update Salesforce Marketing Data

PowerShell

Update-SFMarketingCloud -Connection $SFMarketingCloud -Columns @('Id','Status') -Values @('MyId', 'MyStatus') -Table Subscriber -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.SFMarketingCloud.SFMarketingCloudCommand("UPDATE Subscriber SET EmailAddress='[email protected]' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SFMarketingCloud.SFMarketingCloudParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Salesforce Marketing Data

PowerShell

Add-SFMarketingCloud -Connection $SFMarketingCloud -Table Subscriber -Columns @("Id", "Status") -Values @("MyId", "MyStatus") 

ADO.NET

$cmd = New-Object System.Data.CData.SFMarketingCloud.SFMarketingCloudCommand("INSERT INTO Subscriber (EmailAddress) VALUES (@myEmailAddress)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SFMarketingCloud.SFMarketingCloudParameter("@myEmailAddress","[email protected]")))
$cmd.ExecuteNonQuery()

Delete Salesforce Marketing Data

PowerShell

Remove-SFMarketingCloud -Connection $SFMarketingCloud -Table "Subscriber" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.SFMarketingCloud.SFMarketingCloudCommand("DELETE FROM Subscriber WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SFMarketingCloud.SFMarketingCloudParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject