Using SSIS to extract Marketo data into SQL database | Community
Skip to main content
A
Anonymous
July 24, 2018
Question

Using SSIS to extract Marketo data into SQL database

  • July 24, 2018
  • 1 reply
  • 3668 views

Hi all,

I want to use Microsoft SSIS 2015 to extract Marketo lead data and put it into a SQL database.  Has anyone successfully done this?  There is a post on this Discussion forum from August 2017 but it doesn't seem like it was successful.  There is a Web Service task in SSIS that looked promising, I am not sure on what URL to put in the Server URL field for the HTTP Connection Manager Editor setting.

I want to automate the SSIS package to run overnight, so not interested in the Marketo Bulk Export option.

Has anyone been able to do this data extract via SSIS?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

1 reply

SanfordWhiteman
SanfordWhiteman
Level 10
July 24, 2018

It's not possible to use a generic HTTP request for the REST API. You need an SSIS component that can manage the pagination, expiring access tokens, et al.

Frankly you will be much better served by writing a workflow that uses the Bulk Export API (which is fully automatable, you just have to build the appropriate logic).

A
Anonymous
July 24, 2018

Thanks for the quick reply Sanford.  Assuming I'm a complete novice with API's and workflows, can you point me in the direction of where to start please? How to write one?  What to write it in?  etc.

Cheers,

Andy

SanfordWhiteman
SanfordWhiteman
Level 10
July 25, 2018

The best language is in the one you know, the saying goes. I'm a Java or JavaScript (Node.JS) person these days, but you could use anything.  Go is good to learn in 2018. PHP isn't really good for this kind of app, though, IMO.

But is this is your first development experience ever? If so, this isn't a good place to start. You have to understand a few advanced technologies (HTTP request/response, job queues/polling, and long-lived services) to get it right. At any rate, why don't you start by running through the Bulk Export steps with Postman?  That doesn't take any code at all, and if you can't make it make sense manually in Postman you couldn't write the code to automate it.

You can also try the shrink-wrapped CData Marketo SSIS component, but don't expect too much. I tested it in the past and found it unacceptable for all but the tiniest databases (< 100000 leads). I also had to hack around some of its mistakes w/the Marketo API, but maybe they've fixed it since then. There's probably no stable, scaleable, and shrink-wrapped solution for you at this point, unfortunately: all the good work in this area is custom work.

Terms & ConditionsAccessibility statement

Loading footer...