Ready to get started?

Download a free trial of the Salesforce Marketing Data Provider to get started:

 Download Now

Learn more:

Salesforce Marketing Cloud Icon Salesforce Marketing ADO.NET Provider

Rapidly create and deploy powerful .NET applications that integrate with Salesforce Marketing Cloud data including Accounts, Emails, Lists, Subscribers, and more!

Automate Salesforce Marketing Integration Tasks from PowerShell



Are you in search of a quick and easy way to access Salesforce Marketing data from PowerShell? This article demonstrates how to utilize the Salesforce Marketing Cmdlets and the CData ADO.NET Provider for Salesforce Marketing for tasks like connecting to Salesforce Marketing data, automating operations, downloading data, 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.

PowerShell Cmdlets or ADO.NET Driver?

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. To access Salesforce Marketing data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Salesforce Marketing.

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 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()