Ready to get started?

Learn more about the CData ADO.NET Provider for Salesforce Marketing Cloud or download a free trial:

Download Now

Automate Salesforce Marketing Integration Tasks from PowerShell

Are you looking for a quick and easy way to access Salesforce Marketing data from PowerShell? We show how to use the Cmdlets for Salesforce Marketing and the CData ADO.NET Provider for Salesforce Marketing to connect to Salesforce Marketing data and synchronize, automate, download, and more.

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.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the Salesforce Marketing API, 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. To access Salesforce Marketing data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Salesforce Marketing.

After obtaining the needed connection properties, accessing Salesforce Marketing data in PowerShell consists of three basic steps.

Authenticating to the Salesforce Marketing Cloud APIs

Set the User and Password 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 UseSandbox 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 Instance.

PowerShell

  1. Install the module:

    Install-Module SFMarketingCloudCmdlets
  2. Connect:

    $sfmarketingcloud = Connect-SFMarketingCloud -User "$User" -Password "$Password"
  3. Search for and retrieve data:

    $emailaddress = "john.doe@example.com" $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'='john.doe@example.com'}

ADO.NET

  1. Load the provider's assembly:

    [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Salesforce Marketing\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='john.doe@example.com' WHERE Id = @myId", $conn) $cmd.Parameters.Add(new 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 System.Data.CData.SFMarketingCloud.SFMarketingCloudParameter("@myEmailAddress","john.doe@example.com")) $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 System.Data.CData.SFMarketingCloud.SFMarketingCloudParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()