Automate Marketo Integration Tasks from PowerShell

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 Provider?

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-Object 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-Object 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-Object System.Data.CData.Marketo.MarketoParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject