Ready to get started?

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

 Download Now

Learn more:

Marketo Icon 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!

Automate Marketo Integration Tasks from PowerShell



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

PowerShell Cmdlets or ADO.NET Driver?

The Cmdlets are not only a PowerShell interface to Marketo, 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.

Once you have acquired the necessary connection properties, accessing Marketo data in PowerShell can be enabled in three 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()