Using SSIS to extract Marketo data into SQL database

[Anonymous]

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?

[SanfordWhiteman]

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

[Anonymous]

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]

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.

[Jep_Castelein2]

Not sure if it would be helpful for this specific use case, but Marketo Professional Services now offers a Data Transfer Hub managed service, which will automatically export Lead  data as a delimited file (CSV) and put it on an SFTP server. This can be done on a schedule, usually it's done nightly. Then you can pick it up from the SFTP server and load it into a SQL database.

I'm not too familiar with SSIS, but it appears there are 3rd party plug-ins available that support SFTP, so you could automate it that way.

[Anonymous]

Thanks Jep.  That would be exactly what I would like.  Getting it through SSIS would have been good but this Data Transfer Hub looks like it'll do the job.  Probably cost an arm and a leg but so be it!

Cheers.