Automate Marketo Integration Tasks from PowerShell

Ready to get started?

Download for a free trial:

Download Now

Learn more:

Marketo ADO.NET Provider

Rapidly create and deploy powerful .NET applications that integrate with Marketo marketing automation data including Leads, Opportunities, Channels, Campaigns, and more!



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

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

Cmdlets or ADO.NET?

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

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

Both the REST and SOAP APIs are supported and can be chosen by using the Schema property.

For the REST API: The OAuthClientId, OAuthClientSecret, and RESTEndpoint properties, under the OAuth and REST Connection sections, must be set to valid Marketo user credentials.

For the SOAP API: The UserId, EncryptionKey, and SOAPEndpoint properties, under the SOAP Connection section, must be set to valid Marketo user credentials.

See the "Getting Started" chapter of the help documentation for a guide to obtaining these values.

PowerShell

  1. Install the module:

    Install-Module MarketoCmdlets
  2. Connect:

    $marketo = Connect-Marketo -Schema "$Schema" -RESTEndpoint "$RESTEndpoint" -OAuthClientId "$OAuthClientId" -OAuthClientSecret "$OAuthClientSecret"
  3. Search for and retrieve data:

    $country = "U.S.A." $leads = Select-Marketo -Connection $marketo -Table "Leads" -Where "Country = `'$Country`'" $leads

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

    $leads = Invoke-Marketo -Connection $marketo -Query 'SELECT * FROM Leads WHERE Country = @Country' -Params @{'@Country'='U.S.A.'}

ADO.NET

  1. Load the provider's assembly:

    [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Marketo\lib\System.Data.CData.Marketo.dll")
  2. Connect to Marketo:

    $conn= New-Object System.Data.CData.Marketo.MarketoConnection("Schema=REST;RESTEndpoint=https://311-IFS-929.mktorest.com/rest;OAuthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;") $conn.Open()
  3. Instantiate the MarketoDataAdapter, execute an SQL query, and output the results:

    $sql="SELECT Email, AnnualRevenue from Leads" $da= New-Object System.Data.CData.Marketo.MarketoDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.email $_.annualrevenue }

Update Marketo Data

PowerShell

Update-Marketo -Connection $Marketo -Columns @('Email','AnnualRevenue') -Values @('MyEmail', 'MyAnnualRevenue') -Table Leads -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.Marketo.MarketoCommand("UPDATE Leads SET Country='U.S.A.' WHERE Id = @myId", $conn) $cmd.Parameters.Add(new System.Data.CData.Marketo.MarketoParameter("@myId","10456255-0015501366")) $cmd.ExecuteNonQuery()

Insert Marketo Data

PowerShell

Add-Marketo -Connection $Marketo -Table Leads -Columns @("Email", "AnnualRevenue") -Values @("MyEmail", "MyAnnualRevenue")

ADO.NET

$cmd = New-Object System.Data.CData.Marketo.MarketoCommand("INSERT INTO Leads (Country) VALUES (@myCountry)", $conn) $cmd.Parameters.Add(new System.Data.CData.Marketo.MarketoParameter("@myCountry","U.S.A.")) $cmd.ExecuteNonQuery()

Delete Marketo Data

PowerShell

Remove-Marketo -Connection $Marketo -Table "Leads" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.Marketo.MarketoCommand("DELETE FROM Leads WHERE Id=@myId", $conn) $cmd.Parameters.Add(new System.Data.CData.Marketo.MarketoParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()